Trouble opening worksheet
 

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.

Trouble opening worksheet
 

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
"Demyan" wrote in message
...
|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.
|

Thank you, Jim!