you can open workbooks and read protected data
you can read hidden sheets
you can append table data provided table structure is the same
1: you need to manage file name, location and load status
2: you need to open new source data wbs and copy out the data
3: you need to append data to the master table
4: you need to manage any probable errors
5: you might need to manage delete and reload of data
this will let you manage file name and location
Sub GetFilePath(myRow As Long)'Return file name and path to worksheet cells
Dim myObject As Object
Dim fileSelected As String
Dim myPath As String
Dim myFile As String
Dim strLen As Integer
Set myObject = Application.FileDialog(msoFileDialogOpen)
With myObject
.Title = "Choose File"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
fileSelected = .SelectedItems(1)
End With
strLen = Len(fileSelected) - InStrRev(fileSelected, "\")
myFile = Right(fileSelected, strLen)
strLen = Len(fileSelected) - strLen - 1
myPath = Left(fileSelected, strLen)
With Worksheets("Admin")
.Range("G" & myRow) = myPath 'The file path
.Range("F" & myRow) = myFile 'The file name
.Range("C" & myRow, "D" & myRow).Font.Color = vbBlack
If Len(myFile) > 0 Then
.Range("D" & myRow).Value = "File Located"
Else
.Range("D" & myRow).Value = "No File"
End If
End With
End Sub
and
If fileArray(myMonth, 2) <> "Loaded" And fileArray(myMonth, 1) = "File Located" Then 'load data
fileName = fileArray(myMonth, 3)
myPath = fileArray(myMonth, 4)
myString = myPath & "/" & fileName
If BookOpen(fileName) = True Then Workbooks(fileName).Close SaveChanges:=True
If Dir(myString) <> "" Then 'workbook name exists at location
Set dataWB = Workbooks.Open(fileName:=myString)
Else
myWB.Worksheets("Admin").Cells(myMonth + 6, 4).Value = "File Missing"
End If
DoEvents 'ensure file opens fully before continuing
do some stuff
Workbooks(fileName).Close SaveChanges:=True 'error correction can edit source data
DoEvents
do some more stuff
you can use an array or a range to pass the data to the source workbook. be careful with writing large tables - loops that call the sheet/range object for each cell are slow, and you can transfer a range quickly with range.copy destination:=myDestRange type logic