5

I'm trying to create an excel program that can get data from sheet1 to sheet2 within the same file using VBA. But when I declared the ADODB, it does not appear in the drop down list. And when I try to run sub I get the 'user defined type not defined' error. Can anyone please share with me any fixes?

The code is as below:

Sub testsql()

'declare variable
Dim objMyConn As ADODB.Connection
Dim objMyCmd As ADODB.Command
Dim objMyRecordSet As ADODB.Recordset

Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordSet = New ADODB.Recordset

'open connection
objMyConn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & wbWorkBook & ";Extended Properties=Excel 8.0;"
objMyConn.Open

'set and execute command
Set objMyCmd.activeconnection = objMyConn
objMyCmd.CommandText = "select top 10000 [Die No], Description from DieMaintenanceEntry"
objMyCmd.CommandType = adcmdtext


'open recordset
Set objMyRecordSet.Source = objMyCmd
objMyRecordSet.Open

'copy data to excel
ActiveWorkbook.Sheets("Display-Die Maintenance Summary").ActiveSheet.Range("A5").CopyFromRecordset (objMyRecordSet)

End Sub
hjh93
  • 570
  • 11
  • 27
  • did you reference the Activex library? – Sivaprasath Vadivel May 30 '17 at 05:40
  • how to? Complete beginner here so not sure. – hjh93 May 30 '17 at 05:42
  • 1
    Tools-> References -> Microsoft ActiveX Data object 2.0 library check the box click ok – Sivaprasath Vadivel May 30 '17 at 05:44
  • @SivaprasathV Agree with you except that is should be `Microsoft Active X Data Objects x.x Library` where `x.x` depends on the version of Excel you are running. – L42 May 30 '17 at 05:48
  • You get that for office library like PowerPoint library.....you get ActiveX object library from 2.0 to 2.8 or something. I am not sure of the exact number – Sivaprasath Vadivel May 30 '17 at 05:54
  • 1
    If you're just copying data from one sheet to another, inside the *same* Excel session, and you're not applying sorting or filtering, then all of this ADO business is probably overkill. – ThunderFrame May 30 '17 at 06:08
  • I totally agree with @ThunderFrame unless your are setting a complicated criteria search. – L42 May 30 '17 at 06:10
  • 1
    @SivaprasathV You should use the 6.x version (Windows Vista and later). 2.8 is for use with Windows XP and 2.0-2.7 are unsupported. – ThunderFrame May 30 '17 at 06:11
  • 1
    Thanks @ThunderFrame, I was usually going with 2.8 library without knowing the reason and thought that was the latest library. Since the code didnt have any version reference i thought he can go with the lowest version and thought that the later versions had backward compatability. – Sivaprasath Vadivel May 30 '17 at 06:14
  • @ThunderFrame I will be implementing some complex filtering/sorting down the line of the program. But for now I need to make sure that the data can display first. – hjh93 May 30 '17 at 06:15

1 Answers1

5

You can solve this in two ways:

  1. Early Binding (as hinted by your code)
    What you need to do is reference the correct Microsoft ActiveX Data Object. For my version, it is 6.1.

    References Library

  2. Using Late Binding (No need to reference library)

    Dim objMyConn As Object '/* Declare object type variable */
Dim objMyCmd As Object
Dim objMyRecordset As Object

'/* Set using create object */
Set objMyConn = CreateObject("ADODB.Connection")
Set objMyCmd = CreateObject("ADODB.Command")
Set objMyRecordset = CreateObject("ADODB.Recordset")

As for which to use, I can only give suggestion. During development, use Early Binding for you to take advantage of Intellisense. At deployment, change to Late Binding to overcome version compatibility issues.

L42
  • 19,427
  • 11
  • 44
  • 68
  • Thanks for the input. But now after changing the code I get a new error. – hjh93 May 30 '17 at 06:09
  • "objMyConn.Open" what needs to be changed? – hjh93 May 30 '17 at 06:11
  • @hjh93 That is already a new error and is out of scope on my answer :). Just kidding (but I'm serious), anyways, you need to check your connection string. Try replacing `wbWorkbook` with `ThisWorkbook.FullName` – L42 May 30 '17 at 06:14
  • @L42, is the msjet4.0 dll available in windows 10 by default? – Sivaprasath Vadivel May 30 '17 at 06:18
  • @SivaprasathV Is that related to this post or you're asking a different question. Btw, it should probably work (no way to check, I'm on Win7) but you should use [ACE OLEDB](https://stackoverflow.com/questions/14401729/difference-between-microsoft-jet-oledb-and-microsoft-ace-oledb) instead since it superceded JET. – L42 May 30 '17 at 06:28
  • I usuallly use Ace but for the CSV files ACE doesnt work, Whenever I use Jet its says jet4.0 not present. Is there awork around for this? – Sivaprasath Vadivel May 30 '17 at 06:30
  • @SivaprasathV ACE works in CSV, but to help you more, post a question and provide specific issues there. Let us respect OP's thread. – L42 May 30 '17 at 06:32
  • Thanks, I will do that, The Next Time I encounter a problem with the jet oledb – Sivaprasath Vadivel May 30 '17 at 06:33