maintain formatting in Excel

[KellyK]

Hi all. I have an Access command button that once clicked, runs an Impromptu report, exports it to Excel, and then transfers the Excel spreadsheet to a table in my Access database. The problem is that when the Impromptu report is exported to Excel, I lose all formatting (data types). Because the field names change on a weekly basis (I use week numbers as field names), it is very tedious to alter the field types using VBA code. I'm wondering if there's a way to keep formatting from Impromptu to Excel. I'm using Impromptu 6.0 and Excel 2000. Specifically, currency fields and percent fields are coming across as 'number' and therefore the percentages are very ugly (example: .686357586235) My code follows:

Sub RunImpromptuReport(strCatalog As String, strReport As String, strFile As String, _
    Optional strPrompt As String, _
    Optional strDatabaseUserId As String = "", _
    Optional strDatabasePassword As String = "", _
    Optional strCatalogUserId As String = "", _
    Optional strCatalogPassword As String = "", _
    Optional bPrintIt As Boolean = False)
    
On Error GoTo Err_RunImpromptu

Dim objImpApp As Object
Dim objImpRep As Object

Set objImpApp = CreateObject("Impromptu.Application")
Set objImpRep = CreateObject("Impromptu.Application")

objImpApp.OpenCatalog strCatalog, strDatabaseUserId, strDatabasePassword, _
strCatalogUserId, strCatalogUserId
objImpApp.Visible -1
Set objImpRep = objImpApp.OpenReport(strReport, strPrompt)
objImpRep.RetrieveAll
If bPrintIt Then objImpRep.PrintOut
objImpRep.ExportLotus (strFile)
objImpRep.CloseReport
objImpApp.Quit

Set objImpRep = Nothing
Set objImpApp = Nothing
    
Exit_RunImpromptu:
    Exit Sub

Err_RunImpromptu:
    MsgBox Err.Description
    Resume Exit_RunImpromptu

End Sub

and I use DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeLotusWK1, "tblSalesDataSummary", _
strFile, True

The code example is using Lotus, but I've tried with both Lotus and Excel to no avail. Any suggestions are appreciated, otherwise I will have to alter the field types using code which I'm not 100% clear on how to do.
Thanks!
Kelly