Copying to another workbook error

[vthokienj]

I am at a complete loss as to why this code does not work. It gives the 'run time error 9 error'. The other workbook exists and is open after the first block of code. Any help greatly appreciated. Thanks.

    ' create a new workbook
    Dim newWorkbook As Workbook
    Set newWorkbook = Workbooks.Add
    With newWorkbook
        .Title = "testoutput"
        .Subject = "output"
        .SaveAs filename:="C:\temp\aTestOutput.xlsx"
    End With

   ' write value to new workbook
   Workbooks("C:\temp\aTestOutput.xlsx").Sheets("Sheet1").Cells(1, 1) = "output"
    'Workbooks("C:\temp\aTestOutput.xlsx").Worksheets(1).Cells(1, 1) = "output"

 

[xenou]

When you refer to an open workbook by name do not use the full path, just the name:

Workbooks("C:\temp\aTestOutput.xlsx").Sheets("Sheet1").Cells(1, 1) = "output"

Amend to:

Workbooks("aTestOutput.xlsx").Sheets("Sheet1").Cells(1, 1) = "output"

 

[GTO]

Greetings vthokienj,

In addition to xenou's comments, I would add that as you have set a good reference to the created workbook, the reference is easily usable, whether your second snippet is in the same procedure or another.

(I am using Excel 2000 at the moment, and not sure about how you are accessing builtinproperties.)

Sub example_1()
Dim wbNew As Workbook
    
    Set wbNew = Workbooks.Add(xlWBATWorksheet)
    With wbNew
        .BuiltinDocumentProperties(1) = "test output"
        .BuiltinDocumentProperties(2) = "output"
        .SaveAs Filename:=ThisWorkbook.Path & "\TestOutput.xls", FileFormat:=xlWorkbookNormal
        .Worksheets(1).Cells(1).Value = "Test Value"
    End With
End Sub
Sub example_2()
Dim wbNew As Workbook
    
    Set wbNew = Workbooks.Add(xlWBATWorksheet)
    With wbNew
        .BuiltinDocumentProperties(1) = "test output"
        .BuiltinDocumentProperties(2) = "output"
        .SaveAs Filename:=ThisWorkbook.Path & "\TestOutput.xls", FileFormat:=xlWorkbookNormal
    End With
    
    Call TryMe(wbNew)
    
    If Not wbNew.Saved Then wbNew.Save
    wbNew.Close False
End Sub
Sub TryMe(wb As Workbook)
    wb.Worksheets(1).Cells(1).Value = "Test Value"
End Sub

Hope that helps,

Mark

 

[vthokienj]

Thanks for the replies, this helps greatly.