How do I get the transfer text macro to ask me for the path ?

[Cab Max Dave]

I am trying to create a macro to export a table as a csv file and i want the
macro to ask me for the file name when i run it instead of putting one in the
"file Name" field

 

[Ken Snell [MVP]]

If you just want to enter a filename, use InputBox to get it from user:
File Name: ="C:\MyPath\" & InputBox("Enter File Name:")


If you want to enter the full path and file name:
File Name: =InputBox("Enter Path and File Name:")


If you want to browse to the file, you can use an API function to open the
"File Open" dialog box. This page on The ACCESS Web has code for using the
API to do this. You can substitute the ahtCommonFileOpenSave function call
in the argument list in the TransferText action, similar to the examples
above:

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

 

[Cab Max Dave]

Thanks Ken that worked perfectly.

If you just want to enter a filename, use InputBox to get it from user:
File Name: ="C:\MyPath\" & InputBox("Enter File Name:")


If you want to enter the full path and file name:
File Name: =InputBox("Enter Path and File Name:")


If you want to browse to the file, you can use an API function to open the
"File Open" dialog box. This page on The ACCESS Web has code for using the
API to do this. You can substitute the ahtCommonFileOpenSave function call
in the argument list in the TransferText action, similar to the examples
above:

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

 

[AFSSkier]

How can you set Field Name to NO like in TransferSpreadsheet? I'm exporting
as CSV.

 

[Ken Snell \(MVP\)]

You could use an expression like this for the FieldNames argument:

=IIf(InputBox("Do you want field names? Enter Yes or No ...","Field
Names?")="Yes", True, False)
--

Ken Snell
<MS ACCESS MVP>

 

[AFSSkier]

I'm not sure you understand my question.

I created a macro to export a query as a csv file per your previous
directions.
File Name: ="C:\MyPath\" & InputBox("Enter File Name:")&".csv"

However, I would like to set Field Name to NO like in TransferSpreadsheet.
But I using OutputTo.

 

[Ken Snell \(MVP\)]

You're right... I'm not understanding your question < s>.

There is no Field Name argument for the OutputTo action in a macro. Tell us
what you want to achieve with the export. I was understanding that you
didn't want to put the Has Header Row information in the output file, and
that was what I was answering. But that option is available only with
TransferText -- not with OutputTo or with TransferSpreadsheet when doing an
export.

--

Ken Snell
<MS ACCESS MVP>

 

[AFSSkier]

I want to export a Query to CSV without the FieldName.

 

[Ken Snell \(MVP\)]

Then use the TransferText action, not the OutputTo action, and put the
expression that I provided in my first reply in the box next to the Has
Field Names argument for this action.

 

[AFSSkier]

Using TransferSpreadsheet exporting to Microsoft Excel 8-10, ="C:\CSVFILES\"
& InputBox("Enter File Name:") & ".csv". I'm getting the following Error
3027.
Also, is it possible to export from a query using TransferSpreadsheet?
__________________________________________________________________
Cannot update. Database or object is read-only. (Error 3027)
You tried to save changes in a database that was opened for read-only access.

The database is read-only for one of these reasons:

You used the OpenDatabase method and opened the database for read-only
access.
In Microsoft® Visual Basic®, you are using the Data control, and you set the
ReadOnly property to True.
The database file is defined as read-only in the operating system or by your
network.
The database file is stored on read-only media.
In a network environment, you do not have write privileges for the database
file.
When working with a secured database, the database or one of its objects
(such as a field or table) may be set to read-only. You may not have
permission to access this data with your user name and password.
Close the database, resolve the read-only condition, and then reopen the
file for read/write access.
____________________________________________________________________

 

[Ken Snell \(MVP\)]

Use TransferText to export to a file whose extension will be ".csv".

Use TransferSpreadsheet to export to a file whose extension will be ".xls".

The error you're getting is because you're trying to export to a "csv" file
using TransferSpreadsheet.

Note: You cannot "turn off" the header row export (of field names) in
TransferSpreadsheet. You can turn it off in TransferText.

 

[AFSSkier]

Thanks, TransferText works perfectly. Just one more question, what is the
Specification Name for?

 

[Ken Snell \(MVP\)]

You can create and save an Import Specification or an Export Specification
that defines the fields and the delimiter and other parameters of the
exported data. You do this by manually starting (File | Export) an export
and then click the Advanced button in the wizard window, set the parameters,
save them under a name, return to the wizard window, and then cancel the
export. Similarly, you can save an Import Specification by manually
beginning an import (File | Get External Data... | Import), and doing
similar steps.

 

[AFSSkier]

Thank you, you have been very helpful!
--

You can create and save an Import Specification or an Export Specification
that defines the fields and the delimiter and other parameters of the
exported data. You do this by manually starting (File | Export) an export
and then click the Advanced button in the wizard window, set the parameters,
save them under a name, return to the wizard window, and then cancel the
export. Similarly, you can save an Import Specification by manually
beginning an import (File | Get External Data... | Import), and doing
similar steps.

 

[BamaJack]

I have tried this method (Macro/Transfer Text) and my file name never prompts
for the user input but rather gives me a file name of C:\MyPath\
InputBox(enter date mmddyyyy) & .txt Any reason why the InputBox prompt
would be looked at as just part of the filename and not the function?

 

[Ken Snell \(MVP\)]

You probably are missing some " characters. Post the actual expression that
you have in your macro's FileName argument and let's see.

--

Ken Snell
<MS ACCESS MVP>

 

[BamaJack]

cut/paste from [file name] in the Access Macro(TransferText):
C:\jack\newfile & InputBox("EnterDate MMDDYYYY") & .txt
Yields the following file in c:\Jack\:
newfile & InputBox(_EnterDate MMDDYYYY_) & .txt

 

[Ken Snell \(MVP\)]

Use this in the argument box:

="C:\jack\newfile" & InputBox("EnterDate MMDDYYYY") & ".txt"

--

Ken Snell
<MS ACCESS MVP>

cut/paste from [file name] in the Access Macro(TransferText):
C:\jack\newfile & InputBox("EnterDate MMDDYYYY") & .txt
Yields the following file in c:\Jack\:
newfile & InputBox(_EnterDate MMDDYYYY_) & .txt

I have tried this method (Macro/Transfer Text) and my file name never
prompts
for the user input but rather gives me a file name of C:\MyPath\
InputBox(enter date mmddyyyy) & .txt Any reason why the InputBox prompt
would be looked at as just part of the filename and not the function?

 

[BamaJack]

Ken, Thanks. That did the trick. I didn't read the "=" to be included in
the filename. Once I added that to the macro it worked. I appreciate your
assistance.

Use this in the argument box:

="C:\jack\newfile" & InputBox("EnterDate MMDDYYYY") & ".txt"

--

Ken Snell
<MS ACCESS MVP>

cut/paste from [file name] in the Access Macro(TransferText):
C:\jack\newfile & InputBox("EnterDate MMDDYYYY") & .txt
Yields the following file in c:\Jack\:
newfile & InputBox(_EnterDate MMDDYYYY_) & .txt

I have tried this method (Macro/Transfer Text) and my file name never
prompts
for the user input but rather gives me a file name of C:\MyPath\
InputBox(enter date mmddyyyy) & .txt Any reason why the InputBox prompt
would be looked at as just part of the filename and not the function?

:

If you just want to enter a filename, use InputBox to get it from user:
File Name: ="C:\MyPath\" & InputBox("Enter File Name:")


If you want to enter the full path and file name:
File Name: =InputBox("Enter Path and File Name:")


If you want to browse to the file, you can use an API function to open
the
"File Open" dialog box. This page on The ACCESS Web has code for using
the
API to do this. You can substitute the ahtCommonFileOpenSave function
call
in the argument list in the TransferText action, similar to the
examples
above:

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

--

Ken Snell
<MS ACCESS MVP>



message
I am trying to create a macro to export a table as a csv file and i
want
the
macro to ask me for the file name when i run it instead of putting
one in
the
"file Name" field

 

[Rafi]

Use something like the following code wich will open the windows file dialog
for you. For test purposes, add a msgbox to show the file name

.......
.......

Dim dlgSaveAs as FileDialog
Dim strFileAs
Set dlgSaveAs = Application.FileDialog((msoFileDialogSaveAs)

With dlgSaveAs
If .Show = -1 Then
strFileAs = .SelectedItems(1)
' The user canceled
Else
MsgBox "Action Canceled"
Exit Sub
End If
End With

msgbox strFileAs

......
......