Using variables to reference workbook filenames

[IanWallbridge]

Hi all,

I've written some VBA which manipulates data in two existing workbooks, and am trying to add some extra functionality - but am struggling to get this to work using variables to replace hard coded filenames.

Within the code I have the following:

With extwbk.Sheets("Sheet1")
    myLSFinalRow = .Cells(.Rows.Count, 2).End(xlUp).Row
End With


Workbooks(extwbk).Worksheets("Sheet1").Range("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "000000"


'Populate LS Leaver
For RowCount = 2 To myLSFinalRow


    y = Application.VLookup(Workbooks("odc_full_user_list___macro_backup_report.xlsx").Worksheets("Sheet1").Range("A" & RowCount), _
    Workbooks("Daily SIP report.xls").Worksheets(mySIPWorksheet).Range("$A$2:$O$7000"), 4, False)
    
    If (Workbooks("odc_full_user_list___macro_backup_report.xlsx").Worksheets("Sheet1").Range("O" & RowCount) = "Staff" _
    Or Workbooks("odc_full_user_list___macro_backup_report.xlsx").Worksheets("Sheet1").Range("O" & RowCount) = "Contractor") _
    And Workbooks("odc_full_user_list___macro_backup_report.xlsx").Worksheets("Sheet1").Range("T" & RowCount) = "Active" _
    And IsError(y) Then
        Sheets("LS Leaver").Range("user_id")(RowCount - 1) = Workbooks("odc_full_user_list___macro_backup_report.xlsx").Worksheets("Sheet1").Range("A" & RowCount)
        Sheets("LS Leaver").Range("name")(RowCount - 1) = Workbooks("odc_full_user_list___macro_backup_report.xlsx").Worksheets("Sheet1").Range("C" & RowCount)
        Sheets("LS Leaver").Range("title")(RowCount - 1) = Workbooks("odc_full_user_list___macro_backup_report.xlsx").Worksheets("Sheet1").Range("D" & RowCount)
    End If
Next RowCount

The variable 'extwbk' which appears in the first two paragraphs is interchangeable with the actual workbook name "odc_full_user_list___macro_backup_report.xlsx" - and as the code stands, everything works as expected whether I use the filename or the variable.

If however I make exactly the same substitution into either the Application.Vlookup or the If statements - so that for example the If statement begins

If (Workbooks(extwbk).Worksheets("Sheet1").Range("O" & RowCount) = "Staff"

then the code doesn't work as expected, although I don't get any error messages as such. I'm slightly confused by this, as (in the If statement at least) I'm using exactly the same convention as in the second paragraph.

Can anyone advise? Note that I'm working with a xlsx file here, and I believe that these require more precise VBA syntax than older Excel files.

If there's any other info that's required, please let me know.

Many thanks,

Ian

 

[Fluff]

How have you declared "extwbk" & how have you assigned it?
Also do you have any error handling in the code?

 

[IanWallbridge]

extwbk is Dim As Variant, and it's set as follows:

myLSSource = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS*), *.XLS*", Title:="Select 'odc_full_user_list__macro_backup_report' to be opened")
If myLSSource = False Then Exit Sub

Set extwbk = Workbooks.Open(myLSSource)

I'm not sure why I did it this way, but hindsight is a wonderful thing.
Error handling is just On Error Resume Next

 

[Fluff]

Ok, lines like

Workbooks(extwbk).Worksheets("Sheet1")

this should be

extwbk.Worksheets("Sheet1")

as you have on the first line of code you posted.

Error handling is just On Error Resume Next

I would recommend getting rid of that. All it does is hide any errors, making it a lot more difficult to debug.

 

[IanWallbridge]

Many thanks! This has solved the problem. I've also taken your advice about removing 'On Error Resume Next' - it did then throw up some other issues, but at least my code is now more correct as a result of proper debugging.

 

[Fluff]

Glad to help & thanks for the feedback

 

[IanWallbridge]

Actually I spoke too soon - so I'd really appreciate your help with a supplementary question.

In the code that I originally posted there's a variation on the section below, which is intended to access one of the workbooks and to correct a 'Number stored as text' issue in Column A on the first worksheet.

extwbk.Worksheets("Sheet1").Range("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "000000"

Whatever way I write the Select statement, I get 'Run time error 1004 - Select method of Range class failed'. As previously, extwbk is Variant, and the file type is .xlsx

Can you advise what I'm doing wrong, or suggest a different way of writing the Select statement?

Many thanks,

Ian

 

[Fluff]

You cannot select something unless it is on the activesheet of the activeworkbook. That said there is very rarely any need to use Select.
Try

With extwbk.Worksheets("Sheet1").Range("A:A")
    .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    .NumberFormat = "000000"
End With

 

[IanWallbridge]

Thanks! this works perfectly, plus I've learnt something about Selects. Macro works end to end so no more questions

 

[Fluff]

You're welcome & thanks for the feedback