No answer yet... Data Import from Many Worksheets to on file...

[Guest]

I am having 12 files in different network locations. Each havinf one
worksheet inside. I just want to know whether I can create one file in my
hard drive and create 12 worksheets and Import External Data to each
worksheet from the different files so that I can see all of those worksheets
in indifferent network locations into one file. I tried the External Data
Import & Refresh but the command refreshes only the cells that contained data
at the time of setting up. New cells edited doesn't appear in my amalgamated
worskbook (also cells that contain formulas in the source files, when
imported are reflected as blank cells)
Anybody can help me on this? I just want to create a copy of all those
worsheets in my combined file when i open the combined workbook...

 

[aresen]

I would get the sheets via macro control. First, I would create a
worksheet (I called one "AccessList") where I have a heading row.
Column A would be the network path, column B would be the workbook
name, column C the worksheet name in the remote workbook and column D
the local worksheet name. This way, I have complete flexability in
naming.
You would want the macro to first attempt to open the remote workbook.
If inaccessible, you want to report it but keep going. Only if it could
be opened, the existing local worksheet could be deleted. You could
then move the worksheet into your master workbook and rename it as
appropriate. When doing so, you don't save the remote workbook, leaving
it unchanged.
For example:

Sub RefreshSheets()
Dim rw As Integer, n As Integer, sht As String, trgt As String, cnt As
Integer

Sheets("AccessList").Activate
rw = Range("A1").CurrentRegion.Rows.Count
If rw = 1 Then Exit Sub 'no data other than headings

For n = 2 To rw
sht = Cells(rw, 3).Value ' sheet in remote workbook
trgt = Cells(rw, 4).Value ' local sheet name
If OpenBook(n) Then
cnt = ActiveWorkbook.Sheets.Count
Sheets(sht).Move after:=ThisWorkbook.Sheets("AccessList")
sht = ActiveSheet.Name ' name may have changed if duplicate
If cnt > 1 Then
' workbook would be closed if it only had the one sheet
Workbooks(WB).Saved = True
Workbooks(WB).Close
End If
DeleteSheet trgt
Sheets(sht).Name = trgt
Else
MsgBox Cells(n, 1) & Cells(n, 2) & " could not be accessed"
End If
Next
End Sub

Function OpenBook(n As Integer) As Boolean
Dim pth As String, WB As String, sht As String

OpenBook = False
pth = Cells(n, 1).Value
If Right(pth, 1) <> "\" Then
pth = pth & "\"
Cells(n, 1) = pth ' update path to include delimiter
End If

WB = Cells(n, 2)
On Error Resume Next
Workbooks.Open pth & WB
If Err <> 0 Then Exit Function
OpenBook = True
End Function

Sub DeleteSheet(sht As String)
Application.DisplayAlerts = False
On Error Resume Next
Sheets(sht).Delete
End Sub

 

[Guest]

Hi,
I am very sorry to bother you. I am not familiar with this VB commands. I
tried to copy your command to a new module and run but I get a
"Compilr Error:
Syntax Error"
message,
Can you advice me in this. I have done the Access List worksheet as you
siad...
Regards,