Excel – help correcting subscript out of range error

[ron513]

I require some help with an error I receive when a macro executes.
The purpose of the macro is to copy data from a workbook the user selects to the workbook the macro is in. It loops though the sheets and copies data from specific cells on each sheet to a row, one sheet to each row.
The error is on line: fSheetCount = Workbooks(fName).Worksheets.Count
The error is: Run-time error’9’ Subscript out of range.
It works if the file to be copied from is also open.
Your help will be greatly appreciated. I’m an amateur so any suggestion to improve the code and add error handling is also welcome.

Thanks,

Ron

Sub CopyBoe()

Dim taskID As String
Dim resource As String
Dim pValue As String
Dim sDate As String
Dim eDate As String
Dim rCount As Integer
Dim i As Integer
Dim ppTempFileName As String
Dim fSheetCount As Integer
Dim fName As String
Dim fileToOpen As String

ppTempFileName = ThisWorkbook.Name

fileToOpen = Application _
    .GetOpenFilename("excel files (*.xls), *.xls")

fName = Dir(fileToOpen)

fSheetCount = Workbooks(fName).Worksheets.Count 

rCount = 3

For i = 1 To fSheetCount
    Windows(fName).Activate
     Sheets(i).Select
    
    taskID = Cells(2, 5).Value
    resource = Cells(4, 2).Value
    pValue = Cells(7, 8).Value
    sDate = Cells(6, 2).Value
    eDate = Cells(6, 4).Value
    
    Windows(ppTempFileName).Activate
    Sheets("Resources (Spread or Load)").Select
    Cells(rCount, 1).Value = taskID
    Cells(rCount, 2).Value = resource
    Cells(rCount, 11).Value = pValue
    Cells(rCount, 12).Value = sDate
    Cells(rCount, 13).Value = eDate
    
    rCount = rCount + 1
    
    Next i

End Sub

 

[PHV]

You have to open the workbook and then fName=Activeworkbook.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ron513]

Thanks PH.
That was the problem.
Ron

 

[firefytr]

Hi there,

No need for all that activating and such, you could use something like this ...


Sub CopyBoe()
Dim wb As Workbook, fullName As String, fileName As String
Dim i As Long, fileToOpen As Variant
fileToOpen = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If TypeName(fileToOpen) = "Boolean" Then
MsgBox "Cancel was pressed!"
Exit Sub
End If
fullName = Dir(fileToOpen)
fileName = Right(fullName, Len(fullName) - InStrRev(fullName, "\"))
If IsWbOpen(fileName) Then
Set wb = Workbooks(fileName)
Else
Set wb = Workbooks.Open(fullName)
End If
rCount = 3
For i = 1 To wb.Worksheets.Count
With ThisWorkbook.Sheets("Resources (Spread or Load)")
.Cells(rCount, 1).Value = wb.Sheets(i).Cells(2, 5).Value
.Cells(rCount, 2).Value = wb.Sheets(i).Cells(4, 2).Value
.Cells(rCount, 11).Value = wb.Sheets(i).Cells(7, 8).Value
.Cells(rCount, 12).Value = wb.Sheets(i).Cells(6, 2).Value
.Cells(rCount, 13).Value = wb.Sheets(i).Cells(6, 4).Value
End With
Next i
End Sub


You don't really need that rCount, btw, it will be the wb.Worksheets.Count you could use instead.

HTH

-----------
Regards,
Zack Barresse

 

[firefytr]

Oh, and this is the function used ...

Public Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
End Function

-----------
Regards,
Zack Barresse

 

[ron513]

Zack,

It works great and the code looks better and more efficient, I assume. I have much to learn, thanks for helping with my learning process.

I used the rCount variable for the starting row in the workbook the data is copied to (macro installed in) then it is incremented by one for each loop. This allows the data from each sheet of the workbook the user opens to be copied to a new row. Is that the correct way to accomplish that?

Ron

 

[firefytr]

I would handle it differently, actually. If you're wanting to go to the first available row in say column A, you could use something like this ...

With Workbooks("book1.xls").sheets("sheet1")
    LastRow = .Cells(.Rows.count, 1).end(xlup).row
End With

The "1" in there is saying column A. You *could* use "A" (using quotes) instead of 1, but in Excel 2007 it will bomb on you. So to keep forward compatability, it's best to keep it as a numeric. (IMHO)

There are, of course, many other ways to find the last row, some methods will differ their outcomes depending on if you have hidden rows or not also.

HTH

-----------
Regards,
Zack Barresse

 

[ron513]

Zack

Thanks for the last row example but it is not needed for my situation. It will always start on the third row and the data from each sheet will be on a row.

A question about you code.
What is the purpose of the statement:
fileName = Right(fullName, Len(fullName) - InStrRev(fullName, "\"))
It appears to create the same value as the statement:
fullName = Dir(fileToOpen)

Ron

 

[firefytr]

Sure. fullName will return the entire (full name) string of the file; this includes the full path and the full name. If you want to test if the workbook is open then you just need the file name (i.e. Book1.xls, MyWorkbook.xls, etc), that is what that line does. I'll break it down for you...

Example of using fullName = "C:\Folder\Book1.xls"

Using the Right() function:
fileName = Right(fullName, Len(fullName) - InStrRev(fullName, "\"))

The first syntax of Right() is the string being looked at, which is specified as fullName variable, which I just explained above. The next syntax is the number of characters to show starting from the right. So if I did Right(fullName, 1) it would return "s", and if I did Right(fullName, 4) it would return ".xls", etc.

So we need a way to find the last "\" in the string, because that is where the filename starts. That is where the InStrRev() function comes into play. It is just the opposite as InStr() fx as it will parse a string from right to left looking for the delimiter you specify, in this case our "\" character, but it will return the character position (from left to right). So in our example, InStRev(fullName, "\") would return 10. (If we used InStr() it would return 3).

Now, to count only those characters to the right of the last "\" (in position 10) we need to take:
Total length of string -
character position of "\" =
Length of book name
So our Len(fullName) = 19. That then means that if we take 19 - 10, we get 9, the total length of our name ("Book1.xls"), 9 characters. That is what we use as our second syntax in the Right() fx.

Does this make sense?

-----------
Regards,
Zack Barresse

 

[PHV]

I personnally would replace this:
fullName = Dir(fileToOpen)
fileName = Right(fullName, Len(fullName) - InStrRev(fullName, "\"))
with this:
fullName = fileToOpen
fileName = Dir(fileToOpen)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Ah, yes it would be, woudln't it! Thanks for correcting me PHV.

-----------
Regards,
Zack Barresse