WSH: Open and save unknown excel file.

[Gadall]

I have a situation with a client, they receive an excel file from a 3rd party as a mail attachement, this file is saved to a particular directory and then imported into SQL using SSIS. The problem occurs however that the 3rd party has an Excel version 4.0 formatting macro embedded in the file, so when the file is opened, it is formatted, and hence "changed". This change, then makes excel want to save the file when working with it in SSIS, and since this process is run as a scheduled task, the process hangs until someone manually opens and then saves the excel file in place.

The SSIS process works if the manual open/save procedure is carried out. My question is, is it possible to automate this in some way? Bearing in mind the file is coming from a 3rd party so the adding of macro's/removing the formatting macro etc. are not feasible solutions at this time.


The reason the file is "unknown" is the file name changes based on time period and data within, the SSIS package accomodates this by using a for loop to run through .xls files in the particular directory these files are stored in.

 

[SkipVought]

Hi,

What do you think could be done in MS Office to address this issue? Don't you think that is it an SQL Server side issue? This might be better addressed in Forum183.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Gadall]

Well I was kind of hoping there is a way in Windows Scripting Host to open and save the excel file, and I can add this script to a task schedule before running the SSIS task.

 

[SkipVought]

Again, Windows scripting is not MS Office.

You might try Forum329.



Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Gadall]

um its scripting office actions .. and here I thought this forum might be helpful ...