Import Data 1

[TheRiver]

Currently I have a procedure where by I have serveral text files or csv files stored in a folder on my drive. Each month I have to import these into existing tables within Access using import specs.

Does anyone know any ways of streamlining this procedure?

 

[TheRiver]

i think I will just have to repeat this code within the module three times replacing 5MG with 5MH and 5MJ

LookIn = "K:\WL CMDS (Current Financial Year)\Dudley Group of Hospitals NHS Trust\Inpatient Waiting List CMDS"
'Below File needs to be updated each month with the latest filename
.FileName = "08 IWL Nov (5M*).txt"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With

I dont want to do a search for the last modified file as I want all three files pulled across. Thanks anyway.

 

[p27br]

No !

Your code should work because your filename property has the asterisk wildcard, so the search will return the 3 files and then import them.
You should never repeat the same code in a program if you can get it all in a loop.
you can email me your database with no data in it to make it small and I'll code the function for you.
pauldewouters@mail.com

 

[mtownbound]

I'm trying to do pretty much the same thing, except on a daily basis. Let me clarify first, I have an Excel spreadsheet that I need to import into my database daily. I created and successfully did this with a macro, but I have to manually point it to the file everytime.

I would like to be able to check a folder/subfolders for any new files(All the files have the same name). I have each day's files in a different folder.

Is there any way in the macros tool to establish a variable??

Thanks!!!!!!!

 

[TheRiver]

Paul thanks for your help.

Your last suggestion of adding a command button on the form, will this result in me having to specify the file name that is being imported?

 

[p27br]

TheRiver,

I suggest you create a new thread for your excel import issue in the Microsoft Access VBA Coding forum. You will get an answer quicker that way. But anyway I suggest avoiding macros for this type of task. Take some time to learn more about VBA, you will see it is much richer than macros !

About your second question, I'm not sure what you mean. The file name can be specified in different ways. You could have a File Dialog box pop up when the button is clicked, or the file names could be typed in a text box as I first suggested or they could be stored in a table,...
All depends on the way you want the DB to work.


regards

Paul

 

[TheRiver]

I like the idea of file names being stored in a box which I can just select and run the module.