Import specific fields from Access to Excel (VBA)

O

ossuary

Active Member
Joined
Sep 5, 2004
Messages
279
Hello,

I have working code to copy the matching records of an Access table into an Excel worksheet, but now I am trying to modify this code so that I can select a couple of specific fields from the table instead of just copying all of the fields (there are 15 fields in the table, but I only actually need 4 of them for this particular report).

Here is the code I am currently using to copy all of the fields over with the matching records:

Code: 
Const myDB = "DSD Errors DB tester.mdb"
Private Sub CommandButton4_Click()
  ' Test Field Select button
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim sSQL As String
    
    sSQL = "SELECT * FROM DSD_Invoice_Requests WHERE `Paid?` IS NULL"
    
    Range("A2:O65536").ClearContents
    Application.EnableEvents = False

  ' Create the database connection
    Set cnn = New ADODB.Connection
    myFile = ThisWorkbook.Path & "\" & myDB
    
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open myFile
    End With
    
  ' Create the recordset
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, ActiveConnection:=cnn, _
        CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText
    
  ' Transfer data to Excel
    Range("A2").CopyFromRecordset rst
    
  ' Close the connection and clean up references
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    
    Application.EnableEvents = True
    myRow = Range("A65536").End(xlUp).Row - 1
    MsgBox ("Finished loading " & myRow & " record(s)."), vbInformation, "Data Loaded"
End Sub

How can I modify this so that I put specific fields from the table into columns in Excel that will not line up with the layout of the table? For the sake of the example, let's say that I need fields 2, 4, 6, and 8 of the table to go into columns 1, 2, 3, and 4 in Excel.

Thanks in advance for any help with this. :)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Change your select statement to something like this to get only the fields you require.

Code: 
sSQL = "SELECT Field1, Field2, Field3, Field4 FROM DSD_Invoice_Requests WHERE `Paid?` IS NULL"
 
Upvote 0
Awesome, thanks for the quick responses, Andrew and nirvana. That was exactly what I needed for this report.

Out of curiosity, if for some reason my destination columns in Excel were not in the same order as the fields I am pulling from Access (say for example I needed to put the 4 fields from Access into columns 1, 2, 3, and 7 of Excel because there were formulas in columns 4-6 that I couldn't move), is there a way to do this? If the best solution is realistically "Don't do that," I can accept that! I'm just curious if there's a way to put the fields wherever you want on the destination page one at a time instead of copying and pasting it as a complete range of values in one go.
 
Upvote 0
I dont think there is a way to import data into non-adjacent rows. Unless there is some other way.
 
Upvote 0
You can write the data out cell by cell, which is much slower; you can use GetRows to get the data into an array and then split that into two arrays and write each to the sheet; or if you can guarantee that the records would be returned in the same order, you could open two recordsets and drop one into columns 1-3 and the other into column 7.
 
Upvote 0
Hi,

I was trying to use your code, because I was doing something very similiar.

when I copy the code to my workbook and run I get the following error on the top line:

Dim cnn As ADODB.Connection
Error = "Compile Error. User-defined type not defined"

Do you know what the problem is?

Thanks
 
Upvote 0
Thanks for the response, rorya.

Definitely something to think about. I'd be concerned about the data coming back in a different order though, like you said. Best bet would probably be to just avoid the situation altogether, but it's nice to know there are a few ways to attempt it if you needed to.
 
Upvote 0
Starbucks_33 said:
Hi,

I was trying to use your code, because I was doing something very similiar.

when I copy the code to my workbook and run I get the following error on the top line:

Dim cnn As ADODB.Connection
Error = "Compile Error. User-defined type not defined"

Do you know what the problem is?

Thanks
Click to expand...

You have to have a special reference turned on in your workbook, because the capabilities are not turned on by default. While viewing your workbook's code, go to Tools --> References and put a checkmark in Microsoft ActiveX Data Object 2.x Library, whichever is the highest number of 2.x you have (mine is 2.7 but yours may differ based on your version of Office). After you save that, any code referring to ADODB should work.
 
Upvote 0
I think you can do something like this. But the syntax is not exact, sorry dont have access to test it out.

Code: 
Sub InsertData()
    Dim j As Integer
    j = 1
    Do While Not rst.EOF
        Worksheets(1).Cells(j, 1) = rst!fields(1)
        Worksheets(1).Cells(j, 2) = rst!fields(2)
        Worksheets(1).Cells(j, 3) = rst!fields(4)
        Worksheets(1).Cells(j, 7) = rst!fields(4)
        j = j + 1
    Loop
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Import Table Data from Web using VBA to excel Worksheet and able to input Values which can modify the web data to be extract
Replies
0
Views
357
Ambrosia
A
D
  • Solved
Adodb Insert from excel range
Replies
7
Views
1K
drag1c
D
S
  • Solved
Accessing Sharepoint List from Excel/VBA using ADODB connection
Replies
3
Views
3K
RoryA
RoryA
J
  • Question
VBA - ADODB Update Multiple records (Any ways to speed this up?)
Replies
2
Views
6K
MoshiM
M
C
  • Question
Extracting Specific Fields from multiple PDFs with VBA (Excel 2019)
Replies
1
Views
235
chickyguy
C

Forum statistics

Threads
1,214,944
Messages
6,122,387
Members
449,080
Latest member
Armadillos

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