Open and save excel file via script 1

[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.

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gadall]

The part I don't know how to do is get the filename. Ive got a few examples I've found that show how to create the excel object but I can't seem to find any help on how to get the filename (changes so I can't hard code it).


' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False

myExcelWorker.DefaultFilePath = "D:\DataFiles\NewFiles"

'Need some way to get the filename from the directory
'I'm thinking along the lines of FSO, but not sure on how

myExcelWorker.Workbooks.Open(NameFromFSO)
'Pause so the format macro can run
myExcelWorker.ActiveWorkBook.Close True, NameFromFSO

myExcelWorker.Quit
set myExcelWorker = Nothing

 

[mrmovie]

'open you excel instance
'you might want to consider the use of .\ or something like that when creating the instance of excel
If FSO.FolderExists(...) Then
Set objFolder = FSO.GetFolder(...)
For Each aFile In objFolder
If Right(LCase(aFile.Name), 4) = ".xls" Then
'do you open and close
End If
Next
Set objFolder = Nothing
Else
'something appro
End If

 

[PHV]

You may consider to use myExcelWorker.GetOpenFilename

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrmovie]

sorry objFolder.Files

 

[Gadall]

getting an error of object does not support this property or method for the line

For Each aFile In objFolder

 

[Gadall]

the line
For Each aFile In objFolder

should be

For Each aFile In objFolder.Files

 

[Gadall]

if FSO.FolderExists(oXL.DefaultFilePath) then
Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)
For each aFile in oFolder.Files
If Right(LCase(aFile.Name), 4) = ".xls" Then
oXL.Workbooks.Open(aFile.Name)
oXL.Visible = True
oXL.ActiveWorkBook.SaveAs aFile.Name
oXL.ActiveWorkBook.Close
End If
Next
Set oFolder = Nothing
end if


This opens the excel file, but seems to continually loop.

Any thoughts on why its looping (there is only 1 file in the directory), Why it isn't saving and exiting?

 

[mrmovie]

'it wont be looping and looping and looping
Wscript.Echo Now() & " b4 the loop"
For Each aFile in oFolder.Files
Wscript.Echo aFile.Name
If Right(LCase(aFile.Name), 4) = ".xls" Then
Wscript.Echo vbTab & "its a match"
End If
Next
Wscript.Echo Now() & " after loop

 

[Gadall]

Dim oXL
Dim oFolder
Dim aFile
Dim FSO

Set oXL = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")

oXL.DefaultFilePath = "C:\DataFiles\NewFiles"

oXL.DisplayAlerts = False
if FSO.FolderExists(oXL.DefaultFilePath) then
Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)

For each aFile in oFolder.Files
If Right(LCase(aFile.Name), 4) = ".xls" Then

oXL.Workbooks.Open(aFile.Name)
oXL.Visible = True
oXL.ActiveWorkBook.SaveAs aFile.Name, -4143
oXL.ActiveWorkBook.Close SaveChanges = True
End If
Next
Set oFolder = Nothing
end if
oXL.DisplayAlerts = True
oXL.Quit
Set oXL = Nothing


This appears to work fine with one minor caveat.
If I open the excel file after running this, it still reformats the file and wants me to save the changes.

If I save the changes manually, when I reopen the file, it no longer does the reformat nor does it ask me to save the changes when exiting.

 

[PHV]

Replace this:
oXL.ActiveWorkBook.Close SaveChanges = True
with this:
oXL.ActiveWorkBook.Close True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gadall]

Same thing

 

[Gadall]

Is there any way to make it pause for 5 seconds between the open & close?

 

[PHV]

pause for 5 seconds
WScript.Sleep 5000

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gadall]

OK some progress
It appears that the macro doesn't fire when opening via this script, but it fires automatically when the file is opened manually, any ideas?

 

[PHV]

Which macro ?
Have a look at the RunAutoMacros method of the Workbook object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gadall]

On a hidden sheet there is a macro named Autpen

 

[Gadall]

oXL.ActiveWorkBook.RunAutoMacros xlAutoOpen ---------------------------

Error: RunAutoMacros method of Workbook class failed
Code: 800A03EC
Source: Microsoft Office Excel

---------------------------

Do I need to reference the hidden sheet to run this macro?

Its on a sheet called Macro1

 

[Gadall]

in VBS the line

oXL.ActiveWorkBook.RunAutoMacros xlAutoOpen

should be

oXL.ActiveWorkBook.RunAutoMacros 1