正确指定工作簿和工作表VBA

Question

我已经搜索过此问题，但所有示例都太复杂。 我只是不能使用“设置工作簿”和“设置工作表”对象。我将得到错误1004：对象定义的错误。

我想从目标文件片 “路径” 读取路径：C：\用户\霍尔格\文件\ VBA \ WB_data.xlsx

请人帮忙。

Sub Get_numbers() 

'I have got 2 sheets 
Dim WB_dest As Workbook 
Dim WB_data As Workbook 

'I need 3 worksheets 
Dim path_sheet As Worksheet 
Dim dest_sheet As Worksheet 
Dim data_sheet As Worksheet 

'Data worksheet's path 
Dim path As String 

'Counter 
Dim i As Byte 

'I run this sub from destination file sheet "PATH" which is already open 
Set WB_dest = ThisWorkbook 
Set path_sheet = WB_dest.Worksheets("Path") 
Set dest_sheet = WB_dest.Worksheets("TO") 

'I set worksheet from the Excel taht I do not need to open, just reading 
Set WB_data = Workbooks.Open(path) 
Set data_sheet = WB_data.Worksheets("FROM") 

'Data sheet contains three numbers in the first column 
'and destination sheet also contains only three numbers in the first coulm 
'I would like to add data_sheet numbers after dest_sheet numbers 
For i = 4 To 6 
    dest_sheet.Cells(i, 1) = data_sheet(i, 1) 
Next i 

Answer 1

尝试......

Sub Get_numbers() 

'I have got 2 sheets 
Dim WB_dest As Workbook 
Dim WB_data As Workbook 

'I need 3 worksheets 
Dim path_sheet As Worksheet 
Dim dest_sheet As Worksheet 
Dim data_sheet As Worksheet 

'Data worksheet's path 
Dim path As String 

'Counter 
Dim i As Byte 

path = "C:\Users\Holger\Documents\VBA\WB_data.xlsx" 

'I run this sub from destination file sheet "PATH" which is already open 
Set WB_dest = ThisWorkbook 
Set path_sheet = WB_dest.Worksheets("Path") 
Set dest_sheet = WB_dest.Worksheets("TO") 

'I set worksheet from the Excel taht I do not need to open, just reading 
Set WB_data = Workbooks.Open(path) 
Set data_sheet = WB_data.Worksheets("FROM") 

'Data sheet contains three numbers in the first column 
'and destination sheet also contains only three numbers in the first coulm 
'I would like to add data_sheet numbers after dest_sheet numbers 

data_sheet.Range("A4:A6").Copy 
dest_sheet.Range("A4").PasteSpecial xlPasteValues 
'For i = 4 To 6 
' dest_sheet.Cells(i, 1) = data_sheet(i, 1) 
'Next i 

End Sub