TransferSpreadsheet to Excel (Multiple Tabs)

[smoothlarryhughes]

Hi All...

I have the following code, which is exporting 4 queries to 4 tabs on an excel file. The only issue I'm having with this is there is a tick mark in front of most of the cells, and some of my columns, which are numbers, are showing up as text, and excel gives an option to "covert to number" after I open it in excel. I tried using different excel formats, but some of the other formats won't give me multiple tabs. Any suggestions? I'd also like the file to open after the export if possible.

Private Sub Command1_Click()

On Error GoTo Err_bExportCurrentRecord_Click

Dim sLocation As String
Dim sFileName As String

sLocation = "I:\415757_JamesRogers\PHREF_TEST\"
sFileName = Format(Now(), "YYYYMMDD") & "AAM_RECAP_PHREF.xls"
' MsgBox sFileName 'used for testing
If Dir(sLocation & sFileName) <> "" Then
'MsgBox "The " & sLocation & sFileName & "already exists!"
Kill sLocation & sFileName
Else
'MsgBox "file does not exists"
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryFINALAAMRECAPdistinct", sLocation & sFileName, True, "AAMRECAP"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryCHECKER-SORTED", sLocation & sFileName, True, "CHECKER"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tblPHREF", sLocation & sFileName, True, "PHREF"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryNOTONAAMRECAP", sLocation & sFileName, True, "NOT_ON_AAM"

Beep
MsgBox "The file has been exported"

Exit_bExportCurrentRecord_Click:
Exit Sub

Err_bExportCurrentRecord_Click:
If Err = 75 Or Err = 3010 Then
Beep
MsgBox "The '" & sFileName & "'file is open." & vbCrLf & vbLf & "Please close the '" & sFileName & "' file before trying to export the data for current Position ID " & sRecordID & ".", vbCritical, "Export Error >>> " & sLocation & sFileName
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_bExportCurrentRecord_Click
End If


End Sub

[June7]

Which of the exports are resulting in numbers treated as text in Excel? Is it the 3 queries? Are those numbers the result of calculations, maybe using Format function?

[smoothlarryhughes]

all of the queries have some columns that are being exported as text. they are the results of calculations. Is there a way to format in the calculation? Any idea on the tick mark that is showing up in all the cells?

[June7]

The tick mark (apostrophe) forces Excel to treat the data as text and not numbers or formulas (as in an ssn with hyphens). I am not sure why the export is prefixing with the mark.

So the export from table does not show the prefix mark?

Show query SQL statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

[smoothlarryhughes]

The tick mark (apostrophe) forces Excel to treat the data as text and not numbers or formulas (as in an ssn with hyphens). I am not sure why the export is prefixing with the mark.

So the export from table does not show the prefix mark?

Show query SQL statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

PHREF_MDB_testing.zip

I've attached a copy of my database. Basically I have 3 tables - I format all 3 of those tables using 3 queries. Then I have qryCHECKER that I do some basic comparisons - then use qryCHECKER-SORTED to sort the query because I was running into some issues where I couldn't sort the original query because it kept popping up criteria windows when i would try to run the query sorted. I then query the notes generated in the checker to qryFINALAAMRECAP and then use qryFINALAAMRECAPdistinct to give me back only distinct trade id's.

i have one form that i use to clear the tables, repopulate the tables, and then export the data.

i appreciate all of your help.

Thanks!

[June7]

I am using Access 2007, no longer have Access 2003 available.

I exported with your code and none of the data have tick marks. However, many of the values that are composed of numbers such as Order_ID, Trade_ID, and some amount fields do have cell message 'Number Stored as Text'. NetDiff column is displaying some values in scientific notation.

[smoothlarryhughes]

I am using Access 2007, no longer have Access 2003 available.

I exported with your code and none of the data have tick marks. However, many of the values that are composed of numbers such as Order_ID, Trade_ID, and some amount fields do have cell message 'Number Stored as Text'. NetDiff column is displaying some values in scientific notation.

PHREF_MDB_testing (1).zip

I reattached this in 2007 format. I have noticed that when i export this from access 2010 i don't get the tick marks. I do, however, still get the "tradeid_aam" and "tradeid_hp_orders" showing up in txt format, and would like this in number format. if you just change the directory of output you can see what i'm talking about.

[June7]

I did, of course, change the directory of output to run your code.

I took closer look at the data for cells showing 'numbers as text'. Actually, the export is working correctly.

The ID values are just fine as text because they are identifiers, not quantifiers.

NET_HP and PRICE_HP_ORDERS return either a number or text depending on IIf criteria, so these fields must export as text.

SHARES in tblAAMRECAP is a text type. It has a field size of 255, formatted as a date, aligns right. Remove the date format and it aligns left. (LocalPriceCurrency is properly set as text type but also has date format).

PriceDiff uses scientific notation because data calculates to 8 decimal places and NetDiff appears to calculate even further out.

[smoothlarryhughes]

Is there a way to use transferspreadsheet like above, exporting to 3 tabs, but if the file already exists (if the user makes a mistake and has to rerun the queries/export) how do I have it asks to overwrite the entire file?

[June7]

You already have code in the procedure to do that. If file exists it is deleted.