Here is an example for opening a file. This code does not actually open the
file, it only gets the file and path name. All the options involved are only
for the presentation of the dialog:
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_FILEMUSTEXIST
'Set filter to show only csv spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Text Files (*.csv)")
'Set the Default File Name
strFileName = "VoughtBillCurrentMonth.csv"
'Set the Default Path
strDefaultDir = "\\rsltx1-bm01\FTPData$"
'Call the Open File Dialog
Do While True
varGetFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
Filter:=strfilter, _
FileName:=strFileName, _
InitialDir:=strDefaultDir, _
Flags:=lngFlags, _
DialogTitle:="Import BillCurrMonth3345")
Me.Repaint 'I put this in because I was having display problem
If varGetFileName = "" Then 'User clicked Cancel
If MsgBox("Cancel Import?", vbQuestion + vbYesNo, "Import
BillCurrMonth3345") _
= vbYes Then
blnOkGo = False
Exit Do
End If
Else
blnOkGo = True
Exit Do
End If
Loop
In this case, I am getting the file name so I can link to it:
'Link to the csv file
DoCmd.TransferText acLinkDelim, "VoughtBillCurrentMonth Link
Specification", _
"VoughtBillCurrentMonth", varGetFileName, True
In this example, I am getting the name of a file I want to copy a recordset
to:
'Get filename to copy to
'Flags Hides the Read Only Check
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT
'Set filter to show only xls Spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Spreadsheet Files (*.xls)")
'Set the Default File Name
strFileName = "ImportErrors" & "_" & _
Forms!frmLoadMonth!cboPeriod.Column(1) & "_" & _
Forms!frmLoadMonth!txtCurrYear & ".csv"
'Set the Default Path
strDefaultDir = "\\rsltx1-bm01\busmgmt\Dev CISCMS\BillCurrMonthBackup\"
'Call the Open File Dialog
varGetSaveName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strfilter, _
FileName:=strFileName, _
InitialDir:=strDefaultDir, _
Flags:=lngFlags, _
DialogTitle:="Save Error File")
Me.Repaint
If varGetSaveName = "" Then
MsgBox "Save File Canceled", vbOKOnly, "Save Errors"
GoTo cmdSave_Click_Exit
End If
Now I have the file name in varGetSaveName, So I need to create the
spreadsheet and copy the data to it. Why not a TranferSpreadsheet, you ask?
Seems that in this case, because I have the table as the recordset of my
subform, I was getting "recordset is read only" error, so I got around it
this way. Why is would let me do this and not a TransferSpreadsheet, I don't
know.
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo cmdSave_Click_Error
DoEvents
xlApp.DisplayAlerts = False
'Create the Workbook
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
For lngCtr = 0 To rst.Fields.Count - 1
xlSheet.Cells(1, lngCtr + 1) = rst.Fields(lngCtr).Name
Next lngCtr
xlSheet.Cells(2, 1).CopyFromRecordset rst
xlBook.SaveAs FileName:=varGetSaveName
All the variables and objects need to be dimmed.
Your API code should be in a standard module by itself.
The code examples above go in the Click Event of the command button you will
use, one for Save and one for Open.
Not the Do While True Loops. That is there so the user can make mistakes.
If they decide not to open or save the file, then you will get a zero length
("") string returned, and bypass the code that uses the variable.
If you have any questions on this, please post back. I hope this answers
your questions.
[quoted text clipped - 13 lines]
