Open File Routine

The following routine is useful for automatically opening a file and then extracting some data before automatically closing. The routine computes a relative file path so that it can find a file in the same directory, irrespective of the absolute path. This was developed and tested with the file stored on a sharepoint server.

' Variable decs
Dim fullPathLength As Integer, fileNameLength As Integer
Dim fullPath As String, fileName As String, path As String, targetPath As String
Dim wBook As Workbook
Dim data As String

' Set various application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

' Initial capture of the envirionment
fullPath = ThisWorkbook.FullName
fileName = ThisWorkbook.Name

fullPathLength = Len(fullPath)
fileNameLength = Len(fileName)

path = Left(fullPath, fullPathLength - fileNameLength)

' define the target path
targetPath = path + "test data.xls"

' output for verification at the moment
Cells(1, 2) = path
Cells(2, 2) = targetPath

' extract data and write to the summary page
Set wBook = Workbooks.Open(targetPath)
data = wBook.Worksheets(1).Cells(1, 2)
Cells(3, 2) = data
wBook.Close

' Restore the application properties.
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With