What's wrong with this code?

[kualjo]

I borrowed this straight out of the VBA book I use, modified the variable names, but it still gives me a "Run-time error '91': Object variable or With block variable not set" message. It gets as far as showing the Open File dialog, but whether I choose a file or Cancel, this error appears. Is there something wrong with this?

Dim upfile As Excel.Workbook
Dim datafile As Excel.Workbook

Sub GetExpFile()

Set upfile = ActiveWorkbook
With Application
datafile = .GetOpenFilename(, , "Get Export File")
End With
If datafile = "False" Then
Exit Sub
End If

Application.ScreenUpdating = False
Workbooks.Open datafile
upfile.Activate
Application.ScreenUpdating = True

End Sub

[GTO]

Greetings,

The error is caused by declaring 'datafile' as a Workbook. .GetOpenFilename returns the fullname (that is, the path and filename) of the file/workbook selected. I hope that your code is not exactly as shown in any book about vba!

Just given the code snippet you supplied, I see no reason to declare the variables at a scope beyond the procedure. Also, regardless of what type of naming convention you come to like, I would suggest against alllowercasenamed variables, BecauseTheyAreHarder to read.

Option Explicit

Sub GetExpFile()
Dim wbUpFile As Excel.Workbook
Dim wbDataFile As Workbook
Dim strDataFileName As String

Set wbUpFile = ActiveWorkbook

strDataFileName = Application.GetOpenFilename(, , "Get Export File")

If strDataFileName = "False" Then
Exit Sub
End If

Application.ScreenUpdating = False
Set wbDataFile = Workbooks.Open(strDataFileName)
wbUpFile.Activate
Application.ScreenUpdating = True
End Sub

[kualjo]

Actually, the declarations were not part of what I got from the book, just the sub. Sorry about that! The reason that I declare them at the higher scope is because they will be used in other procedures. I get what you are saying about the string vs workbook distinction, so I will give it a try and see how it turns out. If I run into any other snags, I'll be back. Thanks!

[GTO]

Glad to help :-)