CODEWITHSUNDEEP
excelvbaimportpath
Atena M.
Atena M.

Reputation: 11

Import Excel Workbook from variable path depending on cell value

I am completely new at this VBA language, and i am in need of help.

I have a few workbooks in which i need to import information from certain excel exports i make every few months. (these workbooks have only one sheet each)

These exports will be in a certain path on the server in folders that have the name equal to value of "B2" cell in my main workbooks.

The path of the files i need to import info from would be: \\Server-Name\MainFolder\SubFolder\SubSubFolder\

Where:

MainFolder has always the same name

SubFolder name = the date of the exports/workbooks that i need to import in my main workbooks

SubSubFolder name = cell B2 in each workbook i need to make the import

I can change the code/path regarding the first subfolder every time i make the exports according to the date of the exports, i do not necessary need that to be automated in the code because the date may vary (or we can find a solution to put in here the date of the system), but i need Excel to import the info from "Kosten.xlsx", "Belege.xlsx", and "Zeiten.xlsx" (these 3 are the exports i make every few months) from that certain path.

I managed to write the code (Macro) for importing the information, but the files i need info from need to be in the same folder as the workbook i work in.

Sub import_sheets

Dim Pfad, Datei As String
Pfad = ActiveWorkbook.Path & "\"
Datei = ActiveWorkbook.Name

Sheets("FW-ProjAuswertMatSEK").Select
Range("A1").Select
Workbooks.Open Filename:=Pfad & "Kosten.xlsx" 
Columns("A:L").Select
Selection.Copy
Windows(Datei).Activate
ActiveSheet.Paste
Windows("Kosten.xlsx").Close

Sheets("FW-PrjAuswertStunden").Select
Range("A1").Select
Workbooks.Open Filename:=Pfad & "Zeiten.xlsx"
Columns("A:H").Select
Selection.Copy
Windows(Datei).Activate
ActiveSheet.Paste
Windows("Zeiten.xlsx").Close

Sheets("FW-Bestell-Lief-Pos").Select
Range("A1").Select
Workbooks.Open Filename:=Pfad & "Belege.xlsx" 
Columns("A:O").Select
Selection.Copy
Windows(Datei).Activate
ActiveSheet.Paste
Windows("Belege.xlsx").Close

Sheets("Übersicht").Select

End Sub

Upvotes: 1

Views: 120

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Try something like this:

Sub import_sheets()
    Const MAIN_FLD As String = "\\Server-Name\MainFolder\"
    
    Dim wb As Workbook, Pfad As String, ssf As String, dt As Date
    
    Set wb = ThisWorkbook 'if the same workbook as where the code is run
    With wb.Worksheets("info")    'for example; sheet with date/folder info
        dt = .Range("B1").Value   'date
        ssf = .Range("B2").Value  'sub-subfolder
    End With
    
    'build the path
    Pfad = MAIN_FLD & Format(dt, "yyy-mm-dd") & "\" & ssf & "\"
    
    With Workbooks.Open(Filename:=Pfad & "Kosten.xlsx", ReadOnly:=True)
        .Worksheets(1).Columns("A:L").Copy _
                wb.Worksheets("FW - ProjAuswertMatSEK").Range("A1")
        .Close False 'no save
    End With
    
    With Workbooks.Open(Filename:=Pfad & "Zeiten.xlsx", ReadOnly:=True)
        .Worksheets(1).Columns("A:H").Copy _
                wb.Worksheets("FW-PrjAuswertStunden").Range("A1")
        .Close False
    End With
    
    With Workbooks.Open(Filename:=Pfad & "Belege.xlsx", ReadOnly:=True)
        .Worksheets(1).Columns("A:H").Copy _
                wb.Worksheets("FW-Bestell-Lief-Pos").Range("A1")
        .Close False
    End With
    
End Sub

Upvotes: 1

Related Questions