Help with VBA ....Please
Results 1 to 4 of 4

Thread: Help with VBA ....Please

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with VBA ....Please

    Hi guys,

    I was hoping you can help. I have the below VBA macro running in a master file (named ZMasterFile).

    The idea is I have this file in a folder where up to 100 excel workbooks are stored and I use it to extract some data from each of the workbooks and collate on the ZMasterFile.

    It is working great on my laptop however when I put it into the Network Shared drive and try to put in the path to the folder it doesn't grab any of the data from the files.
    I also wanted to be able to run it multiple times in a month and only maintain the most recent version so with the help of jmcleary on here he provided the solution highlighted in red, that works great!

    My problem is getting it all to work on the shared drive and one solution jmcleary suggested was instead of directing it to a specific path to use the 2nd batch of code I've pasted below to direct it to the active folder. This however is returning a runtime error 1004 and it looks like it is trying to find the folder as an excel file.

    When running debug it references the line highlighted in orange

    Any help on this would be great as I am stuck

    First Version of code

    Sub LoopThroughDirectory()
    Dim Filepath As String
    Dim erow
    Filepath = "C:\Users\paulb\Desktop\EmployeeProject"
    MyFile = Dir(Filepath)
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).RowSheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents
    Do While Len(MyFile) > 0
    If MyFile = "ZMasterFile.xlsm" Then
    Exit Sub
    End If
    Application.DisplayAlerts = False
    Workbooks.Open (Filepath & MyFile)
    Range("A2:M900").Copy
    ActiveWorkbook.Close
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))
    MyFile = Dir
    Application.DisplayAlerts = True

    Loop

    End Sub

    2nd Version of Code
    Sub LoopThroughDirectory()
    Dim Filepath As String
    Dim erow
    Filepath = Application.ActiveWorkbook.Path
    MyFile = Dir(Filepath + "\*.*")
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents


    Do While Len(MyFile) > 0
    If MyFile = "ZMasterFile.xlsm" Then
    Exit Sub
    End If


    Application.DisplayAlerts = False


    Workbooks.Open (Filepath & MyFile)
    Range("A2:M900").Copy


    ActiveWorkbook.Close


    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))


    MyFile = Dir
    Application.DisplayAlerts = True




    Loop


    End Sub





    Many thanks

    Paul

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with VBA ....Please

    Quote Originally Posted by braidp View Post
    Hi guys,

    I was hoping you can help. I have the below VBA macro running in a master file (named ZMasterFile).

    The idea is I have this file in a folder where up to 100 excel workbooks are stored and I use it to extract some data from each of the workbooks and collate on the ZMasterFile.

    It is working great on my laptop however when I put it into the Network Shared drive and try to put in the path to the folder it doesn't grab any of the data from the files.
    I also wanted to be able to run it multiple times in a month and only maintain the most recent version so with the help of jmcleary on here he provided the solution highlighted in red, that works great!

    My problem is getting it all to work on the shared drive and one solution jmcleary suggested was instead of directing it to a specific path to use the 2nd batch of code I've pasted below to direct it to the active folder. This however is returning a runtime error 1004 and it looks like it is trying to find the folder as an excel file.

    When running debug it references the line highlighted in orange

    Any help on this would be great as I am stuck

    First Version of code

    Sub LoopThroughDirectory()
    Dim Filepath As String
    Dim erow
    Filepath = "C:\Users\paulb\Desktop\EmployeeProject"
    MyFile = Dir(Filepath)
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).RowSheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents
    Do While Len(MyFile) > 0
    If MyFile = "ZMasterFile.xlsm" Then
    Exit Sub
    End If
    Application.DisplayAlerts = False
    Workbooks.Open (Filepath & MyFile)
    Range("A2:M900").Copy
    ActiveWorkbook.Close
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))
    MyFile = Dir
    Application.DisplayAlerts = True

    Loop

    End Sub

    2nd Version of Code
    Sub LoopThroughDirectory()
    Dim Filepath As String
    Dim erow
    Filepath = Application.ActiveWorkbook.Path
    MyFile = Dir(Filepath + "\*.*")
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents


    Do While Len(MyFile) > 0
    If MyFile = "ZMasterFile.xlsm" Then
    Exit Sub
    End If


    Application.DisplayAlerts = False


    Workbooks.Open (Filepath & MyFile)
    Range("A2:M900").Copy


    ActiveWorkbook.Close


    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))


    MyFile = Dir
    Application.DisplayAlerts = True




    Loop


    End Sub





    Many thanks

    Paul
    Hi
    In my opinion you have improperly written the line highlited in orange - I guess there's \ missing between path and file name. File path returns ex. C:\Test and MyFile name for ex. File.xlsx soto having these contatenated you get C:\TestFike.xlsx which is wrong.
    Try to change Workbooks.Open (Filepath & MyFile) to Workbooks.Open (Filepath & "" & MyFile)

    Regards,
    Sebastian

  3. #3
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with VBA ....Please

    Hi,
    I accidently posted the code outside the brackets and the slash has been removed.
    Instead of using line:
    [Code]
    Workbooks.Open (Filepath & MyFile)
    [\code]
    use this line:
    [CODE]
    Workbooks.Open (Filepath & "\" & MyFile)
    [\code]

  4. #4
    New Member
    Join Date
    Dec 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA ....Please

    Mentor82 that has worked like a dream!!! many, many thanks!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •