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