The following is a copy of my post in AccessMonster, but I've had no
response there. I hope there is someone in this newsgroup with enough
familiarity with Access to give me a suggestion. When I open the
workbook resulting from this code, I have a single column with the
comma-seperated values in it, rather than a matrix of an equal number
of rows and columns.

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

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"


--
ragtopcaddy
------------------------------------------------------------------------
ragtopcaddy's Profile: http://www.excelforum.com/member.php...o&userid=25838
View this thread: http://www.excelforum.com/showthread...hreadid=535577

The transfer spreadsheet method / action should not put ' in front of
numbers. It doesn't for me. Check the properties you are seting for that
method / action. You could also post on the Access newsgroup site.

"ragtopcaddy" wrote:


The following is a copy of my post in AccessMonster, but I've had no
response there. I hope there is someone in this newsgroup with enough
familiarity with Access to give me a suggestion. When I open the
workbook resulting from this code, I have a single column with the
comma-seperated values in it, rather than a matrix of an equal number
of rows and columns.

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

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"


--
ragtopcaddy
------------------------------------------------------------------------
ragtopcaddy's Profile: http://www.excelforum.com/member.php...o&userid=25838
View this thread: http://www.excelforum.com/showthread...hreadid=535577

The ' happens because the underlying queries have formatted fields. That
forces Excel to force them to text with the '. I'm trying to get around
having to duplicate the queries that are formatted to display data in a
form, with redesigned queries without the formatting to export to Excel.
This would create a maintenance nightmare in my db. I have several forms
whose underlying queries are exported to Excel and I'd like to use them
"as is" for my exports. Otherwise, any change to a form query will
necessitate a change to it's export counterpart.


--
ragtopcaddy
------------------------------------------------------------------------
ragtopcaddy's Profile: http://www.excelforum.com/member.php...o&userid=25838
View this thread: http://www.excelforum.com/showthread...hreadid=535577