Import CSV then delete/remove file

andrewhoddie

Board Regular
Joined
Dec 21, 2008
Messages
114
Hi

I have an access database that I would like to update automatically but I am having issues, the debugger comes back to the table line as the issue, does anyone know why it is failing?

Also is there a way I can then delete the imported csv file or move it into a sub folder e.g. imported?

I would then like to run the macro automatically using task scheduler which I should be able to do.

thanks in advance
Andrew

Option Compare Database

Option Explicit

Public Function Import_data_files()

Dim report_path As String, file_name As String

report_path = "\\mycomputer\CVS Files\"

file_name = Dir(report_path & "*.csv", vbDirectory)

Do While file_name <> vbNullString

DoCmd.TransferText acImportDelim, , "dbo_COProperties", , True
file_name = Dir
Loop


End Function
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi @Joe4

thanks very much for your response, I have changed it to

DoCmd.TransferText acImportDelim, , "dbo_COProperties", "*.csv", True

and also tried

DoCmd.TransferText acImportDelim, , "dbo_COProperties", "\\my computer\CVS Files\*.csv", True

This is still erroring at the same point. We will be getting lots of files with different names so I cant specify a file name

thanks
Andrew
 
Upvote 0
You cannot use wildcards in there!
You are actually looping through the file names, so that file name variable is what you want to reference, but you also need to include the path, i.e.
Rich (BB code):
DoCmd.TransferText acImportDelim, , "dbo_COProperties", report_path & file_name, True

See here for more details: How to Import All Files within a Folder to Your Access Table
 
Upvote 0
Solution
Hi @Joe4
You cannot use wildcards in there!
You are actually looping through the file names, so that file name variable is what you want to reference, but you also need to include the path, i.e.
Rich (BB code):
DoCmd.TransferText acImportDelim, , "dbo_COProperties", report_path & file_name, True

See here for more details: How to Import All Files within a Folder to Your Access Table

thank you very much I have now got it working, I have also had to add in an Import specification as my table names to not exactly match some of the headings in the CSV and i have also added in a way to move the file. I have copied the code below in case anyone else comes across this

thanks
Andrew

Public Function Import_data_files()

Dim report_path As String, file_name As String
Dim FSO As Object
Dim FolderNameMove As String
Dim FileList As String

report_path = "\\mycomputer\CVS Files\"
FolderNameMove = "\\mycomputer\CVS Files\old\"

file_name = Dir(report_path & "*.csv", vbDirectory)

Do While file_name <> vbNullString

DoCmd.TransferText acImportDelim, "Import CO CSV Specification", "dbo_COProperties", report_path & file_name, True
file_name = Dir
Loop

'Move files after import
FileList = Dir(report_path & "*.csv")
While (Len(Trim$(FileList)) > 0)
If (Len(Trim$(FileList))) > 0 Then
Set FSO = CreateObject("scripting.filesystemobject")
FSO.MoveFile Source:=report_path & FileList, Destination:=FolderNameMove
End If
FileList = Dir
Wend
End Function
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top