consolidating multipel sheets those stored on different online PCs

[4ukh]

Dear Experts please help and support:
Scenario:
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...
Objectives:
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,

[AndreRet]

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?

[4ukh]

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...

Big thanks...:?::?::?:

[AndreRet]

Have a look at THIS link. It contains all you need to do to open the workbooks, copy data and paste to one workbook. Just replace the "C:/....." part with "192.168.0.201\MyServerName\FileName.xls" to get the data from the server using the ip address.

[4ukh]

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... :)

many thanks...

4ukh

[AndreRet]

Its a pleasure, I wish you luck!:)

[4ukh]

Dear AndreRet,

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...

thanks,
4ukh.

[AndreRet]

Post me the code where you are trying to connect to the other pc's.:)

[4ukh]

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...

thanks,
4ukh

[4ukh]

Dear AndreRet please for reference go through with this link

thanks,

[4ukh]

waiting for your kind support... :)

[4ukh]

waiting for kind supports any thing beside this... please advise...

[AndreRet]

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.

[4ukh]

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:pass@10.2.135.22/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

[AndreRet]

Try to change the following code

' Change this to the path\folder location of your files.
MyPath = "ftp://user:pass@10.2.135.22/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

To

' 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.:)

[AndreRet]

@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.