How to import excel files into access tables automatically! 1

[tekvb1977]

I have a folder on my C:\ drive that has bunch of excel spread sheets. I want to move through each of the spreadsheets and see if a certain portion of the name of spreasheet matches with my string criteria. If it macthes, I like to export that file into access table automatically and then I like to delete that file from the folder right after it is imported into the access table.

There are two challenges here for me:

1) How to move through each of the excel files in the folder using VBA and doing the string match.

2) I know Docmd.Transfer spreadsheet could do the import thing but the problem is I don't know the exact file name in the folder. Those excel files are dumped from outlook into the folder and have time stamp on it to avoid overwriting of the files of the same name.

For example ..... my file name could be 20060405_084332_ImpData

The first part is the time stamp which will vary for the same file depending upon when it was received by outlook.

Any ideas/thoughts how to overcome these two challenges would be really appreciated.

I am stuck here.

Thanks,

J

 

[snyperx3]

You can do a search in that folder for your string using

lets say you were looking for the date 20060405 in the folder c:\exported\
(this is just to show an example)

Dim strFolderName As String
strFolderName = Dir$("c:\exported\20060405*")

that gets all the files in the folder like that. Now we can cycle through them:

Do Until strFolderName = ""
        [green]'import[/green]
        DoCmd.TransferText [green]'your stuff here[/green]
        [green]'get the next file[/green]
        strFolderName = Dir$
Loop

let me know if this helps.

-Pete

 

[tekvb1977]

Pete,

Thanks a lot for your quick reponse.

In your code below:

****** Do Until strFolderName = ""
'import
DoCmd.TransferText 'your stuff here
'get the next file
strFolderName = Dir$
Loop ******

How can I kill the file after I import it. What's the VBA command for that. How can I refer to the full name of the file.

For example if file name is 20060405_Itm_Imp
The code will pick this file based upon the string match of 20060405. But how could I refer to (get to know) full name of the file when I have to use Docmd.Transfertext

Thanks once again for your great help.

J

 

[snyperx3]

strFolderName will refer directly to the filename...so youll have to add the directory

so you could use

docmd.TransferText acImportDelim,,tblName,"c:\exported\" & strFolderName

-Pete

 

[snyperx3]

im sorry...and killing the file is pretty easy =]

Kill "filename"

-Pete

 

[tekvb1977]

Thanks. Yes it is now referring to the right file name. How can I delete the file using VBA after it gets imported to access.

Kill FileName command doesn't seem to work.

J

 

[tekvb1977]

I was using the worng path. Kill command would work. How ever, the command below

DoCmd.TransferSpreadsheet acImport, "DummyTable", "G:\VAPA\VAPAMailFolder\" & strFolderName


doesn't work. It gives me the following error:


"External Table is not in the expected format"

DummyTable has the same field names as the column names in Excel. Not sure why I am getting this error.

Thanks,

J

 

[snyperx3]

youre missing a comma.

DoCmd.TransferSpreadsheet acImport, "DummyTable", "G:\VAPA\VAPAMailFolder\" & strFolderName

should be

DoCmd.TransferSpreadsheet acImport,, "DummyTable", "G:\VAPA\VAPAMailFolder\" & strFolderName

-Pete

 

[tekvb1977]

Thanks a bunch and sorry for bothering you over and over again. Comma has taken care of the above error.

 

[snyperx3]

no problem at all. =]

i enjoy this.

-Pete