从根据单元格值的不同路径导入Excel工作簿。

huangapple
go评论90阅读模式
英文:

Import Excel Workbook from variable path depending on cell value

问题

我是完全新手，对这种VBA语言一无所知，我需要帮助。

我有一些工作簿，需要从我每隔几个月制作的某些Excel导出中导入信息（这些工作簿每个只有一个工作表）。

这些导出文件将位于服务器上的某个路径中，路径的文件夹名称与我的主要工作簿中“B2”单元格的值相同。

我需要从这些文件中导入信息的路径将是：\\Server-Name\MainFolder\SubFolder\SubSubFolder\

其中：

MainFolder始终具有相同的名称

SubFolder的名称等于我需要导入到主要工作簿中的导出/工作簿的日期

SubSubFolder名称=每个需要进行导入的工作簿中的B2单元格

我可以根据导出日期每次更改代码/路径的第一个子文件夹，不必在代码中进行自动化，因为日期可能会有所变化（或者我们可以找到将系统日期放在这里的解决方案），但我需要Excel从那个特定路径中导入“Kosten.xlsx”、“Belege.xlsx”和“Zeiten.xlsx”中的信息（这3个是我每隔几个月制作的导出文件）。

我已经成功编写了用于导入信息的代码（宏），但我需要提取信息的文件必须与我正在工作的工作簿位于同一个文件夹中。

  1. Sub import_sheets
  3. Dim Pfad, Datei As String
  4. Pfad = ActiveWorkbook.Path & "\"
  5. Datei = ActiveWorkbook.Name
  7. Sheets("FW-ProjAuswertMatSEK").Select
  8. Range("A1").Select
  9. Workbooks.Open Filename:=Pfad & "Kosten.xlsx" 
  10. Columns("A:L").Select
  11. Selection.Copy
  12. Windows(Datei).Activate
  13. ActiveSheet.Paste
  14. Windows("Kosten.xlsx").Close
  16. Sheets("FW-PrjAuswertStunden").Select
  17. Range("A1").Select
  18. Workbooks.Open Filename:=Pfad & "Zeiten.xlsx"
  19. Columns("A:H").Select
  20. Selection.Copy
  21. Windows(Datei).Activate
  22. ActiveSheet.Paste
  23. Windows("Zeiten.xlsx").Close
  25. Sheets("FW-Bestell-Lief-Pos").Select
  26. Range("A1").Select
  27. Workbooks.Open Filename:=Pfad & "Belege.xlsx" 
  28. Columns("A:O").Select
  29. Selection.Copy
  30. Windows(Datei).Activate
  31. ActiveSheet.Paste
  32. Windows("Belege.xlsx").Close
  34. Sheets("Übersicht").Select
  36. End Sub

这是你提供的VBA代码的一部分，用于导入工作簿中的信息。这部分代码打开特定的Excel文件，复制其内容，然后将其粘贴到当前工作簿中。希望这有所帮助。

英文:

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.

  1. Sub import_sheets
  3. Dim Pfad, Datei As String
  4. Pfad = ActiveWorkbook.Path & "\"
  5. Datei = ActiveWorkbook.Name
  7. Sheets("FW-ProjAuswertMatSEK").Select
  8. Range("A1").Select
  9. Workbooks.Open Filename:=Pfad & "Kosten.xlsx" 
  10. Columns("A:L").Select
  11. Selection.Copy
  12. Windows(Datei).Activate
  13. ActiveSheet.Paste
  14. Windows("Kosten.xlsx").Close
  16. Sheets("FW-PrjAuswertStunden").Select
  17. Range("A1").Select
  18. Workbooks.Open Filename:=Pfad & "Zeiten.xlsx"
  19. Columns("A:H").Select
  20. Selection.Copy
  21. Windows(Datei).Activate
  22. ActiveSheet.Paste
  23. Windows("Zeiten.xlsx").Close
  25. Sheets("FW-Bestell-Lief-Pos").Select
  26. Range("A1").Select
  27. Workbooks.Open Filename:=Pfad & "Belege.xlsx" 
  28. Columns("A:O").Select
  29. Selection.Copy
  30. Windows(Datei).Activate
  31. ActiveSheet.Paste
  32. Windows("Belege.xlsx").Close
  34. Sheets("Übersicht").Select
  36. End Sub

答案1

得分: 1

请尝试以下方式：

  1. Sub import_sheets()
  2.     Const MAIN_FLD As String = "\\服务器名称\主文件夹\"
  3.     
  4.     Dim wb As Workbook, Pfad As String, ssf As String, dt As Date
  5.     
  6.     Set wb = ThisWorkbook '如果与运行代码的工作簿相同
  7.     With wb.Worksheets("info")    '例如，包含日期/文件夹信息的工作表
  8.         dt = .Range("B1").Value   '日期
  9.         ssf = .Range("B2").Value  '子子文件夹
  10.     End With
  11.     
  12.     '构建路径
  13.     Pfad = MAIN_FLD & Format(dt, "yyy-mm-dd") & "\" & ssf & "\"
  14.     
  15.     With Workbooks.Open(Filename:=Pfad & "Kosten.xlsx", ReadOnly:=True)
  16.         .Worksheets(1).Columns("A:L").Copy _
  17.                 wb.Worksheets("FW - ProjAuswertMatSEK").Range("A1")
  18.         .Close False '不保存
  19.     End With
  20.     
  21.     With Workbooks.Open(Filename:=Pfad & "Zeiten.xlsx", ReadOnly:=True)
  22.         .Worksheets(1).Columns("A:H").Copy _
  23.                 wb.Worksheets("FW-PrjAuswertStunden").Range("A1")
  24.         .Close False
  25.     End With
  26.     
  27.     With Workbooks.Open(Filename:=Pfad & "Belege.xlsx", ReadOnly:=True)
  28.         .Worksheets(1).Columns("A:H").Copy _
  29.                 wb.Worksheets("FW-Bestell-Lief-Pos").Range("A1")
  30.         .Close False
  31.     End With
  32.     
  33. End Sub
英文:

Try something like this:

  1. Sub import_sheets()
  2.     Const MAIN_FLD As String = "\\Server-Name\MainFolder\"
  3.     
  4.     Dim wb As Workbook, Pfad As String, ssf As String, dt As Date
  5.     
  6.     Set wb = ThisWorkbook 'if the same workbook as where the code is run
  7.     With wb.Worksheets("info")    'for example; sheet with date/folder info
  8.         dt = .Range("B1").Value   'date
  9.         ssf = .Range("B2").Value  'sub-subfolder
  10.     End With
  11.     
  12.     'build the path
  13.     Pfad = MAIN_FLD & Format(dt, "yyy-mm-dd") & "\" & ssf & "\"
  14.     
  15.     With Workbooks.Open(Filename:=Pfad & "Kosten.xlsx", ReadOnly:=True)
  16.         .Worksheets(1).Columns("A:L").Copy _
  17.                 wb.Worksheets("FW - ProjAuswertMatSEK").Range("A1")
  18.         .Close False 'no save
  19.     End With
  20.     
  21.     With Workbooks.Open(Filename:=Pfad & "Zeiten.xlsx", ReadOnly:=True)
  22.         .Worksheets(1).Columns("A:H").Copy _
  23.                 wb.Worksheets("FW-PrjAuswertStunden").Range("A1")
  24.         .Close False
  25.     End With
  26.     
  27.     With Workbooks.Open(Filename:=Pfad & "Belege.xlsx", ReadOnly:=True)
  28.         .Worksheets(1).Columns("A:H").Copy _
  29.                 wb.Worksheets("FW-Bestell-Lief-Pos").Range("A1")
  30.         .Close False
  31.     End With
  32.     
  33. End Sub

huangapple
  • 本文由 发表于 2023年4月4日 17:45:04
  • 转载请务必保留本文链接：https://go.coder-hub.com/75927882.html
  • excel
  • import
  • path
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定