Excel VBA跨文件夹工作簿连接报错：无法设置子文件夹路径

Hey there! Let's figure out why your cross-folder VBA workbook connection is throwing errors—this is a super common pitfall when moving files out of the same directory. Below are the most likely issues and fixes to get your code working again:

1. Make Sure the Source Workbook is Properly Targeted #

When your files were in the same folder, VBA could find Formini1.xlsm just by its filename. Now that it's in a subfolder, you need to use its full file path to reference or open it—no more shortcuts.

Wrong (relying on filename alone): #

Dim srcWB As Workbook
Set srcWB = Workbooks("Formini1.xlsm") ' Fails if the file isn't in the active folder

Correct (use full path + check if it's already open): #

Dim srcPath As String
srcPath = "C:\project\database\formini1.xlsm" ' Note: Windows prefers backslashes (\) over slashes (/)
Dim srcWB As Workbook

' Check if the workbook is already open to avoid duplicate openings
On Error Resume Next
Set srcWB = Workbooks("Formini1.xlsm")
On Error GoTo 0

' If not open, open it with the full path
If srcWB Is Nothing Then
    Set srcWB = Workbooks.Open(srcPath)
End If

Or, if you don't want the source workbook to be visible, use GetObject instead:

Dim srcWB As Object
Set srcWB = GetObject("C:\project\database\formini1.xlsm")

2. Fix Your Range/Table References #

Your RangeTabel code was probably using implicit references (like relying on the active sheet/workbook) when files were in the same folder. Now you need to explicitly tie every range/table to its parent workbook and worksheet.

Wrong (relies on active objects): #

' Assumes the table is in the currently active workbook/sheet
Dim tblRange As Range
Set tblRange = RangeTabel("YourTableName")

Correct (explicitly reference source workbook/sheet): #

If RangeTabel is a custom function, update it to accept a workbook/sheet parameter, or call it with full context:

' First, get the source worksheet
Dim srcWS As Worksheet
Set srcWS = srcWB.Worksheets("Sheet1") ' Replace with your actual sheet name

' If using an Excel Table (ListObject):
Dim srcTable As ListObject
Set srcTable = srcWS.ListObjects("YourTableName")
Dim tblRange As Range
Set tblRange = srcTable.Range

' If your RangeTabel function returns a range, pass the source sheet to it:
' Set tblRange = RangeTabel("YourTableName", srcWS)

3. Verify Path & Permissions #

• Double-check your file path: Make sure there are no typos (e.g., missing database folder, wrong capitalization—Windows paths are case-insensitive, but typos still break things).
• Use Dir() to confirm the file exists before trying to open it:

  If Dir(srcPath) = "" Then
      MsgBox "Source file not found at: " & srcPath, vbCritical
      Exit Sub
  End If
  

• Ensure you have read access to the database folder—some protected folders (like Program Files) block VBA from accessing files.

4. Ditch Active/Select Statements #

If your original code used Activate or Select to switch between workbooks, that's a recipe for errors with cross-folder files. These commands rely on the active window, which can easily switch to the wrong workbook.

Bad practice (avoid this!): #

Workbooks("Formini1.xlsm").Activate
Sheets("Sheet1").Select
Range("A1:D100").Copy
Workbooks("Tampil1.xlsm").Activate
Sheets("Target").Select
Range("A1").Paste

Clean, reliable alternative: #

' Copy directly without activating any windows
srcWB.Worksheets("Sheet1").Range("A1:D100").Copy _
    Destination:=ThisWorkbook.Worksheets("Target").Range("A1")

If you're still getting a specific error (like "Subscript out of range" or "Object required"), share the exact error message and your RangeTabel code, and we can narrow it down further!

内容的提问来源于stack exchange，提问作者Jaka E Sembodo