Saving excel file as field from form 1

[dporrelli]

OK,

I am importing data from a form into a spreadsheet, the info that I am importing works OK, but I am trying to SaveAs with the ref number from the form and some text, but I keep getting error msg "The remote server machine does not exist or is unavailable".
Here is the code I'm using:

Dim newref As String
Dim savefile As String
Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Application.Workbooks.Open "C:\database\quote template.xls"
xl.Visible = True
xl.Range("H5") = Form_Quotes.RefNo
newref = "svl" & Form_Quotes.RefNo
savefile = "C:\database\" & newref & ".xls"
ActiveWorkbook.SaveAs savefile, , , , , , , , , , ""

 

[RoyVidar]

You are using unqualified references to Excel objects (activeworkbook), which means Excel/Access may be confused as to what to save.

Try the following alterations:

[tt] Dim newref As String
Dim savefile As String
Dim xl As Excel.Application
dim wr as excel.workbook
'Set xl = New Excel.Application
' I often use this in stead of the New keyword
set xl=createobject("excel.application")
set wr=xl.Application.Workbooks.Open("C:\database\quote template.xls")
xl.Visible = True
xl.Range("H5") = Form_Quotes.RefNo
newref = "svl" & Form_Quotes.RefNo
savefile = "C:\database\" & newref & ".xls"
wr.SaveAs savefile[/tt]

More info can be found for instance here Excel automation fails second time code runs

Roy-Vidar