Saving Excel file as HTML using the command line

[IS300]

Does anybody know if there is a command to save an excel file as in html using the command prompt? We have excel files brough in automatically, and we want to put them on our intranet, but they need to be converted to html first. The file names do not change, so it is just one file we convert. The problem is that this file gets imported every hour, so it's hard for someone to remember to manually convert it every hour. Is there a command or script that will save our excel file that has been imported as html?

Thanks,

 

[hopelessliar]

I don't think you'll be able to do this from a command prompt as you will need to 'convert' the file via excel.

I can think of 2 possible solutions based on what you've supplied so far.

1) If the file always has the same name, or is always imported into the same location you could open it and save it as .html using VBA.

2) Get the file imported as .csv rather than .xls and then use a database driven webpage to display the contents.

Do either of these sound feasible?

 

[IS300]

The file will always have the same name, and it is always imported to the same location, over writing the previous file. I thought about using VBA, except I do not know it that well. Perhaps I'll browse around in the groups for something that might help.

Thanks

 

[hopelessliar]

If you have any familiarity at all with macros, this is quite easy to achieve.


(this runs the sub every 15 mins ("00:15:00"). You can change this as appropriate)

You need the following in the ThisWorkbook code :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "SaveAsHtm", , False
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:15:00"), "SaveAsHtm"
End Sub


And out this in a standard module:

Sub SaveAsHtm()

Workbooks.Open Filename:= _
"C:\Documents and Settings\BigNose\Desktop\pants.xls"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\BigNose\Desktop\pants.htm", FileFormat:= _
xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
End Sub




Obvisouly change the "C:\Documents... " etc to represent the paths that you want.

I haven't tested it, but I think that'll work.

 

[hopelessliar]

I mised a bit!

In the standard module, it should read:

Public dTime As Date
Sub SaveAsHtm()
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "SaveAsHtm"
Workbooks.Open Filename:= _
"C:\Documents and Settings\BigNose\Desktop\pants.xls"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\BigNose\Desktop\pants.htm", FileFormat:= _
xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False


End Sub