Trouble opening worksheet

[Guest]

A simple code opening 1st worksheet from user-identified spreadsheet - lines enclosed in <> can be retained or omitted

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
If fd.Show = -1 Then
FAOfficeFile = fd.SelectedItems(1)
<Workbooks.Open Filename:=FAOfficeFile, ReadOnly:=True>
Dim FAOfficeSheet As Worksheet
<Debug.Print Workbooks(FAOfficeFile).Worksheets.Count>
Set FAOfficeSheet = Workbooks(FAOfficeFile).Worksheets(1)
End If

gets 'Subscript out of range' error message at Set FAOfficeSheet = line <or Debug.Print line>. Could someone please suggest me the solution?

Thank you very much.

 

[Jim Rech]

Workbooks(FAOfficeFile)

The Workbooks method expects the name of the file only, not the full path
and name. I might suggest using just:

Set FAOfficeSheet = Worksheets(1)

since the opened workbook would be active (unless the workbook was hidden.

Another alternative:

Sub a()
Dim fd As FileDialog
Dim FAOfficeFile As String
Dim FAOfficeSheet As Worksheet
Dim FAOfficeWB As Workbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
If fd.Show = -1 Then
FAOfficeFile = fd.SelectedItems(1)
Set FAOfficeWB = Workbooks.Open(Filename:=FAOfficeFile,
ReadOnly:=True)
Set FAOfficeSheet = FAOfficeWB.Worksheets(1)
End If
End Sub


--
Jim Rech
Excel MVP
|A simple code opening 1st worksheet from user-identified spreadsheet -
lines enclosed in <> can be retained or omitted
|
| Dim fd As FileDialog
| Set fd = Application.FileDialog(msoFileDialogFilePicker)
| If fd.Show = -1 Then
| FAOfficeFile = fd.SelectedItems(1)
| <Workbooks.Open Filename:=FAOfficeFile, ReadOnly:=True>
| Dim FAOfficeSheet As Worksheet
| <Debug.Print Workbooks(FAOfficeFile).Worksheets.Count>
| Set FAOfficeSheet = Workbooks(FAOfficeFile).Worksheets(1)
| End If
|
| gets 'Subscript out of range' error message at Set FAOfficeSheet = line
<or Debug.Print line>. Could someone please suggest me the solution?
|
| Thank you very much.
|