By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 755 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Problem Exporting to Excel

100+
P: 121
I am having quite the unique problem trying to Export to Excel and I need ot find a solution within 3 hours. -- PLEASE HELP!

The system exports a file from the application that the users can make changes to and then reimport. But now matter what format I specify to export to, when I open the exported files, it says they are in Excel 5.0.

I originally used the code:

Expand|Select|Wrap|Line Numbers
  1.         DoCmd.OutputTo acTable, "PRC_REPRT", "MicrosoftExcelBiff8(*.xls)", EXP_FLDR & "Processed Records - " & DATE_CODE & ".xls", False, "", 0
Doing a little research, I found a lot of people said that the "OutputTo" causes the problem and that I should use the "Transferspreadsheet" Command.

Now the code looks like this:
Expand|Select|Wrap|Line Numbers
  1.         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ERR_REPRT", EXP_FLDR & "Error Records - " & DATE_CODE & ".xls", False, , 0
The file exports in the right format, but it left me with a new problem. When I used the OutputTo command, It output with the Caption titles and they were formatted in Grey. It looked nice and i built my reimport mappings based on those titles. To change it now would be a huge undertaking.

So my question is, can I get the transferspreadsheet method to output the caption names not the actual field names (i.e "Serial Code" instead of "SERL_CD")


Thanks to anyone with some ideas or helpful hints!
May 14 '07 #1
Share this Question
Share on Google+
3 Replies


maxamis4
Expert 100+
P: 295
The simple answer now. This method is used to export raw data, its quick neat but ineffictive for what you want.

What you want is something like this

Expand|Select|Wrap|Line Numbers
  1. Public objapp As Excel.Application
  2. Public objbook As Excel.Workbook
  3. Public objsheet As Excel.Worksheet
  4.  
  5. Set objbook = Workbooks.Add(Template:=CurrentProject.Path & "\Reservation List.xlt")  'Your excel spreadsheet file goes here
  6.                 Set objapp = objbook.Parent
  7.                     Set objsheet = objbook.Worksheets("sheet1") 'Name of sheet you want to export to
  8.                        objbook.Windows(1).Visible = True
  9.                          sSQL = "SELECT tbl_z_tempDID.DID, tbl_Order_Number_Arch_sub.ISDN_ckt_Id, tbl_Order_Number_Arch_sub.VerizonBTN, tbl_Order_Number_Arch_sub.SDP_Address, [tbl_Order_Number_Arch_sub]![Circuit_ID1] & ' - ' & Right([tbl_Order_Number_Arch_sub]![Circuit_ID2],4) AS MCI_CircuitID, tbl_Order_Number_Arch_sub.[MCI BTN], tbl_Order_Number_Arch_sub.Switch, tbl_Order_Number_Arch_sub.Comments" _
  10.                                     & " FROM tbl_Order_Number_Arch_sub INNER JOIN tbl_z_tempDID ON tbl_Order_Number_Arch_sub.AutoNumber = tbl_z_tempDID.DIDReference" _
  11.                                     & " WHERE (((tbl_Order_Number_Arch_sub.Carrier_OrderNumber)='" & Carrier_OrderNumber & "'))" _
  12.                                     & " ORDER BY tbl_z_tempDID.DID"
  13.                                 Set rs = db.OpenRecordset(sSQL)
  14.  
  15.                                 With objsheet
  16.                                     .Select
  17.                                         .Range("B3") = Carrier_OrderNumber
  18.                                             .Range("B4") = OrderDate
  19.                                                 .Range("B5") = rs!Did
  20.                                                     If lbl_description.Caption = " DID" Then
  21.                                                     .Range("B6") = "N/A"
  22.                                                          .Range("B7") = "N/A"
  23.                                                             .Range("B8") = "N/A"
  24.                                                      Else
  25.                                                     .Range("B6") = Forms!frm_Order_Req!frm_Order_Req_Address!Address
  26.                                                         .Range("B7") = SiteReference
  27.                                                             .Range("B8") = CrossAtSite
  28.  
  29.                                                     End If
  30.                                                                 .Range("B10") = RSD
  31.                                                                     .Range("A13").CopyFromRecordset rs
  32.                                                                         .Range("B13").CopyFromRecordset rs!SDN_ckt_Id
  33.                                 End With
  34.                                    '.Range("E1") = Now()
  35.                                 objapp.Visible = True
  36.  

With this one you create a template with all the nice trimmings you want and using the with function you can export to specific cells in excel
I am having quite the unique problem trying to Export to Excel and I need ot find a solution within 3 hours. -- PLEASE HELP!

The system exports a file from the application that the users can make changes to and then reimport. But now matter what format I specify to export to, when I open the exported files, it says they are in Excel 5.0.

I originally used the code:

Expand|Select|Wrap|Line Numbers
  1.         DoCmd.OutputTo acTable, "PRC_REPRT", "MicrosoftExcelBiff8(*.xls)", EXP_FLDR & "Processed Records - " & DATE_CODE & ".xls", False, "", 0
Doing a little research, I found a lot of people said that the "OutputTo" causes the problem and that I should use the "Transferspreadsheet" Command.

Now the code looks like this:
Expand|Select|Wrap|Line Numbers
  1.         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ERR_REPRT", EXP_FLDR & "Error Records - " & DATE_CODE & ".xls", False, , 0
The file exports in the right format, but it left me with a new problem. When I used the OutputTo command, It output with the Caption titles and they were formatted in Grey. It looked nice and i built my reimport mappings based on those titles. To change it now would be a huge undertaking.

So my question is, can I get the transferspreadsheet method to output the caption names not the actual field names (i.e "Serial Code" instead of "SERL_CD")


Thanks to anyone with some ideas or helpful hints!
May 14 '07 #2

100+
P: 121
Is there a simpler way than this? I have over 110 columns I would have to map.

If this is the only way, I will do it, but I was hoping there might be an easier way...
May 14 '07 #3

P: 3
It is too late ,But I suggest to use an c# excel component, I use it from last year, there may be a easier way to you.
Aug 18 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.