0

I'm trying to open a file to access information in the third worksheet. The .FileDialog works fine and I can select a file, but I keep getting run-time error '91' because of the line Set wbR = wb.Worksheets(3) near the bottom of this section of code because wb.Worksheets(3) = <Object variable or With block variable not set>. This leads me to believe my Set wb = Workbooks.Open(myFile) does not work and returns Nothing, but after looking through how other people have opened files using .FileDialog, I can't see how mine is different and wouldn't function. Any help or advice would be much appreciated.

'Set variables
Dim wb As Workbook 'Workbook to open
Dim wbR As Worksheet 'This is the raw data on the new workbook
Dim wsL As Worksheet 'Worksheet in current file
Dim myFile As String 'File to open
Dim FilePicker As FileDialog

'Set light chain hit worksheet
Set wsL = ThisWorkbook.Worksheets(3)

'Optimizes Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Retrieve target file
Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)

'Opens folder-picking window
With FilePicker
    .Title = "Select a file."
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myFile = Dir(.SelectedItems(1))
End With

'If folder is not selected
NextCode:
    myFile = myFile
    If myFile = "" Then GoTo ResetSettings

'Set variable equal to opened workbook
Set wb = Workbooks.Open(myFile)
'Ensure Workbook has opened before moving on to next line of code
DoEvents
'Sets worksheet for importing
Set wbR = wb.Worksheets(3)

*the macro continues past this last line but it doesn't get to it yet because of this error

MTJ
  • 99
  • 1
  • 3
  • 13
  • 1
    Do you have 3 worksheets? Do you maybe mean `set wbR = wb.Worksheets("Sheet3")`? Also, while debugging, keep the `ScreenUpdating` ON...then step through the code with `F8` and see if your `wb` workbook actually opens. – BruceWayne May 24 '17 at 19:15
  • Try something like `If Not wb is Nothing Then Set wbR = wb.Worksheets(3)` This will prevent it from trying to operate on wb if it isn't set. This will then confirm or deny that this is the source of the issue. – Brandon Barney May 24 '17 at 19:16
  • I don't get an error with your code. Make sure both workbooks have 3 worksheets in them. Brandon is correct about being safe when setting workbooks, but since it worked fine on my machine I think that may not be the issue. – John Muggins May 24 '17 at 19:19
  • Agreed. It is most likely that the workbook being opened doesn't have three sheets in the Worksheets collection. This could be confirmed with something like `Debug.print wb.Worksheets.Count` – Brandon Barney May 24 '17 at 19:22
  • Hmmmm. I tried deleting one of the sheets on the opened WB and got an error 9, not 91. 91 is "object not set" while 9 is "Subscript Out of Range." So it may be that wb is in deed not set.... I can only say when both WBs have 3 sheets then it works fine for me. – John Muggins May 24 '17 at 19:24
  • @JohnMuggins That makes sense. It should give a subscript error if you are trying to use an index that is outside of the existing indices. Object variable not set should generally occur when the referenced object is 'Nothing'. – Brandon Barney May 24 '17 at 19:27
  • 1
    @MTJ, put a pause line on the line throwing the error. The program will stop before it executes that line. Hold your cursor over the "wb" and see if the little textbox says that it equals nothing. – John Muggins May 24 '17 at 19:33
  • @JohnMuggins Both workbooks have more than eight sheets. I removed the `Application.ScreenUpdating = False` as BruceWayne said and stepped through the code, and the workbook did not open. When holding my cursor over the `wb` it shows `wb = Nothing`. Issue seems to be with opening `myFile`. – MTJ May 24 '17 at 20:21
  • OK. Debugging is pretty easy. Now put the pause at the very first executable line, which is "Set wsL = ThisWorkbook.Worksheets(3)". After this you can run each line one at a time until you see where it doesn't work by pressing PF8. After each line executes, hold your cursor over each variable to see if it was set correctly. A little box should pop up over each variable showing its value when you hover over it. Keep pressing PF8 until you find the line that is not working. – John Muggins May 24 '17 at 20:28
  • @JohnMuggins Whoops, wasn't clear in my last comment. What I meant when saying I was holding my cursor over the `wb`, was that the line `Set wb = Workbooks.Open(myFile)` doesn't appear to be working properly. This has me quite confused because `myFile` is the path and file (checks out when stepping through each line). Also discovered `Set wsL = ThisWorkbook.Worksheets(3)` returns `wsL = Nothing`, but that's a different issue. – MTJ May 24 '17 at 21:04

1 Answers1

0

Sorry, I have to use the answer box to do this because there is code involved. But No, you're doing fine. This is how debugging works. Now that we know which lines are not working we will try to find out why. Now try to change 

wsL = ThisWorkbook.Worksheets(3)

to 

wsL = ThisWorkbook.Worksheets(1)

or 

wsL = ThisWorkbook.Worksheets("PUTnameOFsheetHere")

to see if it sets or not. If so then we know there is some kind of problem with WorkSheets(3).

Now we'll try to find why "Set wb = Workbooks.Open(myFile)" is not working. Tunr on your macro recording. Got to "File" on the menu and select "Open." The dialog box will open. Search for your file in the dialog box and open it. Go back to your original workbook and stop recording. Find the recorded macro and you will see the exact line That Excel used to open the file. It should be something like:

Workbooks.Open Filename:="C:\Files\CNC TEST.xlsx"

Now run your script with PF8 and hover over "myFile" after it is initialized. Does it's value match the path and filename of the recorded macro file?

John Muggins
  • 1,198
  • 1
  • 6
  • 12
  • I actually determined the source of the error was the line `Set wb = Workbooks.Open(myFile)`. I [posted another question](https://stackoverflow.com/questions/44169301/vba-workbook-openfile-returns-nothing?noredirect=1#comment75355972_44169301) because I still can't get the thing to run. `.Open(myFile)` doesn't seem to be working, even if I use the string of the path & file instead of `myFile`. – MTJ May 25 '17 at 16:12