VBA code from 32 bit to 64 bit - Ms Access

Question

I've built an application on a 64bit machine (windows 7) running 32bit Microsoft Office. The client machine is 64bit windows and 64bit office.

I initially had problems with comdlg32.dll but included the PtrSafe keyword. The next problem was a missing IPCONFIG.dll which I installed into the client machine.

I now have it compiling okay but I am trying to use the file save dialog (code by Ken Getz). It seems to be skipping over opening the actual dialog and runs straight to runtime error 2522 (file name required). Any help appreciated. This is the code I'm using (that refers back to Ken Getz's functions):

Function exportData_Click()


Dim strFilter As String
Dim strSaveFileName As String
Dim The_Year As Variant

Dim ctlCurrentControl As Control
Dim queryName As String



'Get the name of the control button clicked (corresponds to query name to be run)
Set ctlCurrentControl = Screen.ActiveControl
queryName = ctlCurrentControl.Name



'Get combobox value and assign relavent values to The_Year
The_Year = Forms![Extract Data]!Extract_Year.value


'Change the year from a variant to what we need in the SQL

If The_Year Like "20*" Then
    The_Year = CInt(The_Year)
    'MsgBox The_Year & "Data Type = " & VarType(The_Year)
Else: The_Year = "*"
'MsgBox The_Year & "Data Type = " & VarType(The_Year)
End If

'Set queryYear variable
setYear (The_Year)


'Check the variable is correct
'MsgBox getYear()

'Open the Save as Dialog to choose location of query save

strFilter = ahtAddFilterItem("Excel Files (*.xlsx)", "*.xlsx")

strSaveFileName = ahtCommonFileOpenSave( _
                                openFile:=False, _
                                Filter:=strFilter, _
                Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, queryName, strSaveFileName

End Function

Debugging points to this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, queryName, strSaveFileName

Answer 1

I found that this code will work with MS Access 64:

Function GetFile(strStartIn As String, strFilter As String) As String
Dim f As Object

   Set f = Application.FileDialog(3)
   f.AllowMultiSelect = False
   f.InitialFileName = strStartIn & strFilter
   f.Show

   GetFile = f.SelectedItems(1)
End Function

It is not as neat as Ken Getz' code, but should be suitable to choose a file to save or open. You can reference the Microsoft Office library to use built-in constants such as :

msoFileDialogOpen (1) 
msoFileDialogSaveAs (2)
msoFileDialogFilePicker (3) 
msoFileDialogFolderPicker (4) 

( http://msdn.microsoft.com/en-us/library/office/aa190813(v=office.10).aspx#ofobjFileDialogFilters )