File Type 2

[MadMax7]

I was hoping someone could help me out with the following

I have a report expot automatically everynight from one of our packages we use, i know want to set up a process so access will automatically collect the file and paste it into the desired table , when i go into the file properties and into the type of file it is called 001 File

Does anyone know what kind of file this is and if so how i can get access to collect the file

 

[KenReay]

Hi

It simply means it has an extension of .001 and that extension is not associated with any application in your windows installation

As to getting the file into Access, you need to know more about the file, is it text, is it comma seperated (csv), it it fixed width etc etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MadMax7]

KenReay

The file is a CSV file althougth the file name is not ended with .csv so when i try and call the file into access, access error's out saying it can't find the file

 

[KenReay]

Hi

How are you trying to import the file?, using File\getexternaldata\import of in code using docmd.transfertext ?

Why not just specify the full file specification ie including the .001

If you are using A2000 (and perhaps 2002, 2003), there is a new 'feature' whereby you can only import files with declared extensions, otherwise you get a spurious error telling you the database is readonly.

Why not simply rename the file, get the import to work, then decide how to handle to the problem of the extension

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MadMax7]

Sorry i think i am being thick but this is really starting to bug me now.

I am currently trying to bring in the file using the TransferText Macro, when i try and call in the data using the full path it gives me the following error

'You cannot import a text file unless it has one of these extensions : TXT,CSV,TAB,ASC,HTM,HTML

i have changed the filename to add .csv to the end of the file name and this then brings in the file.

the problem i have is i want to automate the collection of the data to run overnight so there won't be anyone around to manually change the name of the file

 

[KenReay]

Hi

"'You cannot import a text file unless it has one of these extensions : TXT,CSV,TAB,ASC,HTM,HTML", this is the macro version of what I said in earlier post

Forget macros ther are yesterdays technology, use VBA, the equivalent VBA command is docmd.transfertext

You can automate the renaming too (in VBA)

NAME strFilename AS strFilename & ".csv"

You can automate searching the directory for the file using DIR()





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MadMax7]

KenReay

thanks for your help

just one more thing i want to be able to delete the file so it will name it the same the next night, what would be the best way to do this

 

[KenReay]

Hi

in VBA

Kill strFileName

or

Kill "MyFile.001"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MadMax7]

Sorry to bother you again but i am using teh following code to import the file data

DoCmd.TransferText acExportDelim, , "tblCalldataTest", "H:\Retail Network\Private\GMS Call Centre\OPSCEN\Meridian Overnight\Acc.001", NO

but it is given a run time error saying

Cannot Update . Database or object is read only

 

[KenReay]

Hi

Yes....

from my first post

"If you are using A2000 (and perhaps 2002, 2003), there is a new 'feature' whereby you can only import files with declared extensions, otherwise you get a spurious error telling you the database is readonly.

Why not simply rename the file, get the import to work, then decide how to handle to the problem of the extension "

you are using an extension of .001, which you attept with a macro told you was not in the accepted list

try (say)

DoCmd.TransferText acExportDelim, , "tblCalldataTest", "H:\Retail Network\Private\GMS Call Centre\OPSCEN\Meridian Overnight\Acc.csv", NO

having first renamed the file (but see below)

also "acExportDelim", this line of code is exporting, not importing?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[psemianonymous]

A star for your extended patience.


Pete

 

[workworkwork]

I have a similar issue. I have a macro that will import an excel file - no problem. What I would like to do is browse for the file instead of providing the full file path. Even an input box prompting for the file name would be better than typing in the whole file name. Can I do this in a macro? I know, I should be using VB, but I don't havethe time to invest in it, I need it quick and dirty.

 

[workworkwork]

OK, I got the import working fine with an imputbox,

filename = "C:\inventory\ScannerFiles\ScannerFiles\june_23\"
filename = filename + InputBox("Please Enter File Name:")
DoCmd.TransferText acImportDelim, "scanner_import", "scanner_in", filename


now how do I make a browser...

Does anyone read these posts???

 

[3plJAB]

workworkwork, are you developing some sort of data collection system for radio frequency? (e.g. "scanner")

AFCJAB