CODEWITHSUNDEEP
excelvba
Marc
Marc

Reputation: 25

Openning workbook with FilePicker in VBA throws compile error

I define workbook as variable with FilePicker. At the top I defined my variable as public and string. I tried to declare it as workbook but it doesnt work. Having it defined as string when I try to get into this workbook in another module I get the following compile error:

Invalid qualifier.

Any suggestions what is wrong?

Public wipreport As String

sub wip()

With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        .Title = "Select WIP Report"
        .ButtonName = "OK"
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        'Store in fullpath variable
        wipreport = .SelectedItems.Item(1)
    End With

end sub

Line of code in another module, where I get this compile error:

wipreport.Worksheets("1. WIP report").Select

Thank You for help.

Upvotes: 1

Views: 101

Answers (2)

EylM
EylM

Reputation: 6103

FileDialog returns the file name as String object, but you are trying to use it as Workbook. If the user has selected the file, then you first need to open it and then try to select the sheet:

Set wb = Workbooks.Open(wipreport) 'open the file
wb.Worksheets("1. WIP report").Select ' your code

Upvotes: 2

Vityata
Vityata

Reputation: 43575

Compile error is the best error you may have - the compiler tells explicitly it does not work. In this case, wipreport.Worksheets("1. WIP report").Select could not work, becase wipreport is of type String and it does not have Worksheets property.

Probably try something like this, it will work if wipreport is the name of the opened Excel file:

Workbooks(wipreport).Worksheets("1. WIP report").Select

If wipreport is a path of the file, then openning it is a better option:

Workbooks.Open wipreport

or even, setting a variable to it:

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=wipreport)

And while on the subject, there is a reason that this question has the highest number of votes in #VBA in StackOverflow - How to avoid using Select in Excel VBA.

Upvotes: 2

Related Questions