View Full Version : What's wrong with this code?
kualjo
03-16-2011, 07:33 AM
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
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
03-16-2011, 08:06 AM
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.