CODEWITHSUNDEEP
vbaexcel
adp
adp

Reputation: 311

VB macro Excel script should copy entire folder data

Background : approx. 300 Excel surveys (with multiple sheets) should be centralized in 1 single excel.The macro is ready. Goal : although the Macro is ready and able to copy the desired data from the Survey excel,I do not have the possibility to copy all 300 surveys at once ( I have to go 1 by 1 through all the surveys)

Question : is it possible to ask the macro to target copy from a specific network path and hereby to copy all the 300 excel workbooks?

Macro script : 

Function bIsBookOpen(ByRef szBookName As String) As Boolean
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Sub Start()
currwb = ActiveWorkbook.Name

If bIsBookOpen("PERSONAL.XLSB") Then
    Windows("PERSONAL.XLSB").Visible = True
    ActiveWindow.Close
End If

If Workbooks.Count > 1 Then
MsgBox " To many files open...  " & Workbooks(1).Name
Else
Application.Dialogs(xlDialogOpen).Show
Response = MsgBox("Weiter mit 'IT-Personal'?", vbYesNo)
If Response = vbYes Then
Windows(currwb).Activate
Call CopyForm
End If

End If

End Sub

Upvotes: 0

Views: 503

Answers (1)

sam092
sam092

Reputation: 1335

To loop through files in a folder,

Sub LoopThroughFiles()
    Dim path As String
    Dim filename As String
    Dim wb As Workbook
    path = ""  'your folder path here
    filename = Dir(path & "*.xls")

    While (filename <> "")
        Set wb = Workbooks.Open(path & filename)
        'Your code goes here
        wb.Close
        filename = Dir
    Wend
End Sub

Upvotes: 1

Related Questions