Opening a workbook

[ch46guy]

I'm trying to open an excel workbook and read the data from sheet1. when it gets to the "With Workbooks(xyz).sheets(Sheet1)" it gives me an error "Run-Time error '9': Subscript out of range.

Also when I try and close the workbook, it gives me an error. Any help would be appreciated.

Danka

Public Sub imporCord()
Dim RowNum As Integer
RowNum = 1
Dim easting(), northing(), elevation() As Double
Dim boring As String
Dim selection As String
Dim xyz As String


   ' selection = Application.GetOpenFilename("MIP Files (*.xls), *.xls")
  xyz = ("Z:\Advanced Site Characterization\Public Folder\Project Folder\2014\14.0089 - Johnson Company - Flowery Branch, GA\Data from JCO\MIP_location_XYZ.xlsx")
   Workbooks.Open (xyz)
    
  With Workbooks(xyz).Sheets(Sheet1)


     End With
    
 Workbooks.Close (xyz)


End Sub

 

[Norie]

You only need the workbook name when referring to a workbook with Workbooks.

 

[ch46guy]

So I tried using xyz as just the work book name but it gives me an error: Run-Time error '13' type mismatch.

Public Sub imporCord()
Dim RowNum As Integer
RowNum = 1
Dim easting(), northing(), elevation() As Double
Dim boring As String
Dim selection As String
Dim xyz As String


   ' selection = Application.GetOpenFilename("MIP Files (*.xls), *.xls")
  xyz = Dir$("Z:\Advanced Site Characterization\Public Folder\Project Folder\2014\14.0089 - Johnson Company - Flowery Branch, GA\Data from JCO\MIP_location_XYZ.xlsx")
   Workbooks.Open ("Z:\Advanced Site Characterization\Public Folder\Project Folder\2014\14.0089 - Johnson Company - Flowery Branch, GA\Data from JCO\MIP_location_XYZ.xlsx")
    
  With Workbooks(xyz).Worksheets(Sheet1)


     End With
    




End Sub

 

[Norie]

Sheet1 could be the code name of a worksheet in the workbook you have opened, when using Sheets to refer to a sheet you need the sheet name as a string.