File export to excel

[nayfeh]

Hi,

I desperately need some help with this function. I had set it up to export orders to a text file. Now the user wants the file exported to an excel spreadsheet. I changed the extension in the filename to .xls but when I open it up it in excel it still have to be formatted from text-to-columns. How can I have it automatically formatted when opened in Excel? Will the TransferSpreadsheet method work? If so, can someone show me how or will I have to create an entirely new function.....I hope there's a way around it

Thanks in advance!

Please see my code below:

strFilename = "C:\NO"
strDateString = Format(Now(), "mmddyyyy-hhmm&quot
strFilename = strFilename + strDateString + ".xls"


Set adConn = New ADODB.Connection
Set adRs = New ADODB.Recordset
Set fso = New FileSystemObject


adConn.ConnectionString = "Provider=SQLOLEDB; Data Source=SQL1;Initial Catalog=MMA;User ID=sa; Password="
adRs.CursorLocation = adUseClient

strSql = "sp_Orders " & " '" & strStartDate & "', '" & strEndDate & "' "

adConn.Open
adRs.Open strSql, adConn

Set ts = fso.CreateTextFile(strFilename, True)

Do While Not adRs.EOF

lngTotalQty = 0
lngTotalWt = 0
dblTotPallets = 0

strCustNbr = LTrim(adRs("CustNbr&quot)
strPONbr = LTrim(adRs("PONbr&quot)
strTrackingNbr = LTrim(adRs("TrackingNbr&quot)
strShipDate = LTrim(adRs("ShipDate&quot)
strShipDate = Format(strShipDate, "mmddyyyy&quot
strDeliveryDate = LTrim(adRs("DeliveryDate&quot)
strTotalQty = lngTotalQty
strTotalWt = lngTotalWt
strTotPallets = dblTotPallets

ts.WriteLine strTransCode & "|" & strClientName & "|" & strCustNbr _
& "|" & strDeliveryDate & "|" & strShipDate & "|" & strTrackingNbr & "|" & _
strPONbr & "|" & strTotalWt & "|" & strTotalQty & "|" & strTotPallets

Loop

If adRs.state = adStateOpen Then adRs.Close
If Not fso Is Nothing Then Set fso = Nothing
If Not adRs Is Nothing Then Set adRs = Nothing
If Not adConn Is Nothing Then Set adConn = Nothing

 

[lameid]

Transeferspreadsheet should work great on a table of query. I'd modify sp_Orders to return the results you want or create a similar stored procedure that will. Then modify the SQL property of an SQL pass through query in code and use that query as the source of your TransferSpreadsheet method... I'm still in the DAO/ACC97 world, so there may be a better way.

 

[cristians]

Hi,
I am a beginner, therefore i have a simple solution
Bring the results in a form (Continuous Forms)then
DoCmd.OutputTo acOutputForm, "Form name", acFormatXLS, "File Name.xls", True
in Acc2000