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

Question

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.

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, Aug 17 '19 at 06:09
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, Aug 17 '19 at 07:02
As @TimWilliams suggested remove`.Rows(1).Select`. [Don't use .Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/10718179#10718179) 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, Aug 17 '19 at 07:22

score 1 · Answer 1 · answered Aug 17 '19 at 11:07

1

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

answered Aug 17 '19 at 11:07

Gustav

53,498
7
29
55

1

Thanks! After thinking on it for a while I realized each sheet also has a number so I can loop through them that way too. Your solution is even more elegant and worked beautifully. I also added an instruction to Freeze Panes Top Row and two other functions, one to autofit the number of columns passed to it (from 1 to that number), and another to set the column width to a specific value based on a starting and ending numbered column passed it. Hot stuff, thanks so much!! – MBReed Aug 18 '19 at 02:18
1

@MBReed Notice that Gustav didn't use the index (number), but the [_NewEnum](https://riptutorial.com/vba/example/18935/vb--var-usermemid) property of Worksheets-Collection, that allows to use`For Each`Loop. – ComputerVersteher Aug 18 '19 at 02:50

hisoka · Accepted Answer · 2019-08-18T02:12:02.023

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! :)

Note that Op declares the objects at module-level, what gives them sufficent scope. Without the select the code runs fine (tested).. But good advice pasing the ref to make methode reuseable.. — ComputerVersteher, Aug 18 '19 at 02:22
Well I stand corrected! I got so focused on the last portion of the post about the scope, that I didn't notice that OP was using global variables. — hisoka, Aug 18 '19 at 02:52
Most likely OP benefits from your expaination! And you are not alone ;) I just noticed (because of comment), that initial request was to edit all worksheets, as Gutav did in his answer ;) — ComputerVersteher, Aug 18 '19 at 03:00

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

2 Answers2