Opening and Saving new Excel Workbooks from Access VBA

[clifftech]

I am stumped and need help.

From an Access command button (using VBA) I am opening 2 different existing workbooks. The first workbook1 that opens I add information to some of the cells then save the spreadsheet as a different name (new_workbook1). The second workbook2 is opened and I simply need to rename (and save) as new_workbook2. I need both the renamed new_workbooks to remain open and the original 2 workbooks to close. My code works except that somehow the original workbooks remain open in the background and the new renamed and saved workbooks close. Here is my code:

Private Sub Select_PI_file_Click()


Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("c:\workbook1")

With objXLApp

Workbooks.Open ("c:\workbook1")
Sheets.Select
Sheets(1).Activate
Range("C35").Select
Selection.ClearContents
Range("C3").Select
ActiveCell.FormulaR1C1 = "new text added"
End With

ActiveWorkbook.SaveAs ("c:\new_workbook1")

Set objXLBook = objXLApp.Workbooks.Open("c:\workbook2")

ActiveWorkbook.SaveAs ("c:\new_workbook2")

End Sub

 

[PHV]

...
    Set objXLApp = CreateObject("Excel.Application")      
    Set objXLBook = objXLApp.Workbooks.Open("c:\workbook1")
    With objXLBook.Sheets(1)
        .Range("C3:D5").ClearContents
        .Range("C3").FormulaR1C1 = "new text added"
    End With
    objXLBook.SaveAs "c:\new_workbook1"
    Set objXLBook = objXLApp.Workbooks.Open("c:\workbook2")
    objXLBook.SaveAs "c:\new_workbook2"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[clifftech]

PH,

Thanks for helping -again!

Your changes don't work completely. The new workbooks are made and saved but are not visible. When I try to delete the new workbooks in the explorer window I get a message saying I must close the file before proceeding (but they are not visible!). Once I manually open then close the new workbooks they can be deleted. Somehow the new workbooks are open just not visible.

 

[PHV]

objXLApp.Visible = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[clifftech]

PH,

That worked, thanks.

One other feature that I need to incorporate is the ability to add the "new text data" to all of the worksheets in the workbook. There can be up to 11 worksheets. Do I need to do a count of worksheets then loop through each of the worksheets with your code below or is there a property that will select all the worksheets?

With objXLBook.Sheets(1)
.Range("C35").ClearContents
.Range("C3").FormulaR1C1 = "new text added"
End With

 

[Spooku777]

When you perform a SaveAs in Word or Excel it still "remembers" the original file - I could be wrong but I've had issues with this in the past. Maybe something to do with the temporary file in the background.

Since your using Excel you may be better off performing a SaveCopyAs then opening the new file(s) separately.
(ActiveDocument in Word VBA does not have this function by the way)

...
objXLBook.SaveCopyAs "c:\new_workbook1"
objXLBook.close
Set objXLBook = objXLApp.Workbooks.Open("c:\new_workbook1")
...

That way the original file is cleanly closed and completely separate from the new one.





Spook :-j