Export to Excel with File Browser

J

jguscs

Registered User.
Local time
Today, 15:03
Joined
Jun 23, 2003
Messages
148
Can anyone post some example code for exporting a table from Access as an Excel file with a file browser?
Basically, I want to be able to browse for the path and use that string in the PATH part of the code below.
Any help would be great.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "EP Import Template", "PATH" & "EP Import Template.xls", True
 
If you're looking to export to Excel, try OutputTo:

DoCmd.OutputTo acOutputTable, "Tbl_YourTable", acFormatXLS

You will then be prompted for where you want to save it to.
 
Excellent!
The only point I should bring up is that there needs to be some error-catching code in case the Cancel button is pressed on the browse dialog window (otherwise the VB debugger spits out an error...)
Thanks very much!
Why wasn't something like this made available for IMPORTING with a file browser???
 
OK, ready for some code? Most of this stuff I've copied from this forum. Put these in a module except for the Get_XL procedure. Put that in your form:
PHP: 
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
    
End Type

Function LaunchCD(strform As Form) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String

    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.hwnd
    sFilter = "Excel Files (*.xls)" & Chr(0) & "*.xls" & Chr(0) & "All Files (*.*)" & Chr(0) & "*.*" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "MYEXCELFOLDER" 'Change this to a default folder'
    OpenFile.lpstrTitle = "Select a file ..."
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
    If lReturn = 0 Then
        MsgBox "A file was not selected!", vbInformation, _
          "Select a file ..."
    Else
        LaunchCD = Left(OpenFile.lpstrFile, InStr(OpenFile.lpstrFile, ".") + 3)
    End If

End Function

'Have this in a form'
Public Sub Get_XL() 
Dim TheFile As String

        TheFile = LaunchCD(Me) ' Me is the form name from which you are trying to do the import '
        If TheFile <> "" Then
             DoCmd.TransferSpreadsheet acImport, 8, "YourTable", TheFile, True
        End If

End Sub

Now, you can use this to use the TransferSpreadsheet when doing an export.
 
... or you can reference the Excel Object Library and use this functionality:

PHP: 
Public Sub TheSaveAs()
Dim fileSaveName As String
Dim objXLS As New Excel.Application

fileSaveName = objXLS.GetSaveAsFilename(InitialFilename:="EP Import Template.xls", fileFilter:="Excel Files (*.xls), *.xls")
Set objXLS = Nothing

If fileSaveName <> False Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "EP Import Template", fileSaveName, True
End If
 
Ah, yes, that is the code I am "familiar" with, pdx_man.
But I prefer your first option, the DoCmd.OutputTo.
It works very nicely and it doesn't require more code.
Also, I prefer not to involve other object libraries (ADO), etc..., but you sure know your stuff.

One question for you:
Is it possible to take any of the following code out of a module and put it in the form?

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
...
lpTemplateName As String

End Type

Function LaunchCD(strform As Form) As String
Dim OpenFile As OPENFILENAME
...
End Function
 
You could, but then you lose the scalabilty of being able to call this from any form. If you put it in a form, then the scope gets limited to that form. I guess my question would be why do you want to move it out of the module level? How does that add value?
 
Well, actually, my goal is to simply cut away as much of the code I'm using for the import-browse process as possible. The problem is that I really don't know what the code is doing exactly. I've already managed to trim it down some, but my ultimate goal is to get it small enough so that it can fit discretely in my Form code. I'm not re-using the code outside of the form, and so the advantage to me is that I'll have all of the code in one place. I tried moving the code to the Form before, but the debugger said it was missing [Private Type OPENFILENAME] or [Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long] or something along those lines. The code that follows is what I have for the Import-browse process...
What parts of this do I NEED and what parts can I cut out?

Option Compare Database
Option Explicit
Public Const OFN_ALLOWMULTISELECT As Long = &H200
Public Const OFN_CREATEPROMPT As Long = &H2000
Public Const OFN_ENABLEHOOK As Long = &H20
Public Const OFN_ENABLETEMPLATE As Long = &H40
Public Const OFN_ENABLETEMPLATEHANDLE As Long = &H80
Public Const OFN_EXPLORER As Long = &H80000
Public Const OFN_EXTENSIONDIFFERENT As Long = &H400
Public Const OFN_FILEMUSTEXIST As Long = &H1000
Public Const OFN_HIDEREADONLY As Long = &H4
Public Const OFN_LONGNAMES As Long = &H200000
Public Const OFN_NOCHANGEDIR As Long = &H8
Public Const OFN_NODEREFERENCELINKS As Long = &H100000
Public Const OFN_NOLONGNAMES As Long = &H40000
Public Const OFN_NONETWORKBUTTON As Long = &H20000
Public Const OFN_NOREADONLYRETURN As Long = &H8000& 'see comments
Public Const OFN_NOTESTFILECREATE As Long = &H10000
Public Const OFN_NOVALIDATE As Long = &H100
Public Const OFN_OVERWRITEPROMPT As Long = &H2
Public Const OFN_PATHMUSTEXIST As Long = &H800
Public Const OFN_READONLY As Long = &H1
Public Const OFN_SHAREAWARE As Long = &H4000
Public Const OFN_SHAREFALLTHROUGH As Long = 2
Public Const OFN_SHAREWARN As Long = 0
Public Const OFN_SHARENOWARN As Long = 1
Public Const OFN_SHOWHELP As Long = &H10
Public Const OFS_MAXPATHNAME As Long = 260

'OFS_FILE_OPEN_FLAGS and OFS_FILE_SAVE_FLAGS below are mine to save long statements; they're not
'a standard Win32 type.
Public Const OFS_FILE_OPEN_FLAGS = OFN_EXPLORER _
Or OFN_LONGNAMES _
Or OFN_CREATEPROMPT _
Or OFN_NODEREFERENCELINKS
Public Const OFS_FILE_OPEN_MULTI_FLAGS = OFN_EXPLORER _
Or OFN_LONGNAMES _
Or OFN_CREATEPROMPT _
Or OFN_NODEREFERENCELINKS _
Or OFN_ALLOWMULTISELECT
Public Const OFS_FILE_SAVE_FLAGS = OFN_EXPLORER _
Or OFN_LONGNAMES _
Or OFN_OVERWRITEPROMPT _
Or OFN_HIDEREADONLY

Private Const BIF_RETURNONLYFSDIRS = 1
Private Const MAX_PATH = 260

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long

Private Type BrowseInfo
hWndOwner As Long
pIDLRoot As Long
pszDisplayName As Long
lpszTitle As Long
ulFlags As Long
lpfnCallback As Long
lParam As Long
iImage As Long
End Type
Private Type OPENFILENAME
lStructSize As Long
hWndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Public Function GetOpenFileNameGs(objForm As Form) As String
Dim sFileFilter As String
Dim lReturn, lFlag As Long
Dim OpenFile As OPENFILENAME
sFileFilter = "Excel Files (*.XLS)|*.XLS|Comma Delimited Files (*.CSV)|*.CSV|" & _
"DEL File (*.DEL)|*.DEL|All Files|*.*||"
lFlag = 0
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hWndOwner = objForm.Hwnd
OpenFile.hInstance = Application.hWndAccessApp
OpenFile.lpstrFilter = Replace(sFileFilter, "|", vbNullChar)
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = "Open File"
OpenFile.flags = lFlag
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
GetOpenFileNameGs = ""
Else
GetOpenFileNameGs = Replace(OpenFile.lpstrFile & "", vbNullChar, "")
End If
End Function
 
For example, the following variables of the TYPE OPENFILENAME do not seem to be used in the GetOpenFileNameGs function... I'm assuming that they may be used under other circumstances. But for my purposes, can I safely remove these from the Type declaration?

lpstrCustomFilter As String 'NOT USED
nMaxCustFilter As Long 'NOT USED
nFileOffset As Integer 'NOT USED
nFileExtension As Integer 'NOT USED
lpstrDefExt As String 'NOT USED
lCustData As Long 'NOT USED
lpfnHook As Long 'NOT USED
lpTemplateName As String 'NOT USED
 
OK, I getcha. It is not a good idea to have all of your code located in one place for the sake of having it all wrapped up in a nice bundle. You will find it far more trouble than what it is worth. While it may look neat, what has to be done and the memory to allocate will get things really screwed up in the long run. Keep the main parts in a module and call them from your form. Trust me. Otherwise these declarations must be executed every time you call the form, whether or not it is being used. If it is in a module, the work is only done when needed. Like I mentioned above, it will also keep your project scalable.

As far as getting rid of the other lines ... try it ... you'll save yourself about 500 bytes, but that's about it. And if you ever do need them, will you remember their names and datatypes? These are properties with the windows api and are specific to the name. I wouldn't go messin' with it.
 
Last edited:
Alright. Can you at least tell me if and how it might be possible to combine some of the variables of the same type during the OPENFILENAME type declaration?
There are a number of Long, String and Integers.
Can they be declared on the same line?
For example:
lStructSize, hWndOwner As Long

Private Type OPENFILENAME
lStructSize As Long
hWndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
 
Try it. I've never seen the need to, but go ahead and try it.
Report your results back here.

Just a side note ... I'm guessing your sock and underware drawer are very neat and organized, true?
 
Not really.
And it didn't work.
 
Last edited:
Hi,

In this case:
Code: 
lStructSize, hWndOwner As Long
lStructSize is declared as Variant, not as Long. You can do it in this way:
Code: 
lStructSize As Long, hWndOwner As Long
I mention this, because it's a general problem (solved in VB.NET, bot still not in Access (2003) ).

For this particular case, I agree with pdx_man: there's actually no need to do this. You would think to make the procedure more readible, but later you'll see that it isn't.
I (personally) never declare more than one variable at one line and when I read back my code, I can find very quick what's where...

Greetz,
Bert
 
You must log in or register to reply here.

Similar threads

J
Use VBA to export form data to Excel, but keep getting "We Found a Problem..." when opening the file in Excel
Replies
5
Views
529
June7
June7
J
Export data in Work to different folders
Replies
2
Views
338
JPR
J
Juice
Printing html file to a specific printer
Replies
4
Views
342
arnelgp
arnelgp
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
784
oxicottin
oxicottin
C
Make DoCmd.OutputTo not working properly with an Excel template
Replies
1
Views
188
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom