Using Excel Data in a query

[AC5FF]

I am in the process of re-vamping an outdated and severely flawed inventory database. I currently have an Access DB that runs inventory control, but an Excel spreadsheet (x10) that contains all the individual inventories for reporting. The end goal is to get rid of the Excel files and move to Access completely. But until I get all the forms working to the boss's liking I need to keep using all of the spreadsheets...

I have pulled data from Access into Excel before - that's a no brainer. But what I need to do now is query Access tables by data in my Excel spreadsheet.

I.E. Data at cell F12 in Excel needs to link to tabel "Inventory" in Access and return the "On Hand" data from that Access table to the same spreadsheet cell G12.

Is this even possible? Or, do I continue working the Excel sheets by hand until I can get them totally replaced in Access?

THANKS!

 

[the_net_2.0]

There are a few different ways you can do this. The easiest might just be to use DAO and create the objects you need and manipulate them as appropriate. The other would be to use the OLE provider with ADO and query out the Access table that way.

You're going to be writing the vba code from Excel, right? Or is it from Access to Excel? Which program is the source and which is the criteria?

This article might get you started with the ADOX method: http://support.microsoft.com/kb/257819

It's not really a very good article, but you can learn a few things from it...

 

[AC5FF]

Sorry for the delay in a reply here --- work has gotten out of control the past couple weeks...

The way i understand this -- Excel is the criteria, and Access will hold the source information.

I've done something like this in the past with ... i think it was vlookup function. But that was just from one workbook to another within the same spreadsheet file. As for writing VB and/or using a DAO.... Not sure about that - I have never gone down that road before..

 

[the_net_2.0]

Sorry for the delay in a reply here --- work has gotten out of control the past couple weeks...

The way i understand this -- Excel is the criteria, and Access will hold the source information.

I've done something like this in the past with ... i think it was vlookup function. But that was just from one workbook to another within the same spreadsheet file. As for writing VB and/or using a DAO.... Not sure about that - I have never gone down that road before..

As Access as the source and Excel as the criteria schema, YES it is possible and actually it is pretty easy do. With that said though, I have not heard of very many people ever needing to do it because the queries are just usually in Access or Excel, one or the other.

But, to get something like this set up, you might just be inviting unneccessary complication into your life. I would only do it as a last resort.

<edit>

to give you an example, consider this function:

Function AdoExample(                 strSource As String, _
                        strSheet As String, _
                        Optional bHeaders As Boolean)

On Error Resume Next

'******************************************************************************
'_____________________________________________________________________________*
'                                                                             |
'THIS FUNCTION ASSUMES THAT YOU ARE USING MS OFFICE 8.0 OR LATER.  THIS       |
'WAS TESTED USING AN 8.0 VERSION (2002) SPREADSHEET IN OFFICE 2007.  LATER    |
'FILE EXTENSIONS MAY REQUIRE AN UPDATED ISAM DRIVER AS YOU MAY ENCOUNTER      |
'AN ERROR.                                                                    |
'                                                                             |
'Note: This function does not check the validity of the worksheet argument.   |
'      Code must be modified to accomodate this risk of error.                |
'_____________________________________________________________________________|
'                                                                             *
'******************************************************************************

Dim ctr As Integer
Dim strList As String
Dim iHeaders As String

   If Dir(strSource) = "" Then
      MsgBox "Source Not Found..."
         Exit Function
   End If
         
            If bHeaders Then
               iHeaders = ""
            Else
               iHeaders = " HDR=No;"
            End If

On Error GoTo Cleanup

Dim cnStr As String
Dim cn As ADODB.Connection
Dim cnRs As ADODB.Recordset
Set cn = New ADODB.Connection
Set cnRs = New ADODB.Recordset

   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strSource & ";" & _
                          "Extended Properties=" & """" & "Excel 8.0;" & iHeaders & """"
      .Open
   End With

      cnRs.Open "SELECT * FROM [" & strSheet & "$]", cn
      
         With cnRs
         
            Do Until .EOF
               
               For ctr = 0 To cnRs.Fields.Count - 1
                  If .AbsolutePosition = 0 Then
                     If Not bHeaders Then
                        'DO SOMETHING HERE
                     End If
                  Else
                        'DO SOMETHING ELSE HERE
                  End If
               Next ctr
               
                     .MoveNext
                  
            Loop
                  
         End With

Cleanup:
   ctr = 0
   iHeaders = ""
   cnRs.Close
   cn.Close
   Set cnRs = Nothing
   Set cn = Nothing

      If Err.Number <> 0 Then
         MsgBox (Err.Description)
      End If
            
End Function

That code uses a specified Excel worksheet as a recordset. So...in essence it could be modified to lookup criteria from an Access object...