1

I've been trying to adapt excel VBA queries for retreiving data from a closed workbook as an alternative option. I found a sample code online and I adjusted it as I thought to fit my needs. After frusterating few hours I can't quite figure out why it will not work.

When I try to specify a table I get the error "Microsoft access database engine cannot find the object 'CompanyInFo' ... I tried replacing it with a defined name range aswell and still receive the same error.

Heres the Code: 

 Sub DataLookup()

Dim str As String
Dim Recset As ADODB.Recordset
Dim query As String

Dim fileName As String
fileName = ActiveWorkbook.Sheets("DataValidation").Range("D18")

str = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=" & fileName & ";" & _
           "Extended Properties=Excel 12.0"


query = "SELECT CompanyName FROM CompanyInFo"
    'Tried SELECT [CompanyName].[CompanyInFO] FROM [CompanyInFo]
    '      SELECT * From [CompanyInFo]
    ' Works SELECT * FROM [Cell Validation$] <--- Sheet

 'Error Prompts here 
Set Recset = New ADODB.Recordset
Recset.Open query, str



Cells.Clear
Range("A2").CopyFromRecordset Recset

Dim cell As Range, i As Long
With Range("A1").CurrentRegion
    For i = 0 To Recset.Fields.Count - 1
        .Cells(1, i + 1).Value = Recset.Fields(i).Name
    Next i
    .EntireColumn.AutoFit
End With
End Sub

The tablename is "CompanyInFo" and it is located on a sheet called "Cell Validation". I wanted to reference a table as the list is updated daily with additions and sometimes removals. Mentioned before I did try creating a dynamic named range but I still received the same issues?

My thoughts: 1. I Could be missing a reference? 2. I am mis using th ADODB?

Any help or guidance is appreciated! Alex

Community
  • 1
  • 1
Alex
  • 47
  • 1
  • 7
  • [this shows you a few bits youre missing in your code](http://stackoverflow.com/questions/18637376/query-my-excel-worksheet-with-vba/18640226#18640226) –  Nov 28 '13 at 13:56
  • I looked at that earlier this morning. Is it the the lack of a WHERE clause that is causing the issues? Thanks for the link! – Alex Nov 28 '13 at 14:00
  • no because you can use `* from [Sheet1$]` with no where clause –  Nov 28 '13 at 14:01
  • I think the problem here is the way you thinking of retrieving your table. your Table only exist in Excel as a Table/Named Range. When you are querying the spreadsheet your actually have to imagine querying a grid of rows and columns and not separate as objects ie. tables –  Nov 28 '13 at 14:06
  • OKay, I was hoping it would be easy just pointing to a table and pulling from it. I think I have an idea how I want to do it. Lets see! Thanks! – Alex Nov 28 '13 at 14:12
  • i think you misunderstood me i maybe I worded the concept poorly :) I have given you an answer though –  Nov 28 '13 at 14:15

0 Answers0