CODEWITHSUNDEEP
excelms-accessvba
Chris2015
Chris2015

Reputation: 1050

Run-time error '1004' Method 'Worksheets' of object'_Global' failed

I believe i have an unqualified reference issue with the code below, based on what I've read. I think what I need to do is reference the workbook specifically however I've tried a number of ways of doing this, all unsuccessfully. Is anyone able to assist?

Dim f As FileDialog, str As String
Set f = Application.FileDialog(msoFileDialogFilePicker)
f.Show
str = f.SelectedItems(1)
    
Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Visible = True
xl.Workbooks.Open (str)
                    
Dim wsT As Worksheet, wsA As Worksheet, wsE As Worksheet, wsI As   Worksheet, lr As Long, lrow As Long
            
Worksheets.Add(After:=Worksheets(1)).Name = "TABLE"
                                   
Set wsA = Worksheets("ACTIVE")
Set wsT = Worksheets("TABLE")

One example, of what I've tried was

Set wb = xl.workbooks.open(str)

Upvotes: 0

Views: 3453

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

You were almost there. Just define the variables first.

Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Visible = True

Dim xlWB as Excel.Workbook '** added this line
Set xlWB = xl.Workbooks.Open(str)

Rest of code looks good at first glance

Upvotes: 1

Related Questions