Using Excel VB to import another .csv file into an Access DB

L

leeloo0505

Board Regular
Joined
Mar 28, 2003
Messages
130
Hello,
I am writing a macro in Excel that hopefully will allow a user to import a .csv from a certain location into an Access DB.. the .csv does not have anything to do with the Excel window I have open.
I have created the DB and the text file specification layout to be used when importing this 'third' .csv. And the code below works great within Access but when I try from within Excel it cannot find the "DowntimeToolImportLayout". I get the error 'the DowntimeToolImportLayout specification does not exist' when I know it does. I have adjusted my References but to avail.. here is the code.
docmd.transfertext acImportDelim, "DowntimeToolImportLayout", "tblDowntimeToolImport", "K:\fixed\data\downtime\cust_ord_post\20031008_cust_ord_post_2042_p1.csv", True, "", 28591

When I coded this in excel, it recognized it as VB does helping me with the agruements..so that is okay.. I just can't get it to find the text file specification name when it finds it fine when I am within Access.

Thanks,
leeloo
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Re: Using Excel VB to import another .csv file into an Acces

Hi mate,

I'm presuming that you've set a reference to the Access object library as you're using the Docmd property. Anyway, I think you'll have to load the database in question first because the import spec is stored in the database itself (in the MSysIMEXSpecs and MSysIMEXColumns system tables). This code worked OK for me...

Code: 
Sub Test()
    Dim acApp As Access.Application

    Set acApp = New Access.Application
        
    acApp.OpenCurrentDatabase "H:\temp\db1.mdb"

    acApp.DoCmd.TransferText acImportDelim, "MyImportSpec", "MyCSV", "H:\temp\mycsv.csv", True

    acApp.CloseCurrentDatabase
    acApp.Quit acQuitSaveNone

    Set acApp = Nothing

End Sub
 
Upvote 0
Re: Using Excel VB to import another .csv file into an Acces

Hey Dan,

Thanks for your time.. worked great. I was calling another procedure to open the DB which was working fine able to retrieve etc.. but seemed to like the
"acApp.OpenCurrentDatabase sPathDownTimeDB" ... I was using the below..
Public Sub OpenLargeOrderDowntimeDB()
Set Con = New Connection
Set Res = New Recordset

Con.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & sPathDownTimeDB
Con.Open
Exit Sub
End Sub


Thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Open CSV with VBA, convert to ANSI and a cell "75 %" is changed to format type percent and the value is changed to "0.75"
Replies
1
Views
212
Tanquen
T
R
  • Question
EXCEL VBA Import CSV large numbers as text
Replies
2
Views
468
jkpieterse
jkpieterse
M
  • Question
Memory resource issue when opening and closing excel files
Replies
0
Views
253
Mike UK
M
A
  • Question
Take screenshots & simultaneously capture the link, place it into an Excel file which is linked to a FileMaker/Claris db.
Replies
3
Views
277
Dan_W
D
Y
  • Question
Unwanted empty rows in csv file
2
Replies
11
Views
2K
Alex Blakenburg
A

Forum statistics

Threads
1,221,572
Messages
6,160,575
Members
451,656
Latest member
SBulinski1975

Share this page

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