ADO - Access to Excel

Trying to use ADO to move some data to Excel
Excel shows an error when:
Dim Connection As ADODB.Connection is executed.

This is the program:

Sub AccessToExcel()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Cells.Clear
' Database information
DBFullName = "E:\db.accdb"
' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
'Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset

Filter
Src = "SELECT * FROM DB1 WHERE DB2 = 'Starting'"
Src = Src & "and Year = '2001'"

..Open Source:=Src, ActiveConnection:=Connection
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range(“A1”).Offset(0, Col).Value = _
Recordset.Fields(Col).Name
Next
'Write the recordset
Range(“A1”).Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
0
Utf
3/13/2010 3:31:01 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
1258 Views

Similar Articles

[PageSpeed] 34

hi
connection may be a reserved word like string.
set string as string????
try this
Dim Con as ADODB Connection   'no dot
also
dim rs as ADODB Recordset        'again no dot
also 
Dim col as long                                
                          
edit the rest of your code to reflect above.
everything else "looked" ok BUT i didn't not test.

Regards
FSt1

regards
FSt1


"Philosophaie" wrote:

> Trying to use ADO to move some data to Excel
> Excel shows an error when:
> Dim Connection As ADODB.Connection is executed.
> 
> This is the program:
> 
> Sub AccessToExcel()
> Dim DBFullName As String
> Dim Cnct As String, Src As String
> Dim Connection As ADODB.Connection
> Dim Recordset As ADODB.Recordset
> Dim Col As Integer
> Cells.Clear
> ' Database information
> DBFullName = "E:\db.accdb"
> ' Open the connection
> Set Connection = New ADODB.Connection
> Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
> Cnct = Cnct & "Data Source=" & DBFullName & ";"
> Connection.Open ConnectionString:=Cnct
> 'Create RecordSet
> Set Recordset = New ADODB.Recordset
> With Recordset
> 
> Filter
> Src = "SELECT * FROM DB1 WHERE DB2 = 'Starting'"
> Src = Src & "and Year = '2001'"
> 
> .Open Source:=Src, ActiveConnection:=Connection
> ' Write the field names
> For Col = 0 To Recordset.Fields.Count - 1
> Range(“A1”).Offset(0, Col).Value = _
> Recordset.Fields(Col).Name
> Next
> 'Write the recordset
> Range(“A1”).Offset(1, 0).CopyFromRecordset Recordset
> End With
> Set Recordset = Nothing
> Connection.Close
> Set Connection = Nothing
> End Sub
0
Utf
3/13/2010 6:13:01 AM
Have you set a reference to the Microsoft ActiveX Data Objects Library?

-- 

HTH

Bob

"Philosophaie" <Philosophaie@discussions.microsoft.com> wrote in message 
news:9B33D52F-C520-4976-B1BE-FC69310E40C9@microsoft.com...
> Trying to use ADO to move some data to Excel
> Excel shows an error when:
> Dim Connection As ADODB.Connection is executed.
>
> This is the program:
>
> Sub AccessToExcel()
> Dim DBFullName As String
> Dim Cnct As String, Src As String
> Dim Connection As ADODB.Connection
> Dim Recordset As ADODB.Recordset
> Dim Col As Integer
> Cells.Clear
> ' Database information
> DBFullName = "E:\db.accdb"
> ' Open the connection
> Set Connection = New ADODB.Connection
> Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
> Cnct = Cnct & "Data Source=" & DBFullName & ";"
> Connection.Open ConnectionString:=Cnct
> 'Create RecordSet
> Set Recordset = New ADODB.Recordset
> With Recordset
>
> Filter
> Src = "SELECT * FROM DB1 WHERE DB2 = 'Starting'"
> Src = Src & "and Year = '2001'"
>
> .Open Source:=Src, ActiveConnection:=Connection
> ' Write the field names
> For Col = 0 To Recordset.Fields.Count - 1
> Range("A1").Offset(0, Col).Value = _
> Recordset.Fields(Col).Name
> Next
> 'Write the recordset
> Range("A1").Offset(1, 0).CopyFromRecordset Recordset
> End With
> Set Recordset = Nothing
> Connection.Close
> Set Connection = Nothing
> End Sub 


0
Bob
3/13/2010 2:12:19 PM
Dim Con as ADODB Connection
with a space or without a space gives me an error and:

Dim Con as ADODB.Connection 
is not working also.
0
Utf
3/13/2010 2:47:01 PM
I put in Tools>References

Added:
Microsoft Access 12.0 Object Library
Microsoft ActiveX Data Objects(Multi-dimensional) 6.0 Library

there are others but they conflict with each other.  With these additions it 
still does not work.


"Bob Phillips" wrote:

> Have you set a reference to the Microsoft ActiveX Data Objects Library?

0
Utf
3/13/2010 3:00:01 PM
Not the Multi-dimensional library, that is for OLAP cubes. You want 
Microsoft ActiveX Data Objects 2.n Library as I stated.

-- 

HTH

Bob

"Philosophaie" <Philosophaie@discussions.microsoft.com> wrote in message 
news:7FB2ED89-85C9-45EF-A7F8-00E2BA86F616@microsoft.com...
>I put in Tools>References
>
> Added:
> Microsoft Access 12.0 Object Library
> Microsoft ActiveX Data Objects(Multi-dimensional) 6.0 Library
>
> there are others but they conflict with each other.  With these additions 
> it
> still does not work.
>
>
> "Bob Phillips" wrote:
>
>> Have you set a reference to the Microsoft ActiveX Data Objects Library?
> 


0
Bob
3/13/2010 5:56:07 PM
Reply:

Similar Artilces:

EXTERNAL OWA ACCESS
Hey everyone. I'm trying to setup external access to owa. Everything internally works fine. I dont want to give access to our companys default site to external parties. What do I need to setup in a new site to get OWA to work. I've tried copying all of the settings from the Exchange (and related) virtual directories into the new site, but all it does is list the mailboxes in plain text (like directory browsing). Thanks for the help. What version of Exchange are you using ? "DAN" <dancarmi@carmiflavors.com> wrote in message news:030401c3decd$a1a08760$a301280a@ph...

Excel
Text pasted into a cell from Word or other software will keep the formats including carriage returns, line numbers, tabs, etc. Can text be typed into a cell with control characters that will force this type of formating? -- mwaller If I understood your question correctly - you might try hitting Alt+Enter in order to go to a new line in the same cell Micky "mwaller" wrote: > Text pasted into a cell from Word or other software will keep the formats > including carriage returns, line numbers, tabs, etc. > Can text be typed into a cell with control charac...

access 2007 add contacts from outlook
Hi, I created a contact management DB using 2003, then upgraded it to 2007. The 2007 version has a contact management template that has a handy macro which allows you to add contacts from outlook which I copied and pasted into my similar form. Works great in the template I downloaded, however once I put the button in my DB it doesn't work - the macro has remained in tact and I can see no links to DB-specific objects. I tried to create a new button with the same macro codes and still nothing. The macro is as follows: RunCommand: AddFromOutlook Pretty simple, but I g...

converting Excel 2007 Charts to PDF
User reports that he has 4 charts created in Excel 2007 (line charts); Has issue with one of the charts when "save as PDF file". The colored background won't show up; the data series show up but no data line displays; Some data is missing. Has anyone else run into this issue? ...

How to export specific emails to excel?
I am stuck at a problem where I want to export emails with specific "TO" ( can be a Distribution List). And I expect the excel to have the email address of the sender and the time at which the email was received. Is there a way to do this? I have looked on several forums and sites, but unable to find something like this. My ultimate goal is to track the emails coming in my Microsoft outlook sent to me Distribution List and export them in an excel sheet. The objective is to look for the number of emails recieved and the time/date at which it was received.. I a...

seeking way to expire excel documents after certain date in Office
I was trying to find out if there is anything avalible out there which would allow me to set an expiration date to excel documents in Office 2003. I know there is an IRM feature in Office Professional 2003, but what about Excel 2003? Try this, Rajat: http://vbaexpress.com/kb/getarticle.php?kb_id=540 ************ Anne Troy www.OfficeArticles.com "Rajat" <Rajat@discussions.microsoft.com> wrote in message news:9E9F0F90-DD5E-4840-A75A-11CD9E1719F2@microsoft.com... >I was trying to find out if there is anything avalible out there which >would > allow me to set an ex...

which should I use: Outlook or ACCESS?
I'm a long-time ACT user and want to change. With 5,000 contacts I want to see contacts and their respective histories, todo's, calls, meetings, attachments, etc.. ... and i'd like to use template to write letters, emails, etc. I'm using Outlook for my currently over 5'500 contacts. If there's something I want to write about a contact and there's no such field (like history) I use the big comment field. If you need to do more things (what Outlook doesn't support) go with Access. Or you could start with Outlook and later export the data if you really ne...

How to open newer XLS files in Excel Version 4.0
I'm using Excel version 4. Can someone point me to an add-in or extension or something to let me open newer files ... at least Excel 95 or Excel 97? Thanks, Nick "Nicholas Fitzpatrick" <nfitz@sentex.ca> wrote... >I'm using Excel version 4. Can someone point me to an add-in or extension >or something to let me open newer files ... at least Excel 95 or Excel 97? You must have a very unconventional view of how Microsoft operates. You need to use a newer version of Excel or some other spreadsheet that opens newer Excel files. There are some inexpensive alternati...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

Excel instance in Task Manager
I created instance of the Excel.Application as following On Error Resume Next Set objExcel = GetObject (,"Excel.Application") If err.number > 0 then CreateObject("Excel.Application") End if But the problem is that on closing of my VB application, I m terminating the excel instance as follow Set objExcel = Nothing But despite closure of the VB application, the excel instance remains in the Task Manager. After if I open any other work from Explorer or from my VB Application, the workbook dont become visible. Any suggestions... -------------------------------------...

Is there any way I can import a report design from excel?
Hi My client has designed his Delivery Notes in excel. Is there any way I can import this into access as a report design? Stapes No. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Stapes" <steve.staple@gmail.com> wrote in message news:64a0bb8a-98f5-4cb8-b436-c0cff3ff65c4@s8g2000prg.googlegroups.com... > Hi > > My client has designed his Delivery Notes in excel. Is there any way I > can import this into access as a report design? > > Stapes ...

Excel 2003 Inactive Tab Colours with Vista Aero
Does anyone know how to change the inacticve tab colours for Excel 2003 running in Vista with Aero colour scheme turned on. Excel 2007 is installed on the same PC and there is no problem with that, it is just 2003 - there is virtually no difference in colour between the active and inactive tabs and it makes it very difficult for grouping tabs (selecting an individual tab is not such a problem because the active tab has bold font in the name). David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm#tabs suggests the scrollbar colour affects the tab colours but that doesn'...

How to disable "A Program is trying to access Outlook" dialogue box?
After I re-installed Outlook XP, it has started displaying a dialogue box saying "A Program is trying to access Outlook. Do you want to allow access" with Yes/No buttons and if Yes, then to allow access for specified period in minutes, whenever I try to synchronize Outlook with my Palm Pilot addressbook. Is there a way to disable this dialogue, and allow automatic access to Outlook as it was before re-installation? Srini No, you need to go to Palm and get an update. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (inse...

Excel Stacked Column Graph (3 Divisions for 3 Years)
I am using Microsoft Office Excel 2003 SP2 Professional Edition and I am attempting to create a Stacked Column graph with the following specifications: I have volumes for 3 different divisions for the years 2003, 2004, and 2005. I would like to graph a stacked column graph for different types of procedures (modalities). So the actual stacked column graph would look somewhat like the following (where D1, D2, and D3 are the 3 Divisions): | | |----| | |----|----| ...

Excel 2007 spreadsheet saved as DBF4
Just converted to Windows 7. My Excel spreadsheet .xls can't "save as DBF4" I was using Windows XP and had no problems. Help please. Mary, This has nothing to do with your version of Windows but with your version of Office (the two are not related) A Google search with "excel DBF converter" go many hits best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Mary" <Mary@discussions.microsoft.com> wrote in message news:74C9B53F-2BE4-4A86-8717-1E79690A6493@microsoft.com... > Just converted to Windows 7. My ...

rounding to nearest hundred dollar in Excel
User wants to round up to nearest $100 dollar figure if it's over 50, or down to nearest $100 figure if it's under 50. For example, if they enter dollar figure of $1234.00, they want it rounded down to the nearest $100 dollar amount, which would be $1,200.00. Thanks. 50000? Since you say round up to nearest 100 if over 50 shouldn't 1234 round up to 1300? Or did you mean 50000? Regardsless round to nearest 100 is ROUND(x,100) round up is CEILING(x,100) so you can set it the way you want by testing the condition =IF(x>50,formula1,formula2 Regards, Peo Sjoblom "Diane&...

ADO #4
Hello , i am working on a project which is maintaining the transportion for a call center company where i have to have a ADO data bound dialog on which i have place a combobox control which should have a access to database recordset field to view that field data has a dropdown in this combobox control . so that when ever the table with this field gets updated the combobox data has to get update automatictly . Thanks ...

Problem with Excel 2003 and 2007 on same computer
I've always used the Office 2003 programs. But recently I decided to switch Outlook to the 2007 version. In the process. Office 2007 also installed the 2007 versions of Excel, Word, etc. So now I have both Excel 2003, and Excel 2007 on my Windows 7 home premium computer. I don=92t care at all for the ribbon and other features of Excel 2007. But occasionally, and seemingly randomly, my opening an old Excel file brings up 2007 Excel I know there=92s a setting to create an association between .XLS, .XLT etc.. and a particular program, in this case Excel 2003. But in Windows 7, what woul...

Word 2007 how to get data from forms to Excel
I have forms that have been filled out by various people and would like to export the data into an Excel worksheet. This seemed straightforward in Word 2003, but I cannot get it to work in Word 2007. The actual form is a Word 2003 document. When I save the file as a txt file (after Word options / Advanced / Preserve fidelity / check "save form data as delimited text file") it creates the delimited file, but it gets overwritten when I close the original filled form. Any ideas? Thanks. I am having a similar problem, but the "save form data as delimited text...

Free 'absolute beginners' guide for making charts using Excel
If you are want the ABC's of making charts and a new way to look at the chart-making process, try this free book at http://www.vgupta.com ...

attach an excel workbook using send object method
I am sending emails from Access using SendObject and would like to attach an Excel workbook to the email. Any suggestions? -- Dar Hi Dar, SendObject will not work in this case, since it is only designed to send an object *within* the database (table, query, report, etc.). If you are using Outlook, you can try the method that I show on page 19 of a Word document that I call Access Links.doc. You are welcome to download a zipped copy, here: http://www.accessmvp.com/TWickerath/ Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/htm...

Access Compact/Repair Issue
Client has an Access database on the D: drive of a particular server. MSAccess is installed in the Program Files directory of the C: drive on the same server. Client needs to compact/repair the database on the D: drive due to sizing issues. There is enough room on the D: drive to handle the copy of the database that is created when performing the Compact/Repair. ISSUE: For whatever reason, the Compact/Repair process is writing some files to the C: drive (MS Access Installation) during the Compact/Repair process and the C: drive doesn't have enough capacity to handle these tempor...

OWA Form based authentication
Hello Newsgroup ! I have a question. Does someone of you know how to access the mailbox of another user when form based authentication is enabled? Without FBA enabled it is servername/exchange/user@doamin.com. I don't know how to access the mailbox directly from the logon page when FBA is enabled. Thank you in advanced Br Markus ...

Excel question #15
When using the scroll bar in Excel the screen blanks out - why? Might be a slow video card. Since Excel 2002 the screen is scrolled real time when you drag the scrollbars. Slower video cards might not be able to keep up. -- Jim Rech Excel MVP "Battlespace" <Battlespace@discussions.microsoft.com> wrote in message news:34DD582C-0BFD-40F7-B555-951CFF65B7C8@microsoft.com... | When using the scroll bar in Excel the screen blanks out - why? ...

copy and paste excel spreadsheet into autocad
My autocad drawing is linked to a excel spreadsheet. For fast updating. But the window displayed in autocad changes if I have more than on excel sheet. As I switch from auotcad to excel is there a way to permently save the excel window display and make another window display and alway keep the window the same as you switch from one software to the other ...