How to pass an Excel worksheet name to an Access subroutine?

2
votes

I'm trying to loop through all the worksheets in an Excel file using Access VBA.

The subroutine needs to select the first row and set the RowHeight in each worksheet.

I'm using a string variable to call the subroutine and passing the worksheet name to it. It works the first time but the next time I get

"Select method of Range Class Failed"

I tried moving the variable declarations around, changing where I open Excel (it opens twice if I put it in the subroutine) and doing it as function instead of a subroutine.

'My object and worksheet variables are declared at the top of the object:

Dim objExcel As Object
Dim wks As Worksheet
Dim wkb As Workbook

'I'm opening Excel and setting the workbook object in a subroutine:

Set objExcel = CreateObject("Excel.Application")  'Excel is invoked 01
Set wkb = objExcel.Workbooks.Open(strOutputPathAndFileName)
objExcel.Application.Visible = True

'Then calling the subroutine to set the row height and wrap text property

FirstRowHeightAndWrap ("ChangeTracking")
FirstRowHeightAndWrap ("FivePCalcsThisPPE")

'Here's the function

Function FirstRowHeightAndWrap(strSheetName As String)

    Set wks = wkb.Sheets(strSheetName)
    With wks
        .Rows(1).Select
        .Rows(1).RowHeight = 28
        .Rows(1).WrapText = True
    End With

End Function

It's something about setting the wks variable in a different place than the objExcel and wkb variables, clearly, 'cuz it works if I do it all in one place.

2
excelvbams-access
And sorry...I was referring to it as a sub sometimes in the above and a function in others...any help is appreciated, I've been going batty with this! – MBReed
You can't select a range on a sheet which isn't active, so add .Activate at the top of your With block. Do you really need to select the range though? Typically it's not needed. – Tim Williams
As @TimWilliams suggested remove.Rows(1).Select. Don't use .Select at all except you prepare a useraction. It only appears, because some people use the macro recorder to create code and other people copy it without understanding – ComputerVersteher

2 Answers

1
votes

All you need is to loop the worksheets like this:

Public Function SetRowHeight()

    Dim objExcel    As Object
    Dim wkb         As Workbook
    Dim wks         As Worksheet

    Set objExcel = CreateObject("Excel.Application")
    Set wkb = objExcel.Workbooks.Open("c:\test\test.xlsx")

    For Each wks In wkb.Worksheets
        wks.Rows(1).RowHeight = 28
        wks.Rows(1).WrapText = True
    Next

    wkb.Close True
    Set wkb = Nothing
    objExcel.Quit

    Set objExcel = Nothing

End Function
1
votes

Why does it not work?

It's something about setting the wks variable in a different place than the objExcel and wkb variables, clearly, 'cuz it works if I do it all in one place.

The main reason why your function call does not work is because you are trying to use a variable that is outside it's scope. The variables that you declare and set inside a sub/function are local to that sub/function.

In your case, the wkb variable can only be referenced within the sub where it was declared and set. Once you call the FirstRowHeightAndWrap, you will get an Object required error because access does not know what the variable wkb is.

You can see this process by using the View > Locals Window and executing your code line by line, and you will notice that your existing local variables show up as Empty once it reaches a new sub/function call.

How can you fix it?

1. A fix would be to pass the wkb object as a reference in your function. In other words you would need to adjust the lines as follow :

In your sub ...

 FirstRowHeightAndWrap wkb, "ChangeTracking"
 FirstRowHeightAndWrap wkb, "FivePCalcsThisPPE"

You will need to also change the function header and function to:

If you want to loop for all worksheets ...

Function FirstRowHeightAndWrap(ByRef wkb As Object)

For Each wks In wkb.Worksheets
    wks.Rows(1).RowHeight = 28
    wks.Rows(1).WrapText = True
Next

End Function

If you want to keep your original function with the specific sheetname parameter ...

Function FirstRowHeightAndWrap(ByRef wkb As Object, strSheetName As String)

    Set wks = wkb.Sheets(strSheetName)

    With wks
        .Rows(1).RowHeight = 28
        .Rows(1).WrapText = True
    End With

End Function

2. The easier solution, especially in cases where you are using simple functions, is to dump everything in the same sub so you don't have to pass a reference with each function call.

Also as mentioned in the comments, you do not need the .Select line in your function. I would also recommend using late binding for your variables in order to prevent any possible issues with references if you have other users using your application.

Good luck! :)