Change the extension on a .csv file created in Access VBA....

Here's my issue, I'm creating a .csv text file using DocmdTransferText, problem is my target application needs it's csv files to have the extension .FAC and it's very fussy about that. I've tried altering the target output file to a .FAC one but get a 'Object is read only or cannot be written to' error, or exporting with the extension .FAC, but access doesn't seem to support a .FAC extension when using TransferText.

So I thought, why not add some code to the end of the sub that says once my .csv file has been created in C:\\My Dcouments, then select that file, say test.csv and rename it as test.FAC

Does anybody have an access vba code snippet that can do this

Sponsored Links:

Related Topics

Test File Extension
Just joined and looking forward to hearing from everyone.!
Here's my first Access VBA question:

I have three types of files under certain directories ie. *.log, *.csv and *.txt
Hopefully, while inside an If statement, I'd like to test the extension of the files it has encountered.Then, after the test is satisfied, I would like the code to exit the if statement and go to another public function to import the file extension it has found.
I'm just learning vba, so


Import a file with no extension
I am trying to write a macro that will import a file. The file is created from a mainframe export and the file does not have a file extension. I am using Access 2007 and it will not let me import from a file that does not have one of the standard extensions and gives an error of such when I try. I am trying to create a process that does not require manually having to change the file in any way prior to importing into my Access table. Is there a work-around that will allow me to import this file on a monthly basis without having to manually add a file extension to the file first


Save csv file as excel
I need to import data from csv files into Access.

The problem that I have is that the format of the cvs file can change. Some have 5 columns od data, some have 6. Also the contents of the first row change!

I can handle the changes in the data if the data is available in an Excel spreadsheet.

How can I save a .csv file as an Excel file using vba (DAO)?

Or do I need to open the .csv file and import the data row for row? If yes,


Executing .BAT file from Access
I have a database setup with a macro that imports a Dir.txt file that is originally created by a Dir.bat file that I created in a folder. I basically click on this .bat file that creates a directory of all files in that specific folder with a .tif extension and actually removes that extension once the dir.txt file is created.

I basically want to have Access run the file automatically instead of having someone remember to run it first. Another problem is that Access needs to wait until the .bat file is finished running. How can I accomplish this? I really don't have that much experience with VBA but I'm sure with some help from all of you I can accomplish this.


vba to retrieve data from csv file to auto-populate
Do you know of any vba I can use that once I click on a button (vba coded) can retrieve data from a csv file and autopopulate in a table I was wondering if it was possible to create a complex button using the file \\Cluster2\Dept Data\Migration Data\NOMSALES.csv as a source and from that create a csv file that displayed sample data from the file [LINK] Data\MigrationData\NOMSALES Output.csv.

In other words instead of the user going through a directory to open a file I would like the user to click on a button within an access form and the data within the later file will automatically populate in a table. Can this be done?


Hyperlinks
Is it possible to follow a hyperlink to a file without using a file extension? I have links being created based on the name of a file, but because I haven't used a file extension it crashes. I know I can give the user a choice of what the file extension is and add it to the hyperlink, but it's an extra step, I'm potentially using 3 different file types, Word, Excel and PDF's.

The hyperlink works if I just reference a drive/folder, or if I add the file extension.


Importing a CSV file with VBA
I'm importing a standard CSV file into access using the following VBA:

DoCmd.TransferText acImportDelim (file specs/location follow)

For some reason, access is adding a .00 at the end of a text field (it's a text field but the data is a number). Does anyone know why this is happening? I've been using this same VBA every day with no issue until today. The field/column is formatted as text once it's in the table in access but now with the .00 at the end of every record. For example, the CSV file shows the field value as 011163, and once imported it shows


How to import or export CSV file to and from Access?
I wanted to import and then export CSV file to and from access but couldn't know how can I do that directly.

Is there a way to do that?

do I need all the time to have a "Broker" extension to accomplish this?


export as csv file
I have a file I created in Access. I export it as a text file. When I load open it as a csv file it looks fine but when I save it and reopen, it has changed some of my data. (Drop zeros,displays field in scientific format) How can I convert a text to csv without changing my format?

(I have never had so many problems in one database in my life)


Updating tables from CSV file?
I have a client which gets emailed a CSV file containing the daily invoice updates. Currently data entry is all manual ie the employee responsible will open the CSV file and then copy/re-enter the information in an Excel table.

I am planning to automate all this. My question is if I create a table consisting of the same structure as the CSV file, is it possible to create a simple command/macro in Access to automatically read a CSV file and update as needed?


Automatically export Access report
I have created a report in my Access 2003 database, which pulls through a few columns from a Query. However I need to output this to a .txt or .csv document. If I right click my report and press Export and save as a .csv etc, I get exactly what I want.

However I would like to know if there is a way this can be done Automatically, either by VBA or a bat script.

Basically at 08:00am every morning I want to export my data the report onto a text or csv file.

From there I will be running into third party software to format and e-mail it to the desired people.


Date format problem when exporting to csv file
I have created a macro to export the contents of a query as a csv file, but when I open the csv file, the date is showing the date and time. I need it to only show the date as this csv fileis uploaded to a parcel carrier's system that will only accept this field as dd/mm/y.

The date is created in an access table as a default value in the cell. The format is set to "Short date", the input mask to "00/00/000" and the default value to "Date()".
The query field is also set the same. The date format appears correctly in both the table and the query, but when it is exported it somehow changes to date / time format.

When I checked the cell format in the csv file it shows as a custom format "dd/mm/y hh:mm"
This file is overwritten every time I run the macro and in fact when I ran it for the first time, it created the csv file so it can't be due to a preformatted file problem.


Change the default file format
When you create a new, blank database in Microsoft Office Access 2007, you are prompted to provide a name for the database file. By default, the file is given the ".accdb" extension - it is created in the Office Access 2007 format and is not readable by earlier versions of Access.
In some cases, you may prefer to create files in an earlier Access format. In Office Access 2007, you have the option of creating files in either the Access 2000 format or the Access 2002-2003 format (both with the extension ".mdb"). Then, when you create a new database file, the resulting file is created in the earlier Access format and can be shared with others who use that version of Access. This can be helpful if you have upgraded to Office Access 2007 but you share files with others who have not.


link tables using vba
I have an n number of csv files in a folder that I want to link automatically to an access 2007 database. They are all named in the format file_n.csv, and the value n will change from use to use.

So we have files:
c:\folder\file_1.csv
c:\folder\file_2.csv
c:\folder\file_3.csv
-
-
c:\folder\file_n.csv

Does anyone know what code I need to link of these files as tables?


link tables using vba
I have an n number of csv files in a folder that I want to link automatically to an access 2007 database. They are all named in the format file_n.csv, and the value n will change from use to use.

So we have files:
c:\folder\file_1.csv
c:\folder\file_2.csv
c:\folder\file_3.csv
c:\folder\file_n.csv

Does anyone know what code I need to link all of these files as tables


How to import a .dta file into Access 2010?
We have hundreds of .dta files (data files in text format) that we would like to import into Access. If we first change the file extension to .txt, Access can import it just fine. However, we do not want to change the file extension on these.

Is there a way to let Access realize that these .dta files are .txt files, and have it treat them as such


Importing .CSV file to excel
I am importing .csv files to access. When importing is there a way to have the import add the date the .csv was created or prompt me for a date to enter?


Convert Password Protected Access MDB File To CSV Format
Export Microsoft Access MDB to CSV is a freeware app utility which allows database users to instantly convert MDB database file in CSV (Comma Separated Value) file. As database tool are not abound, using this app would be extremely useful to export database tables into CSV format which is widely used and can be viewed with a simple text editor. Apart from mere exporting MDB file, it also lets user convert ‘password protected’ MDB file in CSV, CSV separated with tab, and CSV text qualifier format along with revealing the password.


CSV Export
I have a database that is exporting a CSV. I would like to be able to add the date into the file name of hte CSV. Here is an example of the file name

Export-03012011-Companyname.csv

The date can actually be in any format obviously other than 00/00/000 becuase of the fact that it is a file and the slashes would through that way off


Verify CSV Field Names Before Importing
Before importing from a CSV, I'm trying to verify:

1) How many fields are present before importing a csv file
2) If the number of fields is correct, are the field names correct as well

The CSV file uses tab as the delimiter with no text qualifier with field names in the first row. If the CSV is verified as correct, the append query is run, transferring the data from the CSV that is a linked table.

[CODE]

There are no errors, it just doesn't get past the first If statement. UBound always returns 0.

I've attached a zip of the database (Access 2007) and a sample csv file. The database looks for the linked file in C:\company\