Allowing "Save As" box when exporting form Data to Excel

[grantwilliams]

Hi!

I'm putting together a client-side script which takes data from an HTML form, opens an Excel template, transfers the data and then saves the spreadsheet to a new location.

I have been able to use another form text field to enter the name the file is to be saved as, but cannot find a way of allowing the user to specify where the file should be saved to. I COULD do this with another form text field containing the directory location which could be changed to suit, but this would not be appropriate in this case. What is needed is a 'Save As' dialog box.

Can anyone help me with this?

Below is the code I have at the moment which saves and closes the file:

Dim x1saveName
x1saveName = document.myForm.fileName.value

x1Book.SaveAs "C:\saved\" + x1saveName + ".xls"
x1Book.Close
ExcelSheet.Quit

 

[ggriffit]

The Save As box is dependant on the local browser configuration - not settable from the server/client side, the best that we can do is to use an unknown MIME type, which will force the Save As box.

alternatively you could use the one of the approaches at

http://www.greggriffiths.org/webdev/both/excel/

like the ActiveX one.

 

[grantwilliams]

thanks ggriffit,

As I'd used ActiveX to open the spreadsheet, I took your advice and used it for saving it and it worked a treat. I can't believe the answer had been staring me in the face the whole time! Though, that's usually the way it goes...

I only have one more problem and that is that the 'Save As' dialog box opens "under" the page... i.e. the web page is on top and I have to use the Alt-Tab combo to select the 'Save As' box. Would this be because of the ActiveX warning box that pops up? Or would there be another gremlin in my script?

How could I solve this? I thought about minimising the web page prior to the 'Save As' box and restoring it after the dialog box was closed. Would this be plausible? Or is there another way?

 

[ggriffit]

not sure, if you post soime code I'll have a look.

 

[grantwilliams]

I had another look at your ActiveX code and found the solution... here is the code anyway. Maybe you can see something wrong (other than the fact that it's messy) I have put the line which fixed it in bold.

Thanks for you help!

Sub cmdExport_onClick

Dim ExcelSheet
Set ExcelSheet = CreateObject("Excel.Application&quot
Excelsheet.visible = true
Dim x1Book
Dim x1FileName
x1FileName = "a:\2002 Charges calculator.xls"
Set x1Book = ExcelSheet.Workbooks.Open(x1FileName)

ExcelSheet.ActiveSheet.Cells(4,2).Value = Document.myForm.numfiles.Value
ExcelSheet.ActiveSheet.Cells(5,2).Value = Document.myForm.numpages.Value
ExcelSheet.ActiveSheet.Cells(6,2).Value = Document.myForm.numdocs.Value
ExcelSheet.ActiveSheet.Cells(7,2).Value = Document.myForm.numexpages.Value
ExcelSheet.ActiveSheet.Cells(8,2).Value = Document.myForm.pagedel.Value
ExcelSheet.ActiveSheet.Cells(9,2).Value = Document.myForm.thirdparty.Value

Dim fname
fname = Excelsheet.GetSaveAsFilename("FOI Estimate.xls&quot
if fname = "False" then
fname="C:\FOI Estimate.xls"
end if

x1Book.SaveAs fname
x1Book.Close
ExcelSheet.Quit
Set ExcelSheet = Nothing

End Sub

 

[ggriffit]

That was the line I would have looked for, glad to be of assistance ;-)