For Each - Open specified workbooks in array
-1
I have about 200ish workbooks That I need opened in a dir containing thousands of files. The name of these 200 files have been placed in Sheet 4 B3:B231.
Whenever I run the macro however, I only get the code to work on 4 of these files, 1 of the 4 isn't even mentioned in the specified range.
Dim wb As Workbook, shtDest As Worksheet
Dim MyFile As String
Dim Filepath As String
Dim WoBo As Variant
Dim DirArray As Variant
DirArray = Sheets("Sheet4").Range("B3:B231").Value
Filepath = "C:"
MyFile = Dir(Filepath)
Set shtDest = ThisWorkbook.Sheets("Sheet1")
For Each WoBo In DirArray
'code
Next WoBo
End Sub
I'm quite new to arrays and I don't really understand the For Each line, so I suspect the error to be there.
excel vba
asked Nov 14 '18 at 10:03
G. KoenG. Koen
134
add a comment |
-1
I have about 200ish workbooks That I need opened in a dir containing thousands of files. The name of these 200 files have been placed in Sheet 4 B3:B231.
Whenever I run the macro however, I only get the code to work on 4 of these files, 1 of the 4 isn't even mentioned in the specified range.
Dim wb As Workbook, shtDest As Worksheet
Dim MyFile As String
Dim Filepath As String
Dim WoBo As Variant
Dim DirArray As Variant
DirArray = Sheets("Sheet4").Range("B3:B231").Value
Filepath = "C:"
MyFile = Dir(Filepath)
Set shtDest = ThisWorkbook.Sheets("Sheet1")
For Each WoBo In DirArray
'code
Next WoBo
End Sub
I'm quite new to arrays and I don't really understand the For Each line, so I suspect the error to be there.
excel vba
asked Nov 14 '18 at 10:03
G. KoenG. Koen
134
stackoverflow.com/a/52467171/5448626
– Vityata
Nov 14 '18 at 10:11
add a comment |
-1
-1
-1
I have about 200ish workbooks That I need opened in a dir containing thousands of files. The name of these 200 files have been placed in Sheet 4 B3:B231.
Whenever I run the macro however, I only get the code to work on 4 of these files, 1 of the 4 isn't even mentioned in the specified range.
Dim wb As Workbook, shtDest As Worksheet
Dim MyFile As String
Dim Filepath As String
Dim WoBo As Variant
Dim DirArray As Variant
DirArray = Sheets("Sheet4").Range("B3:B231").Value
Filepath = "C:"
MyFile = Dir(Filepath)
Set shtDest = ThisWorkbook.Sheets("Sheet1")
For Each WoBo In DirArray
'code
Next WoBo
End Sub
I'm quite new to arrays and I don't really understand the For Each line, so I suspect the error to be there.
excel vba
asked Nov 14 '18 at 10:03
G. KoenG. Koen
134
I have about 200ish workbooks That I need opened in a dir containing thousands of files. The name of these 200 files have been placed in Sheet 4 B3:B231.
Whenever I run the macro however, I only get the code to work on 4 of these files, 1 of the 4 isn't even mentioned in the specified range.
Dim wb As Workbook, shtDest As Worksheet
Dim MyFile As String
Dim Filepath As String
Dim WoBo As Variant
Dim DirArray As Variant
DirArray = Sheets("Sheet4").Range("B3:B231").Value
Filepath = "C:"
MyFile = Dir(Filepath)
Set shtDest = ThisWorkbook.Sheets("Sheet1")
For Each WoBo In DirArray
'code
Next WoBo
End Sub
I'm quite new to arrays and I don't really understand the For Each line, so I suspect the error to be there.
excel vba
excel vba
asked Nov 14 '18 at 10:03
G. KoenG. Koen
134
asked Nov 14 '18 at 10:03
G. KoenG. Koen
134
asked Nov 14 '18 at 10:03
G. KoenG. Koen
134
asked Nov 14 '18 at 10:03
G. KoenG. Koen
134
asked Nov 14 '18 at 10:03
G. KoenG. Koen
134
134
stackoverflow.com/a/52467171/5448626
– Vityata
Nov 14 '18 at 10:11
add a comment |
stackoverflow.com/a/52467171/5448626
– Vityata
Nov 14 '18 at 10:11
stackoverflow.com/a/52467171/5448626
– Vityata
Nov 14 '18 at 10:11
stackoverflow.com/a/52467171/5448626
– Vityata
Nov 14 '18 at 10:11
add a comment |
1 Answer
1
active
oldest
votes
0
Try something like the following. A For Loop is faster than a For Each for arrays and using Transpose turns it into a one dimensional array to loop over.
Dim dirArray()
dirArray = Application.Transpose(ThisWorkbook.Worksheets("Sheet4").Range("B3:B231").Value)
For i = LBound(dirArray) To Ubound(dirArray)
If instr(dirArray(i),"xls") > 0 Then '<== very basic check
Workbooks.Open(dirArray(i)) '<== should be full filepath of file
' Do stuff
End If
Next
answered Nov 14 '18 at 10:10
QHarrQHarr
32.3k82042
What do you mean with " '<== should be full filepath of file"? Do I simply copy the filepath to this line? If I don't add anything I get the error "sorry we could'nt find filename. Is it possible it was moved, renamed or deleted?"
– G. Koen
Nov 14 '18 at 10:25
The cells you are reading in from should contain the full file path to the file I believe C:Userspath.....workbookX.xlsx.
– QHarr
Nov 14 '18 at 10:34
Did you try that?
– QHarr
Nov 14 '18 at 11:39
I'm not sure I understand. The files that have to be read are all in the same directory, C:Userspath...workbookX.xlsx. Each one of these workbooks is defined in sheet 4 range B3:B231 with just the filename itself. workbook1.xlsx, workbook2.xlsx, etc. .
– G. Koen
Nov 15 '18 at 5:52
You need concatenate the path on the name before passing to workbooks.open
– QHarr
Nov 15 '18 at 6:23
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53297528%2ffor-each-open-specified-workbooks-in-array%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Try something like the following. A For Loop is faster than a For Each for arrays and using Transpose turns it into a one dimensional array to loop over.
Dim dirArray()
dirArray = Application.Transpose(ThisWorkbook.Worksheets("Sheet4").Range("B3:B231").Value)
For i = LBound(dirArray) To Ubound(dirArray)
If instr(dirArray(i),"xls") > 0 Then '<== very basic check
Workbooks.Open(dirArray(i)) '<== should be full filepath of file
' Do stuff
End If
Next
answered Nov 14 '18 at 10:10
QHarrQHarr
32.3k82042
What do you mean with " '<== should be full filepath of file"? Do I simply copy the filepath to this line? If I don't add anything I get the error "sorry we could'nt find filename. Is it possible it was moved, renamed or deleted?"
– G. Koen
Nov 14 '18 at 10:25
The cells you are reading in from should contain the full file path to the file I believe C:Userspath.....workbookX.xlsx.
– QHarr
Nov 14 '18 at 10:34
Did you try that?
– QHarr
Nov 14 '18 at 11:39
I'm not sure I understand. The files that have to be read are all in the same directory, C:Userspath...workbookX.xlsx. Each one of these workbooks is defined in sheet 4 range B3:B231 with just the filename itself. workbook1.xlsx, workbook2.xlsx, etc. .
– G. Koen
Nov 15 '18 at 5:52
You need concatenate the path on the name before passing to workbooks.open
– QHarr
Nov 15 '18 at 6:23
|
show 1 more comment
0
Try something like the following. A For Loop is faster than a For Each for arrays and using Transpose turns it into a one dimensional array to loop over.
Dim dirArray()
dirArray = Application.Transpose(ThisWorkbook.Worksheets("Sheet4").Range("B3:B231").Value)
For i = LBound(dirArray) To Ubound(dirArray)
If instr(dirArray(i),"xls") > 0 Then '<== very basic check
Workbooks.Open(dirArray(i)) '<== should be full filepath of file
' Do stuff
End If
Next
answered Nov 14 '18 at 10:10
QHarrQHarr
32.3k82042
What do you mean with " '<== should be full filepath of file"? Do I simply copy the filepath to this line? If I don't add anything I get the error "sorry we could'nt find filename. Is it possible it was moved, renamed or deleted?"
– G. Koen
Nov 14 '18 at 10:25
The cells you are reading in from should contain the full file path to the file I believe C:Userspath.....workbookX.xlsx.
– QHarr
Nov 14 '18 at 10:34
Did you try that?
– QHarr
Nov 14 '18 at 11:39
I'm not sure I understand. The files that have to be read are all in the same directory, C:Userspath...workbookX.xlsx. Each one of these workbooks is defined in sheet 4 range B3:B231 with just the filename itself. workbook1.xlsx, workbook2.xlsx, etc. .
– G. Koen
Nov 15 '18 at 5:52
You need concatenate the path on the name before passing to workbooks.open
– QHarr
Nov 15 '18 at 6:23
|
show 1 more comment
0
0
0
Try something like the following. A For Loop is faster than a For Each for arrays and using Transpose turns it into a one dimensional array to loop over.
Dim dirArray()
dirArray = Application.Transpose(ThisWorkbook.Worksheets("Sheet4").Range("B3:B231").Value)
For i = LBound(dirArray) To Ubound(dirArray)
If instr(dirArray(i),"xls") > 0 Then '<== very basic check
Workbooks.Open(dirArray(i)) '<== should be full filepath of file
' Do stuff
End If
Next
answered Nov 14 '18 at 10:10
QHarrQHarr
32.3k82042
Try something like the following. A For Loop is faster than a For Each for arrays and using Transpose turns it into a one dimensional array to loop over.
Dim dirArray()
dirArray = Application.Transpose(ThisWorkbook.Worksheets("Sheet4").Range("B3:B231").Value)
For i = LBound(dirArray) To Ubound(dirArray)
If instr(dirArray(i),"xls") > 0 Then '<== very basic check
Workbooks.Open(dirArray(i)) '<== should be full filepath of file
' Do stuff
End If
Next
answered Nov 14 '18 at 10:10
QHarrQHarr
32.3k82042
edited Nov 14 '18 at 10:15
answered Nov 14 '18 at 10:10
QHarrQHarr
32.3k82042
answered Nov 14 '18 at 10:10
QHarrQHarr
32.3k82042
answered Nov 14 '18 at 10:10
QHarrQHarr
32.3k82042
32.3k82042
What do you mean with " '<== should be full filepath of file"? Do I simply copy the filepath to this line? If I don't add anything I get the error "sorry we could'nt find filename. Is it possible it was moved, renamed or deleted?"
– G. Koen
Nov 14 '18 at 10:25
The cells you are reading in from should contain the full file path to the file I believe C:Userspath.....workbookX.xlsx.
– QHarr
Nov 14 '18 at 10:34
Did you try that?
– QHarr
Nov 14 '18 at 11:39
I'm not sure I understand. The files that have to be read are all in the same directory, C:Userspath...workbookX.xlsx. Each one of these workbooks is defined in sheet 4 range B3:B231 with just the filename itself. workbook1.xlsx, workbook2.xlsx, etc. .
– G. Koen
Nov 15 '18 at 5:52
You need concatenate the path on the name before passing to workbooks.open
– QHarr
Nov 15 '18 at 6:23
|
show 1 more comment
What do you mean with " '<== should be full filepath of file"? Do I simply copy the filepath to this line? If I don't add anything I get the error "sorry we could'nt find filename. Is it possible it was moved, renamed or deleted?"
– G. Koen
Nov 14 '18 at 10:25
The cells you are reading in from should contain the full file path to the file I believe C:Userspath.....workbookX.xlsx.
– QHarr
Nov 14 '18 at 10:34
Did you try that?
– QHarr
Nov 14 '18 at 11:39
I'm not sure I understand. The files that have to be read are all in the same directory, C:Userspath...workbookX.xlsx. Each one of these workbooks is defined in sheet 4 range B3:B231 with just the filename itself. workbook1.xlsx, workbook2.xlsx, etc. .
– G. Koen
Nov 15 '18 at 5:52
You need concatenate the path on the name before passing to workbooks.open
– QHarr
Nov 15 '18 at 6:23
What do you mean with " '<== should be full filepath of file"? Do I simply copy the filepath to this line? If I don't add anything I get the error "sorry we could'nt find filename. Is it possible it was moved, renamed or deleted?"
– G. Koen
Nov 14 '18 at 10:25
What do you mean with " '<== should be full filepath of file"? Do I simply copy the filepath to this line? If I don't add anything I get the error "sorry we could'nt find filename. Is it possible it was moved, renamed or deleted?"
– G. Koen
Nov 14 '18 at 10:25
The cells you are reading in from should contain the full file path to the file I believe C:Userspath.....workbookX.xlsx.
– QHarr
Nov 14 '18 at 10:34
The cells you are reading in from should contain the full file path to the file I believe C:Userspath.....workbookX.xlsx.
– QHarr
Nov 14 '18 at 10:34
Did you try that?
– QHarr
Nov 14 '18 at 11:39
Did you try that?
– QHarr
Nov 14 '18 at 11:39
I'm not sure I understand. The files that have to be read are all in the same directory, C:Userspath...workbookX.xlsx. Each one of these workbooks is defined in sheet 4 range B3:B231 with just the filename itself. workbook1.xlsx, workbook2.xlsx, etc. .
– G. Koen
Nov 15 '18 at 5:52
I'm not sure I understand. The files that have to be read are all in the same directory, C:Userspath...workbookX.xlsx. Each one of these workbooks is defined in sheet 4 range B3:B231 with just the filename itself. workbook1.xlsx, workbook2.xlsx, etc. .
– G. Koen
Nov 15 '18 at 5:52
You need concatenate the path on the name before passing to workbooks.open
– QHarr
Nov 15 '18 at 6:23
You need concatenate the path on the name before passing to workbooks.open
– QHarr
Nov 15 '18 at 6:23
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53297528%2ffor-each-open-specified-workbooks-in-array%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
stackoverflow.com/a/52467171/5448626
– Vityata
Nov 14 '18 at 10:11