Automate - get External Data (1 Viewer)

[Hiten]

Is this possible.?

I import daily into my Ms Access DB, 2 Excel csv generated documents. I go to
file
get external data
import
select the file and import it into the relevant table.

This all works fine.

Is there are way to write a macro or a module ( which I have no idea how to do ) to automate this procedure?

If anyone can help this would be great.

Thanks

H

 

[macca the hacke]

Try code below.

I have set a public constant DownloadDrive which is the path to the drive where the csv files are saved.

I have then created a table in the database called 'usys_tbl_tablestobeimported' with 4 columns:- 'linkedtablename', 'TableName', 'linkspecs' and 'importedok'.
'linkedtablename' is the name of the csv file (including .csv)
'TableName' is the name of the table once imported into the database
'linkspecs' is the name of the linked table specs
'importedok' is a check box that verifies tables have been imported correctly.

*****code starts*****

Public Const DownloadDrive = "G:\Rob_Mc\Source Files\IMS_Downloads\"

Dim rs As DAO.Recordset
Dim strTableToBeImported, strTableName, strLinkSpecs As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Usys_tbl_tablestobeimported", dbOpenDynaset)

With rs

Do Until .EOF

rs.Edit
rs![importedok] = False
rs.Update
.MoveNext
Loop
End With

With rs
.MoveFirst
Do Until .EOF

strTableToBeImported = DownloadDrive & rs!linkedtablename
strTableName = rs!TableName
strLinkSpecs = rs!linkspecs

DoCmd.DeleteObject acTable, strTableName
DoCmd.TransferText acImportDelim, strLinkSpecs, strTableName, strTableToBeImported
'msgBox ("Imported " & strTableToBeImported & " to " & strTableName)
rs.Edit
rs![importedok] = True
rs.Update

.MoveNext
Loop
.Close

End With

set rs = nothing
set db = nothing

*****code ends*****

Hope this helps!

 

[Scolds]

You can use a Macro and call that from code
Creaye new macro
TransferSpreadsheet as Action
Then at the bottom table id table to import in to