Exporting Access table to excel table

[TaylorBo]

I'm trying to figure out what the code is in order for me to do the following...

I have the code below which exports an access file to an excel file. Currently it exports the file to a set folder. What code will enable a prompt so that the user can select where to export and save the file to? It's a "save as" type function I'd think, but I'm not super familar with VBA.

Thanks.

Private Sub Command1_Click()
'***EXCEL EXPORT
On Error GoTo COMMAND1_Click_ERR
   Call Msg_Box(36)
    If G_MSGKBN = 2 Then
        Exit Sub
    End If
Dim Q_T_SHADAI_EXCEL  As QueryDef
Dim F_NUM As Double
    
    'FILECD GA EXCEL NI MAJITTARA ERROR
    F_NUM = DCount("FILECD", "V_PLAN", "FILECD <> '" & G_FILECD & "'")
    If F_NUM <> 0 Then
        Call Msg_Box(7)
        Exit Sub
    End If

    'DATA DELETE
    Kill "C:\GNENKEI\VEHICLE_PLAN.XLS"
    
    'FONT CHANGE
    Call ole_Exl_font("Q_T_SHADAI_SUB", "C:\GNENKEI\VEHICLE_PLAN.XLS")
    
    'EXPORT
    Shell (EXCEL_PG & "C:\GNENKEI\VEHICLE_PLAN.XLS")
    
    Exit Sub

 

[ListO]

Take a look in Access Help under "TransferSpreadsheet." It does most of the coding.

As for choosing the target directory and filename, I use the commondialog directX control. I'm using Access97, and the CommonDialog control was only available with the Developer Edition Tools which was sold as a separate item. I'm not sure what it's status is in post-97 versions.

I'm sure SOMEBODY else out there knows!

 

[mikebaldam]

Stick this on a command button and it will prompt you where to save the excelfile (replace YOUR_TABLE_NAME with the tablename - of course)

On Error GoTo Error
    
DoCmd.OutputTo acTable, "YOUR_TABLE_NAME", "MicrosoftExcelBiff8(*.xls)", "", False, "", 0
    
Error:     MsgBox Err.Description
Exit Sub

 

[hbasbay]

Stick this on a command button and it will prompt you where to save the excelfile (replace YOUR_TABLE_NAME with the tablename - of course)

On Error GoTo Error
    
DoCmd.OutputTo acTable, "YOUR_TABLE_NAME", "MicrosoftExcelBiff8(*.xls)", "", False, "", 0
    
Error:     MsgBox Err.Description
Exit Sub

thank you very much for your code.I want to ask 2 question

first one is when I use this code to export data from access to excel , I saw a message dialog box.But this dialog box is empty.Is that possible to give a succesful message in this diaolog box?

the second question is , this code is record excel 5-7 format.how can I record excel 2000-2003 format?

 

[GaryPanic]

this is great, this does a lot of what I want, but i want to tweak it 1 step further , I want to have a button on a form to do this, but also have a text field onth e form to state the location example C:\\temp\test.xls

this would let you name the location and allow you to rename file/location

regards