TransferSpreadsheet with prompt for file location

[FoolzRailer]

Hello

I'm trying to Export 2 queries into one Excel file, in two different sheets. I can get this to work, by calling the below code in a macro.

Public Function Export2Queries()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml,  "Concatenate", "C:\Users\Username\Desktop\ExportTest.xlsx", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml,  "SaneringTilImport", "C:\Users\Username\Desktop\ExportTest.xlsx", True
End Function

I would like to get a prompt for the file location, instead of hardcoding it. I'm using Access 2010 64-bit.

Any help would be much appreciated.

 

[CJ_London]

investigate using the FileSystemObject which opens a windows file directory browser- here is a link

https://support.microsoft.com/en-gb/kb/186118

 

[FoolzRailer]

Thanks for the reply. Trying to have a read, but not quite sure I how incorporate it.

Trying to build a form with 4 buttons as specified, and copied the code. However I get a compile error "User-defined type not defined" in Dim fso As New FileSystemObject.

 

[CJ_London]

sorry that link was for VB rather than VBA tho' the code is the same

you need to go to references and add Microsoft file scripting or declare your variables slightly differently - see this link

https://bytes.com/topic/access/answers/559113-filesystemobject

The web has many resources so suggest google something like 'access vba filesystemobject' or similar for more threads on the subject

 

[smellyalater]

Hi FoolzRailer

This link here will help you achieve the prompt for saving to a certain location:

http://access.mvps.org/access/api/api0001.htm

 

[FoolzRailer]

Hello again, sorry for the slow response, but thank you for the help. I tried copy pasting the code into a Visual Basic Module in Access, but when compiling, I get an error saying it must be updated to x64 bit systems, please review and update declare statements.
Any idea how to fix this?

Alright I added PtrSafe (Declare PtrSafe Function) and now it compiles.

So the next might be a stupid question, but how do I get it to into my transferspreadsheet, so it asks for a save location?

Edit: Just to clarify, I'm using the code smellyalater linked to.

 

[CJ_London]

you don't - filedialog returns the path (a string) so run this first and assign the returned value to a temporary string variable, then use this in transferspreadsheet

 

[arnelgp]

FileDialog has a bug. will not return the path of a network drive (tested on x64 access).

 

[FoolzRailer]

Just posting here to say I figured it out and leaving my code if others need it, both for exporting or importing where you use Transferspreadsheet and filedialog (file dialog).

Filedialog for saveas location, code to be put in module:

Option Compare Database
Public Function FilToSave()

Dim FlDia As FileDialog

Set FlDia = Application.FileDialog(msoFileDialogSaveAs)

With FlDia
    .AllowMultiSelect = False
    .InitialFileName = "C:\"  ' You can set outfile to a full path with a fictitious  or real file name, and the dialog will open in that folder.
    .Title = "Navngiv filen med det ønskede navn"
    If .Show = True Then
        FilName = .SelectedItems(1)
       
    Else
        MsgBox "No file selected. Process cancelled"
        DoCmd.Hourglass False
        FilToSave = "Cancelled"
        Exit Function
        End If
End With
 
FilToSave = FilName & ".xls"

End Function

Calling Filedialog into Transferspreadsheet:

Public Function Export2Queries()
Dim savefile As String
savefile = FilToSave
If savefile <> "Cancelled" Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SaneringsVurdering", savefile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "K_SaneringLedMet", savefile, True
End If
End Function

Filedialog for picking a file to import, code to be put in module:

Option Compare Database
Public Function FilToImport()

Dim FlDia As FileDialog

Set FlDia = Application.FileDialog(msoFileDialogFilePicker)

With FlDia
    .AllowMultiSelect = False
    .InitialFileName = "C:\"  ' You can set outfile to a full path with a fictitious  or real file name, and the dialog will open in that folder.
    .Title = "Vælg fil til import"
    .Filters.Clear
    .Filters.Add "Microsoft Excel", "*.xls"
    .Filters.Add "Alle filtyper", "*.*"
    If .Show = True Then
        FilName = .SelectedItems(1)
       
    Else
        MsgBox "No file selected. Process cancelled"
        DoCmd.Hourglass False
        FilToImport = "Cancelled"
        Exit Function
        End If
End With
 
FilToImport = FilName

End Function

Calling import in transferspreadsheet:

Public Function Import2Columns()
Dim importfil As String
importfil = FilToImport
If importfil <> "Cancelled" Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SaneringTilImport", importfil, True, "SaneringsVurdering!L:M"
End If
End Function

Much appreciate the help here.