Calling Data from access based on a specific name in TOC cell A:27

LZ_Code

New Member
Joined
Jan 30, 2025
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Bellow is what I have so far. I am calling in query Analog Inputs, but need to specifically call Analog Input from Sheet 'TOC' cell A27 which will be PLCPanel.


VBA Code:
Option Explicit

Sub AnalogInput_DB()

    'Path
    Dim strPath As String
   
    'Provider
    Dim strProv As String
   
    'Connection String
    Dim strCn As String
   
    'Connection
    Dim Cn As New Connection
                 
    'RecordSet for AI
    Dim rsQry_AI As New Recordset
   
    'SQL Query for AI
    Dim strQry_AI As String
   
   
    'Establish connection to Project DB. Looks at the filepath specified in cell B1 of Project_DB Sheet
   strPath = ActiveWorkbook.Sheets("PAGE").Range("B1").Text
    strProv = "Microsoft.ACE.OLEDB.12.0;"
   strCn = "Provider=" & strProv & "Data Source=" & _
   strPath

    'Connection Open
   Cn.Open strCn
              
    'SQL Query to import Digital Input Tags
   strQry_AI = "SELECT Instruments.PLCPanel, Instruments.Address FROM Instruments WHERE (((Instruments.AnalogInput)=True)) ORDER BY Instruments.PLCPanel;"
   rsQry_AI.Open strQry_AI, Cn
   
    'Puts Data into the Device Column of the Digital Device Sheet
   ActiveWorkbook.Sheets("AnalogInput").Range("A17").CopyFromRecordset rsQry_AI
   
    Cn.Close
   

End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.


Trying to get the PLCPanel User puts in sheet "TOC" cell A:27, and gets only the 'SQL Query to import Analog Input Tags the user needs.
 
Upvote 0
I am trying to make the code take the name of a panel in a cell and take the specific Analog Inputs from Access for that specific Panel. Right now it is semi perfect it takes the query for all Analog Inputs for the project, based on the file in the excel cell.

1738681746820.png


Here is a fresh version of the original code:

VBA Code:
Option Explicit

'Sub AnalogInput_DB()

    'Path
    Dim strPath As String
    
    'Provider
    Dim strProv As String
    
    'Connection String
    Dim strCn As String
    
    'Connection
    Dim Cn As New Connection
                  
    'RecordSet for AI
    Dim rsQry_AI As New Recordset
    
    'SQL Query for AI
    Dim strQry_AI As String
    
    
    'Establish connection to Project DB. Looks at the filepath specified in cell B1 of Project_DB Sheet
    strPath = ActiveWorkbook.Sheets("PAGE").Range("B1").Text
    strProv = "Microsoft.ACE.OLEDB.12.0;"
    strCn = "Provider=" & strProv & "Data Source=" & _
    strPath

    'Connection Open
    Cn.Open strCn
               
    'SQL Query to import Digital Input Tags
    strQry_AI = "SELECT Instruments.PLCPanel, Instruments.Address FROM Instruments WHERE (((Instruments.AnalogInput)=True)) ORDER BY Instruments.PLCPanel;"
    rsQry_AI.Open strQry_AI, Cn
    
    'Puts Data into the Device Column of the Digital Device Sheet
    ActiveWorkbook.Sheets("AnalogInput").Range("A17").CopyFromRecordset rsQry_AI
    
    Cn.Close
    

'End Sub
 
Upvote 0
Your posts talk about a panel name at A:27 (which is an invalid range).
Your screen shot shows the panel name neing at B5.

What it the sheet name and cell reference for the Panel Name ?
Is it one Panel Name or a list of Names ?

The below assumes it's a list and that it is located on a Sheet Named TOC at B5.

VBA Code:
Sub AnalogInput_DB()

    'Path
    Dim strPath As String
    
    'Provider
    Dim strProv As String
    
    'Connection String
    Dim strCn As String
    
    'Connection
    Dim Cn As New Connection
                  
    'RecordSet for AI
    Dim rsQry_AI As New Recordset
    
    'SQL Query for AI
    Dim strQry_AI As String
    
    'Panel Criteria
    Dim rngCriteria As Range
    Dim strCriteria As String
    
    With Worksheets("TOC")                                                              ' <-- Change this to sheet with criteria
        Set rngCriteria = .Range("B" & 5)                                               ' <-- Change this to first cell of criteria
        ' Resize for a list of criteria
        Set rngCriteria = .Range(rngCriteria, .Cells(Rows.Count, rngCriteria.Column).End(xlUp))
        ' Build Criteria String
        strCriteria = "('" & Join(Application.Transpose(rngCriteria.Value), "','") & "')"
    End With
    
    
    'Establish connection to Project DB. Looks at the filepath specified in cell B1 of Project_DB Sheet
    strPath = ActiveWorkbook.Sheets("PAGE").Range("B1").Text
    strProv = "Microsoft.ACE.OLEDB.12.0;"
    strCn = "Provider=" & strProv & "Data Source=" & _
    strPath

    'Connection Open
    Cn.Open strCn
                
    strQry_AI = "SELECT Instruments.PLCPanel, Instruments.Address FROM Instruments" _
                & " WHERE Instruments.AnalogInput=True AND Instruments.PLCPanel IN " & strCriteria _
                & " ORDER BY Instruments.PLCPanel;"
    
    rsQry_AI.Open strQry_AI, Cn
    
    'Puts Data into the Device Column of the Digital Device Sheet
    ActiveWorkbook.Sheets("AnalogInput").Range("A17").CopyFromRecordset rsQry_AI
    
    Cn.Close
    
End Sub
 
Last edited:
Upvote 0
Solution

Apologies for the confusion. The user will be typing in the Panel name in the cell shown in the screen shot named "Page", destination ("AnalogInput").Range("A17").
 

Attachments

  • Screenshot 2025-02-04 162531.png
    Screenshot 2025-02-04 162531.png
    20.5 KB · Views: 3
Upvote 0
It worked I fixed the error it was giving me. see the small changes i did. it worked thank you so much!


VBA Code:
    With Worksheets("PAGE")                                                              ' <-- Change this to sheet with criteria
        Set rngCriteria = .Range("B" & 5)                                               ' <-- Change this to first cell of criteria
        ' Resize for a list of criteria
        Set rngCriteria = .Range(rngCriteria, .Cells(rngCriteria.Column))
        ' Build Criteria String
        strCriteria = "('" & Join(Application.Transpose(rngCriteria.Value), "','") & "')"
    End With
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top