Trying to iterate through some workbooks from a list of workbooks, getting out of range errors

Question

I have a problem. I'm guessing its easier to first write the code, and then explain it so here goes:

Sub Test()
   Dim myHeadings() As String
   Dim i As Long
   Dim path As String
   Dim pathtwo As String
   Dim currentWb As Workbook
   Dim openWb As Workbook
   Dim openWs As Worksheet
   Set currentWb = ActiveWorkbook
   path = "C:\pathto\"
   pfile = Split("File1,File2,File3", ",")
   myHeadings = Split("Januari,Februari,Mars,April,Maj,Juni,Juli,Augusti,September,Oktober,November,December", ",")

 For j = 0 To UBound(pfile)
    pathtwo = path & pfile(j) & ".xlsx"
    i = 0
    If IsFile(pathtwo) = True Then
        For i = 0 To UBound(myHeadings)
            Set openWb = Workbooks.Open(pathtwo)
            Set openWs = openWb.Sheets(myHeadings(i))
            If openWs.Range("C34") = 0 Then
                currentWb.Sheets("Indata").Cells(70, i  + 27 + 12*j.Value = ""
            Else
                currentWb.Sheets("Indata").Cells(70, i + 27 + 12*j).Value = openWs.Range("C34")
            End If
       Next i
     End if
     Workbooks(openWb.Name).Close
Next j
End sub

What I want to pick a file from the pfile list, iterate through all its sheets defined in myHeadings and deduct the value at C34 (in reality there are plenty more values deducted, but to keep it short). After this I want to Close the file, go to the next file and do the same thing until all the Three files (again, in reality there are more, which some of them does not exist yet).

The function "IsFile" is

Function IsFile(fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
   On Error Resume Next
   IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function

written by iDevlop at stackoverflow, in this thread: VBA check if file exists

The reason why I have

 currentWb.Sheets("Indata").Cells(70, i + 27 + 12*j).Value = openWs.Range("C34")

is because I want to start to write my data into currentWb at AA70 (Row 70, column 27). j*12 is because it is "periodic" depending on which file it is (the file file1 corresponds to 2015, file2 to 2016 etc), and hence in my summary I have it month and yearwise.

The problem arises though when I run this macro, at the first file at the sheet Mars I get out of range, but Before I added the iteration of files, there was not any subscript out of range at the first file. Is there anyone who can see how this can be?

Please note that indentation and so on may be somewhat off as I copied this from a much larger file with many rows in between with irrelevant code.

One thing that looks off is you're opening the workbook for each heading instead of for each file. I don't know why you'd get errors on the third sheet with that issue though. — Sobigen, Mar 17 '15 at 13:34
just a thought, instead of doing path ="C:\..." try path = Dir("C:\...") — user3271518, Mar 17 '15 at 13:38
Are you sure the sheet is named "Mars"with no spaces? Often a unique error like that can be a typo. — Doug Glancy, Mar 17 '15 at 14:19
@Sobigen How do you mean? Are you referring to Set openWs = openWb.Sheets(myHeadings(i)) inside the loop? That is for the sheet, where myHeadings includes the names of the sheet. As I'm new to VBA I may have misunderstood my code though — Cenderze, Mar 17 '15 at 14:59
Thanks user3271518 Will do that! Thanks, didn't know about Dir. @DougGlancy I'm positive it has no spaces, as the reference to the sheet string worked until I included the "for j" loop — Cenderze, Mar 17 '15 at 15:00
I would have thought the line Set openWs = openWb.Sheets(myHeadings(i)) would be outside the i loop since pathtwo is only changing with the j loop. It might not change anything functionally it just looked redundant since you were trying to open an already open workbook since you close it outside the i loop. — Sobigen, Mar 17 '15 at 15:28
@Sobigen Thanks for reply. Hm, then I may have misunderstood how loops works in VBA. I thought that, with my syntax, I first open File1 along with sheet Januari and deduct some values. I then open up sheet Februari in the same file and deduct some Changes. Then after I've opened up december, I close File1, and then open up File2 and sheet Januari, and deduct some values until december, where I close file2, open up file 3 etc. If openWs = openWb.Sheets(myHeadings(i)) can be outside the i loop then I've misunderstood how VBA "prioritze" loops? — Cenderze, Mar 17 '15 at 15:31
It looks like you start the j loop, check for the file, start the i loop, open the workbook, process the sheet and then restart the i loop, opening the workbook and processing the sheet 12 times total. Finish the i loop, close the workbook and restart the j loop. I would have thought you would check the file, then open the workbook then start the i loop. You accepted an answer so maybe you're problem is fixed either way. — Sobigen, Mar 17 '15 at 17:33
@Sobigen Aaah now I got it! Since I have openWb = Workbooks.open(pathtwo) _inside_ the i loop! Yes, I've accepted an answer, but help like this is much appreciated. The program runs rather fast at the moment, but that is with having only the files corresponding to 2015 and 2016 to open. Later I will open files up to 2022, where thanks to you I'll open as many files as I do now, instead of 50ish — Cenderze, Mar 18 '15 at 07:02

user3271518 · Accepted Answer · 2015-03-19T19:47:50.587

1

This isnt the right answer for your specific question but this is how I have done something similar and might help you to see how i did it. Basically what this is doing is opening up a CSV and copying the entire sheet and pasting it into a workbook. I was consolidating like 20 CSV dumps into 1 workbook to make it easier to dig through the stuff.

Regarding Dir()

You can invoke Dir with 2 arguments or with no arguments. You initialize it with 2 arguments the pathway and the attributes (which is optional). The 2nd time I am calling Dir in this sub it is without any arguments. What this does is fetch the subsequent files.

Sub Add_Sheets()

Dim ws As Worksheet
Dim PasteSheet As Worksheet
Dim wb As Workbook

Set wb = Application.Workbooks.Open("C:\Users\Desktop\CSV\All.xlsx") 'Location of where you want the workbook to be

StrFile = Dir("c:\Users\Desktop\CSV\*.csv") 'Dir of where all the CSVs were. 
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        Application.Workbooks.Open ("c:\Users\Desktop\CSV\" & StrFile)
        Set ws = ActiveSheet
        ws.Range("A1:C" & rows.Count).Select 'Selecting Specific content on the worksheet
        Selection.Copy
        wb.Activate
        wb.Worksheets.add(After:=Worksheets(Worksheets.Count)).name = StrFile 'Setting the sheet name to the name of the CSV file
        Range("A1").PasteSpecial Paste:=xlPasteValues
        StrFile = Dir 
    Loop

End Sub

edited Mar 19 '15 at 19:47

answered Mar 17 '15 at 13:40

user3271518

628
3
13
27

Thanks for taking your time. Trying to understand the code, StrFile is a vector whose elements are strings of file names ending in .csv from that specific folder? If so, how do you remove each string once it has exited the loop (so that Len(StrFile) is not Always > 0 provided it was greater than 0 to begin with)? With how you set the sheet name to be the name of the CSV file, I'm guessing you somehow use a FIFO system where you loop over the first string in StrFile, remove it, and go onto the next? – Cenderze Mar 17 '15 at 15:10
hey so because the I used *.csv it actually loops through every CSV file in the folder. then to make sure that Len(StrFile) isnt always > 0 I redefine StrFile = Dir before the end of the loop which then makes it one less than before. I hope that helps – user3271518 Mar 17 '15 at 17:43
Thanks for answering. I can't see how StrFile = Dir doesn't return the same value all the time, since there has not been a "StrFile.remove(first element)" or how to say. Adding MsgBox (Len(StrFile)) also returned the same value for me inside the loop all the time even though StrFile = Dir was present. – Cenderze Mar 18 '15 at 07:00
just ran this with some test data and Debug.Print StrFile does print every file name in the folder. Also updated my answer with some more info. – user3271518 Mar 19 '15 at 19:44

Trying to iterate through some workbooks from a list of workbooks, getting out of range errors

1 Answers1