Read another workbook and writing

Question

when open the excel and to do below VBA, And the VBA show there has some mistake, Please help check.

Sub Tianchong()

Dim i As String
Dim w As Worksheet, wb As Workbook, t As Worksheet


Set wb = Workbooks.Open("C:\Users\ZHEN YUAN\Desktop\vba\att.xlsx")

Set w = wb.Worksheets("Sheet1")
set t = worksheets("申请单")

t.Cells(7, 4) = wb.Cells(2, 5)
End Sub

I want sheet.cells = wb.sheet.cells(4,7). But the VBA show Did not find the sheet"申请单". Both excel as xlsx and only one sheet in workbook.

Thank You!!

Rafał B. Rafał B. · Accepted Answer · 2020-08-10T07:45:26

I don't understand your question, but I guess you want copy value to your sheet from att.xlsx file. In the case:

Sub Tianchong()

Dim wb As Workbook
Set wb = Workbooks.Open("C:\Users\ZHEN YUAN\Desktop\vba\att.xlsx")

Dim w As Sheet
Set w = wb.Sheets("Sheet1")

Dim t As Sheet
Set t = ThisWorkbook.Sheets("申请单")
      ' ThisWorkbook means your file with macro

t.Cells(7, 4).Value = w.Cells(2, 5).Value

End Sub

In your code was probably a problem with:

 wb.Cells(2, 5)        ' wb is Workbbok object

Using simple language: wb is Workbook object. It contains sheet(s). And every sheet contains cells. You've tried acces cells from Workbook directly, I changed

 w.Cells(2, 5).Value   ' w is Sheet object

If the code is not working change name of a sheet 申请单 to TEST and the line:

 Set t = ThisWorkbook.Sheets("申请单")

to

Set t = ThisWorkbook.Sheets("TEST")

Maybe there is a problem with chinese name

Read another workbook and writing

1 Answers