user interface for export data to csv 2

[Pampers]

Hi everyone,
I have button on my form that exports the content of a query to a file. There are two things I want to alter:
1. I want the users to be able to use MS Explorer to save the file - instead of the input messagebox (see code below)
2. I want the output to be csv
but I don't know how...

Private Sub Command47_Click()
Dim Message, Title, Default, MyValue
Message = "Enter a FileName"    ' Set prompt.
Title = "EDI Messagebox"    ' Set title.
Default = "C:\Documents and Settings\alaerds\My Documents\FileName.txt"    ' Set default.
'Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

DoCmd.TransferText acExportDelim, , _
    "qryContainerMovesEDI", MyValue

Pampers
You never too young to learn

 

[spizotfl]

check out this site:

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

it uses the windows api to do file stuff. works a charm, even though i can proudly say i don't understand it all.
i am not sure on the transfer text, i use it for fixed width... but i'll see if i can find anything.

 

[Remou]

acExportDelim, which you have, should give a CSV file. Is it not working out for you?

A search for File Dialog may give you options in addition to the above, depending on your version of Access.

 

[stix4t2]

To export to CSV, an export spec must be established. I do this by selecting on the query, right mouse click and select export. You have to use the advanced button to first create the spec and then to save it with a name. Then in your transfertext command be sure to add the spec name to get the desired results.

 

[Remou]

stix4t2
I find that in Access 2000, I do not need an export specifcation to create a CSV, acExportDelim works. Perhaps this is a version thing?

 

[Pampers]

Tnx all for the help.
Remou: it works, but I dont want that the exported textfields are enclosed with "" - is that possible?

spizotfl: I will put the code, and see how it works. I'll be back with the result

Pampers
You never too young to learn

 

[Remou]

In that case you _do_ need an export specification, as stix4t2 has suggested. Quotes ("") are the standard for CSV, but you can do non-standard exports with a specification.

 

[Remou]

... But be careful of text fields that contain commas, because it will throw your export file out: 21, The Street will become two fields. That is why text is usually quoted.

 

[Pampers]

Hi spizotfl and others,
I went to Lebans instead, and imported his module as instead pasting the code. (Lebans code is corrected for A2K or higher, at:

http://www.lebans.com/callbackbrowser.htm).

Works like charm.

And by setting the following line of code to 'false' in the on-click() of the button, you get a Save File Dialog Box instead of the Open File Dialog Box.

Filter:=strFilter, OpenFile:=False,

Remou and Stix4t2: i will have a closer look on how to create and use an 'export spec'. Keep you posted.


Pampers
You never too young to learn

 

[Pampers]

Wow,
I created an export spec, saved it, used in the Transfertext method and... it works great. I could create the output just the way I wanted - or rather: the way the receiving company wants it (delimited by a space, no quotes). Big smile here. Tnx a lot Remou and stix4t2. Have a good weekend; here on our island, the crazy carnaval just started, so... I'll drink to you guys.

Pampers
You never too young to learn

 

[Pampers]

Hi guys,
Back again on this issue. All is ok, except for one small thing. If I give an export-file name a file extension other than csv, txt (and tab, asc), it gives the error message "Run time error 3027. Cannot update. Database or object is read only."

Is there a way around this so I can give for example the export file a .dat file extension? (Of course I can allways change the file extension afterwards, but I rather have it in one go ;-)



Pampers
There is only one way to change a diaper - fast

 

[Remou]

This:
Importing text files with non-standard file extensions.
thread181-1203735
Is to do with import, but may be relevant to export, I haven't tried the change to the registry.

 

[Pampers]

Hi Remou,
Sorry, I missed your last post. Excellent reference! Changing the registry, by adding the desired extension (aka .dat), makes it possible to save (and import) files with a .dat-extensions. My registry for DisabledExtensions now holds:

!txt,csv,tab,asc,tmp,htm,html,dat



Pampers
There is only one way to change a diaper - fast