I am exporting queries to a 4 sheet XL workbook. The transfer spreadsheet
method puts a ' in front of all of my numbers, necessitating a formatting
procedure to remove them and properly format the fields. That's fine when
there are a manageable number of cells to be formatted, but I have one sheet
that has a matrix on the order of 175X175 cells that causes my StripXLFormats
function (shamelessly pilfered from this newsgroup) to take upwards of 15
minutes to complete. This is unacceptable, so I'm trying to work around the
problem with this one sheet by exporting it first, via TransferText to a CSV
file, obviating the need to strip the ' from each cell and reformatting cell
by cell. I then want to open it in Excel and save it as an Excel workbook.
Then I can export the remaining 3 queries as Excel worksheets and format them
as needed.
The problem arises in the following code on the line "WkBk.Close SaveChanges:
=True". I think it's because it's a CSV file that I'm trying to save as an
Excel workbook. Any suggestions would be deeply appreciated.
DoCmd.TransferText acExportDelim, , strQuery, strPath & strCSVFile, True,
""
Set xlObj = CreateObject("excel.application")
With xlObj
Set WkBk = .Workbooks.Open(strPath & strCSVFile)
WkBk.SaveAs Filename:=strPath & strFile
Set WkSht = WkBk.ActiveSheet
WkSht.Name = strSheet
WkBk.Save
DoEvents
WkBk.Close SaveChanges:=True
DoEvents
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryPEExport", strPath & strFile, True, "PEExport"
StripXLFormats strPath & strFile, "PEExport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryCurveExp", strPath & strFile, True, "Curve"
StripXLFormats strPath & strFile, "Curve"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryPEFactorsExp", strPath & strFile, True, "PE_Factors"
StripXLFormats strPath & strFile, "PE_Factors"
Set WkBk = .Workbooks.Open(strPath & strFile)
Set WkSht = WkBk.Sheets(strSheet)
With WkSht
.Move after:=Worksheets("PE_Factors")
.Range("B2").Select
.Range(Selection, Selection.End(xlDown)).Select
.Range(Selection, Selection.End(xlToRight)).Select
Selection.Style = "Currency"
End With
Screen.MousePointer = 0
.Save
.Quit
End With
--
Bill Reed
"If you can't laugh at yourself, laugh at somebody else"
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1
