export to excel question

[siamandm]

Hi

im using this code to export a query to excel sheet

Code: Select all

Dim outputFileName As String
outputFileName = CurrentProject.Path & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrySummary", outputFileName, True

how to :
1- enable the user to choose the location to save the file ?
2- and open the file when the export finished automatically?

regards

[HansV]

Copy the following code to the top of the module:

Code: Select all

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hwnd As Long, _
  ByVal lpOperation As String, _
  ByVal lpFile As String, _
  ByVal lpParameters As String, _
  ByVal lpDirectory As String, _
  ByVal nShowCmd As Long) As Long

Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWNORMAL As Long = 1

Use code like this to export and open:

Code: Select all

Sub Test()
    Dim strFolder As String
    Dim outputFileName As String
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        If .Show Then
            strFolder = .SelectedItems(1)
        Else
            MsgBox "No folder selected!", vbExclamation
            Exit Sub
        End If
    End With
    outputFileName = strFolder & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrySummary", outputFileName, True
    If ShellExecute(Application.hWndAccessApp, "Open", outputFileName, 0&, 0&, SW_SHOWNORMAL) < 33 Then
        MsgBox "Couldn't open file.", vbInformation
    End If
End Sub

[siamandm]

thanks for the reply
1- for the first part: do i need to create a new model?
2- if I want to export desired columns from the query what should i do please?

regards

[HansV]

1. You don't have to create a new module. You can copy the first part of the code that I posted to the top of an existing module (below Option Compare Database and similar lines, but above all Subs and Functions).

2. Create a query that returns only the columns that you want to export, save it, and use that one in DoCmd.TransferSpreadsheet.

[siamandm]

i have a module to relink the table in split database, you mean i can put this code inside this Module?

[HansV]

That is possible, but since the code serves an entirely different purpose, I would use a separate module. That makes it easier to maintain the code later on.

[siamandm]

thanks Hans
when i run debug i get this issue below :

Capture.PNG

[HansV]

You should either place the first bit of code that I posted (Private Declare Function ShellExecute ...) in the same module as the Test procedure, or - if you place that code in a standard module - change all three occurrences of Private in that code to Public.

[siamandm]

thanks alot , now is working fine..
but i notice one thing, it does not allow you to give a name to the exported excel file.


regards

[HansV]

Do you want the user to be prompted for the filename?

[siamandm]

it will be greater.

[HansV]

Try this:

Code: Select all

Sub Test()
    Dim outputFileName As String
    With Application.FileDialog(2) ' msoFileDialogSaveAs
        .Title = "Please specify a .xls file"
        If .Show Then
            outputFileName = .SelectedItems(1)
        Else
            MsgBox "No folder selected!", vbExclamation
            Exit Sub
        End If
    End With
    outputFileName = strFile
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrySummary", outputFileName, True
    If ShellExecute(Application.hWndAccessApp, "Open", outputFileName, 0&, 0&, SW_SHOWNORMAL) < 33 Then
        MsgBox "Couldn't open file.", vbInformation
    End If
End Sub

[CData]

So in the better-late-than-never philosophy - I thought I would contribute some input on this topic for any browsers interested in the topic: You don't have to write any code for this any more.....

Create a Macro, and select 'ExportWithFormating'
Select your object type (table, query) that is to be exported
Select your Output format type i.e excel
but leave the 'Output File' field blank

then when you run that macro it is going to trigger the file explorer for the user to locate and name where the file should go..... makes one almost begin to like macros!!

[siamandm]

So in the better-late-than-never philosophy - I thought I would contribute some input on this topic for any browsers interested in the topic: You don't have to write any code for this any more.....

Create a Macro, and select 'ExportWithFormating'
Select your object type (table, query) that is to be exported
Select your Output format type i.e excel
but leave the 'Output File' field blank

then when you run that macro it is going to trigger the file explorer for the user to locate and name where the file should go..... makes one almost begin to like macros!!

thanks for your suggestion, what i will do i will create this macro and then convert it to vba codes as i dont like macros :)