Hi

I am a relative beginner to vba programming and am battling with connecting Access to excel using VBA.

My aim is to connect to my excel workbook and set the variable myVar equal to the value in my first cell.

I have been testing the following but cannot get it to work:

      Dim cnt As New ADODB.Connection
      Dim db As Database
      Set db = CurrentDb

      cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source= " & db

      Set xlApp = GetObject(, "Excel.Application")
    Set xlWb = GetObject("D:\DataPreparation\MetaData\Countries.x slx")
      Set xlWs = xlWb.Worksheets("Sheet1")

    myVar = xlWs.Cells(1, 1).Value




I get a type mismatch error at the Data source line.

I'm not sure if I'm going about this in the right way. I would appreciate it if someone could help me alter the above. If not please can you reccomend a new approach that would work.


Regards

Ryan

db is an object, of type Database. You cannot join an object to a string unless the object has a default property of type String (or another type than is automatically converted to a String, like an Integer, or a Single).