Save Browser Content
I am trying to automate a procedure. I have a situation where I must retrieve a spreadsheet from a remote server to update our system. I log into the application select the type of report I need and enter a date range then submit. The results are generated by Cold Fusion (cfm page) in a spreadsheet format.
What I have done is create a script that will start an InternatExplorer.Application, use the IE navigate method to load a form that submits with an onLoad event handler, passing the appropriate UserName and Password to log me in to the app. Then I navigate to the correct URL (with date range parameters) that generates the report I need.
I have two needs:
1) I need a way to save the content from my browser to a specified location, whereby I can query the spreadsheet and update our system automatically. I have tried the ExecWb method, however I cannot select the location that I want to save the file to. By default the file is saved in "My Documents", MOST of the time (ie. not reliable).
My code for saving is as follows:
objIe.ExecWB 4, 2, 0, 0
2) Also when I try to query the spreadsheet, I get an error that says, "External table is not in the expected format". However, when I manually open the file and save it with Excel, I can query it. The connection string that I am using is as follows:
set cn = WScript.CreateObject("Adodb.Connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & objFilePath & objFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Any help with this would be greatly appreciated as I am tired of wracking my brains over this. I know you're all smart so someone should have a solution that they have used. Thanks in advance people. Thanks a ton.