Export Query as Fixed Text but prompt for location

[starke1120]

I have this and it seems to work,but I don't want to hard code the
location. Instead I want to launch a file dialog browser and ask the
user for a location and a name to save the txt ... so far I have
this...

DoCmd.TransferText acExportFixed, "ExportSpec", strOutput,
"c:\test.txt", True

and it works, but of course it saves to the c:\ drive and I want it to
prompt...

Thanks,

Dave

 

[Douglas J. Steele]

Grab the code from http://www.mvps.org/access/api/api0001.htm at "The Access
Web" to get the best file dialog browser.

Use something like:

Dim strFilter As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(myFilter, "Text Files (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter)

If Len(strSaveFileName) > 0 Then
If Len(Dir(strSaveFileName)) > 0 Then
If MsgBox(strSaveFileName & " already exists." & vbCrLf & _
"Do you want to delete it?", vbYesNo + vbQuestion) = vbYes Then
Kill strSaveFileName
End If
DoCmd.TransferText acExportFixed, "ExportSpec", strOutput, _
strSaveFileName, True
End If
End If

 

[starke1120]

Thanks for the prompt reply...

I dont know what to do with this part of the code though...
Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000


Thanks ...

Dave

 

[Douglas J. Steele]

All of the shaded code (what's between Code Start and Code End) should be
copied from the web page and pasted into a new module (not a class module,
nor a module associated with a form or report). Make sure you do not name
the module the same as any of the functions or subs within in: module names
must be unique.

 

[chriske911]

(e-mail address removed) used his keyboard to write :

I have this and it seems to work,but I don't want to hard code the
location. Instead I want to launch a file dialog browser and ask the
user for a location and a name to save the txt ... so far I have
this...

DoCmd.TransferText acExportFixed, "ExportSpec", strOutput,
"c:\test.txt", True

and it works, but of course it saves to the c:\ drive and I want it to
prompt...

Dave

if you do not specify a location at the end it defaults to a user's
home drive or the My documents folder without prompting
isn't that an option?
otherwise the code from douglas will work just fine

grtz

 

[Douglas J. Steele]

(e-mail address removed) used his keyboard to write :



if you do not specify a location at the end it defaults to a user's home
drive or the My documents folder without prompting
isn't that an option?
otherwise the code from douglas will work just fine

If by "not specify a location" you mean having a file name but no path,
it'll be placed in the current directory. Since you cannot be sure of where
the current directory is, that's seldom a good idea, unless you explicitly
set the current directory using ChDrive and ChDir. (Note: the current
directory is what the CurDir function returns. It's not necessarily where
the MDB exists, nor where the default database folder, as set on the General
tab through Tools | Options)

 

[chriske911]

Douglas J. Steele has brought this to us :

If by "not specify a location" you mean having a file name but no path, it'll
be placed in the current directory. Since you cannot be sure of where the
current directory is, that's seldom a good idea, unless you explicitly set
the current directory using ChDrive and ChDir. (Note: the current directory
is what the CurDir function returns. It's not necessarily where the MDB
exists, nor where the default database folder, as set on the General tab
through Tools | Options)

well, that's what I found out myself when I used the
docmd.transferspreadsheet function
if I don't specify the full path the file is always ending up in my
documents folder
don't know if that is by chance or by design

grtz