Dear Experts please help and support:
1. 4 excel sheet (same structure) stored on 4 different computers attached to a network…
2. 1 master sheet (same structure) as other 4 sheets stored in my PC...
1. Want to consolidate into one master sheet that is stored in my computer or create new master sheet in my PC and merge in it.
Thanks in advance,
ahhhh, vb6, vba?
We can not read text language (sms). Please write out the full four and one sheets, we might be able to understand then what you mean.:)
So you want to take the data from the other four sheets and put it all into one sheet on you desktop?
Thanks AndreRet for your reply to my post and next time i try my best to explain in details: yes you under stand very well what i want...
i work in head office and we have four other branch offices in different cities beside HO and form these branch offices i've to collect some information on daily bases. let me explain more each office has a shared folder on the PC where these sheet are stored daily_inputs.xlsx and available for me and other users who might need this information and for reporting i've to merge these sheet into one master sheet and generate report for management... sometimes once in a day and some time more than once...
I really appreciate any support to solve the issue...
Yahoo... you are awesome... though i did't try but as i just go through it seems ok...
you give me a busy weekend... ill update you once try... wish me luck... :)
Its a pleasure, I wish you luck!:)
I try too much to achieve my goal but i find myself not so lucky actually the link that you've provided is quite ok for the local use (on same pc) and i didn't figure-out the way to use it for four different online PC. I think I'm missing something and please i really appreciate your help/support in this regard...
Post me the code where you are trying to connect to the other pc's.:)
well all examples merge only those sheets which are in the same folder no way to add different path for different folders/files i if i am not mistaken...
waiting for your kind support... :)
waiting for kind supports any thing beside this... please advise...
Sorry. I was away for a few days.:)
My question, what code are you using to interact with these workbooks?
I am sure the problem is under the entering of the ip address to get to a certain workbook. Post the code so I can have a look at what you have so far.
please see below...
Sub MergeAllWorkbooks() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, FNum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long ' Change this to the path\folder location of your files. MyPath = "ftp://user:firstname.lastname@example.org/dbtest" ' Add a slash at the end of the path if needed. If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" End If ' If there are no Excel files in the folder, exit. FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If ' Fill the myFiles array with the list of Excel files ' in the search folder. FNum = 0 Do While FilesInPath <> "" FNum = FNum + 1 ReDim Preserve MyFiles(1 To FNum) MyFiles(FNum) = FilesInPath FilesInPath = Dir() Loop ' Set various application properties. With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ' Add a new workbook with one sheet. Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 ' Loop through all files in the myFiles array. If FNum > 0 Then For FNum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 If Not mybook Is Nothing Then On Error Resume Next ' Change this range to fit your own needs. With mybook.Worksheets(1) Set sourceRange = .Range("A1:I1") End With If Err.Number > 0 Then Err.Clear Set sourceRange = Nothing Else ' If source range uses all columns then ' skip this file. If sourceRange.Columns.Count >= BaseWks.Columns.Count Then Set sourceRange = Nothing End If End If On Error GoTo 0 If Not sourceRange Is Nothing Then SourceRcount = sourceRange.Rows.Count If rnum + SourceRcount >= BaseWks.Rows.Count Then MsgBox "There are not enough rows in the target worksheet." BaseWks.Columns.AutoFit mybook.Close savechanges:=False GoTo ExitTheSub Else ' Copy the file name in column A. With sourceRange BaseWks.Cells(rnum, "A"). _ Resize(.Rows.Count).Value = MyFiles(FNum) End With ' Set the destination range. Set destrange = BaseWks.Range("B" & rnum) ' Copy the values from the source range ' to the destination range. With sourceRange Set destrange = destrange. _ Resize(.Rows.Count, .Columns.Count) End With destrange.Value = sourceRange.Value rnum = rnum + SourceRcount End If End If mybook.Close savechanges:=False End If Next FNum BaseWks.Columns.AutoFit End If ExitTheSub: ' Restore the application properties. With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub
Try to change the following code
' Add a slash at the end of the path if needed.
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
' If there are no Excel files in the folder, exit.
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
' Change this to the path\folder location of your files. MyPath = "//10.2.135.22/dbtest" 'the server ip address and pc name ' Add a slash at the end of the path if needed. If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" End If ' If there are no Excel files in the folder, exit. FilesInPath = Dir(MyPath & "*.xls*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If
This should do the trick.:)
@4ukh, please mark this thread as solved if you have managed to get a solution, found at the bottom of this page, thanks.:)
It has been open for some time now.