insert data in Excel 2003 cells from Access 2003 1

[DougP]

I have a template that I want to preserve. I want to open it and save is as another every time. But there does not seem to be a SaveAs option. Then I thought I would open it Save it as something else which does work then open the new document. This does not work, it opens the template again.
Here is what I have so far.

Dim oApp As Object
    Dim SheetName As String
    Set oApp = CreateObject("Excel.Application")
    
    'load document
    oApp.Workbooks.Open ("C:\AirMonitoring\Work Area Air Sampling Log.xls")
    
    ' save as new document
    SheetName = "C:\AirMonitoring\Spreadsheets\WorkAreaAirSamplingLog-" & Me.Project_Number & ".xls"
    oApp.Save (SheetName)
    
    'close template
    oApp.Workbooks.Close
    
    ' open new one back up
    oApp.Workbooks.Open (SheetName)
   
   ' make Excel visible
    oApp.Visible = True

DougP, MCP, A+

 

[PHV]

First, save your template as, say, a template (*.xlt)
Then use the Add method of the Workbooks collection (instead of Open):
Dim oApp As Object
Dim SheetName As String
Set oApp = CreateObject("Excel.Application")
'load NEW document
oApp.Workbooks.Add "C:\AirMonitoring\Work Area Air Sampling Log.xlt"
' save as new document
SheetName = "C:\AirMonitoring\Spreadsheets\WorkAreaAirSamplingLog-" & Me.Project_Number & ".xls"
oApp.ActiveWorkbook.SaveAs SheetName
' make Excel visible
oApp.Visible = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DougP]

Thank you once again


DougP, MCP, A+