Anything Access

SAP – Changing the format of downloaded files

The SAP system often downloads files as MHT which can be opened in excel but can be enormous

After getting a list of files for conversion I pass the file name (tmpFile) to this function to load the file listing and then save the file as Excel 2003 for easy import into and Access or SQL database

Function ChangeFormat(tmpFile)
Dim success As Long
Dim xlApp As Object
;first clear the destination folder of any temporary xls/mht files
If Dir("c:\test\*.xls")  "" Then
    Kill "c:\test\*.xls"
End If
If Dir("c:\test\*.mht")  "" Then
    Kill "c:\test\*.mht"
End If
If fIsAppRunning("Excel") Then
    Set xlApp = GetObject(, "Excel.Application")
    Set xlApp = CreateObject("Excel.Application")
End If
If Dir(tmpFile)  "" Then
    Set xlApp = CreateObject("Excel.Application")
    xlApp.workbooks.Open tmpFile
    'xlApp.Visible = True
    xlApp.DisplayAlerts = False
    xlApp.activeworkbook.Saveas "c:\test\OrderImport.xls", FileFormat:=56
    Set xlApp = Nothing
End If
'MsgBox "Done processing " & tmpFile       

End Function
Hi, I’m Pat