Problem Exporting to Excel

[JHNielson]

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:

        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:

        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!

[maxamis4]

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

Public objapp As Excel.Application
Public objbook As Excel.Workbook
Public objsheet As Excel.Worksheet
 
Set objbook = Workbooks.Add(Template:=CurrentProject.Path & "\Reservation List.xlt")  'Your excel spreadsheet file goes here
                Set objapp = objbook.Parent
                    Set objsheet = objbook.Worksheets("sheet1") 'Name of sheet you want to export to
                       objbook.Windows(1).Visible = True
                         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" _
                                    & " FROM tbl_Order_Number_Arch_sub INNER JOIN tbl_z_tempDID ON tbl_Order_Number_Arch_sub.AutoNumber = tbl_z_tempDID.DIDReference" _
                                    & " WHERE (((tbl_Order_Number_Arch_sub.Carrier_OrderNumber)='" & Carrier_OrderNumber & "'))" _
                                    & " ORDER BY tbl_z_tempDID.DID"
                                Set rs = db.OpenRecordset(sSQL)
 
                                With objsheet
                                    .Select
                                        .Range("B3") = Carrier_OrderNumber
                                            .Range("B4") = OrderDate
                                                .Range("B5") = rs!Did
                                                    If lbl_description.Caption = " DID" Then
                                                    .Range("B6") = "N/A"
                                                         .Range("B7") = "N/A"
                                                            .Range("B8") = "N/A"
                                                     Else
                                                    .Range("B6") = Forms!frm_Order_Req!frm_Order_Req_Address!Address
                                                        .Range("B7") = SiteReference
                                                            .Range("B8") = CrossAtSite
 
                                                    End If
                                                                .Range("B10") = RSD
                                                                    .Range("A13").CopyFromRecordset rs
                                                                        .Range("B13").CopyFromRecordset rs!SDN_ckt_Id
                                End With
                                   '.Range("E1") = Now()
                                objapp.Visible = True
 

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:

        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:

        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!

[JHNielson]

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

[RyanALEX]

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.