External Data Source/Query - VBA problem

M

mjmact

New Member
Joined
Jan 22, 2007
Messages
36
Hello All. I have been having a problem trying to figure something out. I am importing some data from access (from a query) using data>Import External Data>Import Data. I am trying to figure out how to have it import the data and not keep the query definition using VBA. I can get it to import the data, but haven't been able to figure out the part about not saving the query definition (one of the options in the query properties in excel. Is there a way to have VBA "turn off" the "save query definition", or is there perhaps a better way to import the data. I would just manually turn it off when I import the data, but I am building this for other people, and I cannot 100% trust them to be able to do it on their own, so I am trying to "automate" everything that I can.

Thanks for any help
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hi,

Another (better?) way is to use ADO instead of a query table. The ADO returns a recordset - within VBA - and if the data is wanted on a worksheet, it can be written to a range. So, the user sees purely the data; they don't have a query table at all, just the data.

If you want to stick with a query table approach, suggest having VBA add a temporary worksheet, then the query, after refreshing the query copy - pastespecial values only the returned data from this temporary worksheet to a different worksheet. Then delete the temporary worksheet and the query. This is an inferior approach, IMO, and I would recommend the ADO approach. There are some good examples on line if you google.

HTH, Fazza
 
Upvote 0
Thanks for that. I am looking into ADO now, and I am still toying around with a couple of examples, trying to make them work. While searching for examples I have read that you have to have the activex object library turned on. If that is correct, is it possible to execute code to automatically do that when the user opens to workbook the first time? I have never affected VBE or the VBA code using VBA code before, so wouldn't know where to start.
Also, does anyone know of any good examples/help for ADO? I have found some, but am having trouble getting it to work.

Thanks for any help you may have
 
Upvote 0
Ok, apparently I was doing something stupid. I can now get the ADO to work (and like you said, it does seem to be a better way to do it; thanks for that)
Anyway. I am still trying to figure out how to "activate" the activex object library by using code. Any thoughts anyone?
 
Upvote 0
Good question!

I encountered the same question and went with late binding - the altenative approach when the reference is not set.

Again, google for examples. I just opened a recent example I had. With almost no modification, it is below.

regards, Fazza
Code: 
    Dim objConnection As Object
    Dim objRecordSet As Object
    Dim sSQL As String
            
    sSQL = Join$(Array("SELECT *", "FROM tbl", "WHERE criteria"), vbCr)
            
    Set objConnection = CreateObject("ADODB.Connection")
    
    objConnection.Open Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
        ThisWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
    
    Set objRecordSet = CreateObject("ADODB.Recordset")
    
    With objRecordSet
        .Source = sSQL
        Set .ActiveConnection = objConnection
        .Open , , 3, 2
        DoEvents
        'load recordset to destination worksheet
        'from row 2 as recordsets do not include headers
        wksData.Range("A2").CopyFromRecordset objRecordSet
        DoEvents
        .Close
    End With
    
    objConnection.Close
    
    Set objRecordSet = Nothing
    Set objConnection = Nothing
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
163
MichaelMorris
M
D
  • Question
Newbie to data connections...
Replies
1
Views
160
Kerryx
K
S
  • Question
VBA import of an IQY file without prompt
Replies
4
Views
346
John_w
J
M
  • Solved
Converting numbers stored as text to regular numbers query (VBA)
Replies
4
Views
411
Alex Blakenburg
A
R
  • Question
Trying to import IQY file into current workbook.
Replies
0
Views
207
rjtaylor
R

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

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