|
VBA Function to talk to MS Access
I'm new to VBA programming, would appreciate some advice,
I have an Excel spreadsheet that needs a pulldown menu based on a SQL query statement. Would like to define a cell (or a named range) like this : =(MyQueryTable(Worksheet1!$A$3)) where MyQueryTable is my new VBA function that does a very simple SQL query call to a MS Access mdb file in the same directory as the excel sheet. so, the SQL statement is something like this "SELECT Position FROM PositionRate WHERE CompanyID = " & InputParameter & ";" InputParameter is what i feed into this function (Worksheet!$A$3) How would I do this ? Appreciate your help. Chad |
VBA Function to talk to MS Access
Hi ChadF
Maybe you like the example workbook with the Data validation cells in the download on this page. It read all unique values in the data validation list http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl "ChadF" wrote in message ... I'm new to VBA programming, would appreciate some advice, I have an Excel spreadsheet that needs a pulldown menu based on a SQL query statement. Would like to define a cell (or a named range) like this : =(MyQueryTable(Worksheet1!$A$3)) where MyQueryTable is my new VBA function that does a very simple SQL query call to a MS Access mdb file in the same directory as the excel sheet. so, the SQL statement is something like this "SELECT Position FROM PositionRate WHERE CompanyID = " & InputParameter & ";" InputParameter is what i feed into this function (Worksheet!$A$3) How would I do this ? Appreciate your help. Chad |
VBA Function to talk to MS Access
Ok, I've followed the example listed in the website and produced the following
function based on that : Option Explicit Sub Import_Positions(InputVar As String, MyDatabaseFilePathName As String, ClearRange As Boolean) Dim MyConnection As String Dim MySQL As String Dim MyDatabase As Object Dim I As Integer Dim str1 As Variant 'If ClearRange = True clear all cells in column K:O Debug.Print "I am in the subroutine, Input value is " & InputVar If ClearRange Then Sheets(DestSheetRange.Parent.Name).Range(DestSheet Range.Address, DestSheetRange.Offset(0, 4)).EntireColumn.ClearContents 'Create connection string MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" MyConnection = MyConnection & "Data Source=" & MyDatabaseFilePathAndName & ";" MySQL = "SELECT Position FROM PositionRate WHERE CompanyID = " & InputVar & ";" ' Open the database and copy the data On Error GoTo SomethingWrong Set MyDatabase = CreateObject("adodb.recordset") MyDatabase.Open MySQL, MyConnection, 0, 1, 1 ' Check to make sure we received data and copy the data If Not MyDatabase.EOF Then 'Copy to K:M in the Criteria sheet (Columns are hidden) DestSheetRange.Offset(0, 1).CopyFromRecordset MyDatabase Else MsgBox "No records returned from : PositionRate Table", vbCritical End If MyDatabase.Close Set MyDatabase = Nothing Exit Sub SomethingWrong: On Error GoTo 0 Set MyDatabase = Nothing MsgBox "Error copying data", vbCritical, "Test Access data to Excel" End Sub -------------------------------------------------------- The way I 'invoke' this function is through a named range variable in Excel by doing Insert-Name-Define let's say I give it a name "myRanges" and define it for what I think the sub is returning (a range)... like so, myRanges = (Worksheet1!$A$3, Worksheet1!$B$2, TRUE) and then setting a validation pulldown menu based on this output. Cell defined to be =myRanges (Much of this function was cut-n-pasted from the example shown in http://www.rondebruin.nl/accessexcel.htm) When I did all the above, Excel came back to me with an error - source evaluates to an error. When I tried looking at the error output screen in the VBA editor windows, I didnt see anything to help. I would appreciate any suggestions.... Thank you, Chad "Ron de Bruin" wrote: Hi ChadF Maybe you like the example workbook with the Data validation cells in the download on this page. It read all unique values in the data validation list http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl "ChadF" wrote in message ... I'm new to VBA programming, would appreciate some advice, I have an Excel spreadsheet that needs a pulldown menu based on a SQL query statement. Would like to define a cell (or a named range) like this : =(MyQueryTable(Worksheet1!$A$3)) where MyQueryTable is my new VBA function that does a very simple SQL query call to a MS Access mdb file in the same directory as the excel sheet. so, the SQL statement is something like this "SELECT Position FROM PositionRate WHERE CompanyID = " & InputParameter & ";" InputParameter is what i feed into this function (Worksheet!$A$3) How would I do this ? Appreciate your help. Chad |
VBA Function to talk to MS Access
Whoops, meant to define the setting range call like this:
MyRanges = Import_Positions(Worksheet1!$A$3, Worksheet1!$B$2, TRUE) where Worksheet1!$A$3 cell contains the string we want to query against and Worksheet1!$B$2 contains the filenameandpath ... "ChadF" wrote: Ok, I've followed the example listed in the website and produced the following function based on that : Option Explicit Sub Import_Positions(InputVar As String, MyDatabaseFilePathName As String, ClearRange As Boolean) Dim MyConnection As String Dim MySQL As String Dim MyDatabase As Object Dim I As Integer Dim str1 As Variant 'If ClearRange = True clear all cells in column K:O Debug.Print "I am in the subroutine, Input value is " & InputVar If ClearRange Then Sheets(DestSheetRange.Parent.Name).Range(DestSheet Range.Address, DestSheetRange.Offset(0, 4)).EntireColumn.ClearContents 'Create connection string MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" MyConnection = MyConnection & "Data Source=" & MyDatabaseFilePathAndName & ";" MySQL = "SELECT Position FROM PositionRate WHERE CompanyID = " & InputVar & ";" ' Open the database and copy the data On Error GoTo SomethingWrong Set MyDatabase = CreateObject("adodb.recordset") MyDatabase.Open MySQL, MyConnection, 0, 1, 1 ' Check to make sure we received data and copy the data If Not MyDatabase.EOF Then 'Copy to K:M in the Criteria sheet (Columns are hidden) DestSheetRange.Offset(0, 1).CopyFromRecordset MyDatabase Else MsgBox "No records returned from : PositionRate Table", vbCritical End If MyDatabase.Close Set MyDatabase = Nothing Exit Sub SomethingWrong: On Error GoTo 0 Set MyDatabase = Nothing MsgBox "Error copying data", vbCritical, "Test Access data to Excel" End Sub -------------------------------------------------------- The way I 'invoke' this function is through a named range variable in Excel by doing Insert-Name-Define let's say I give it a name "myRanges" and define it for what I think the sub is returning (a range)... like so, myRanges = (Worksheet1!$A$3, Worksheet1!$B$2, TRUE) and then setting a validation pulldown menu based on this output. Cell defined to be =myRanges (Much of this function was cut-n-pasted from the example shown in http://www.rondebruin.nl/accessexcel.htm) When I did all the above, Excel came back to me with an error - source evaluates to an error. When I tried looking at the error output screen in the VBA editor windows, I didnt see anything to help. I would appreciate any suggestions.... Thank you, Chad "Ron de Bruin" wrote: Hi ChadF Maybe you like the example workbook with the Data validation cells in the download on this page. It read all unique values in the data validation list http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl "ChadF" wrote in message ... I'm new to VBA programming, would appreciate some advice, I have an Excel spreadsheet that needs a pulldown menu based on a SQL query statement. Would like to define a cell (or a named range) like this : =(MyQueryTable(Worksheet1!$A$3)) where MyQueryTable is my new VBA function that does a very simple SQL query call to a MS Access mdb file in the same directory as the excel sheet. so, the SQL statement is something like this "SELECT Position FROM PositionRate WHERE CompanyID = " & InputParameter & ";" InputParameter is what i feed into this function (Worksheet!$A$3) How would I do this ? Appreciate your help. Chad |
VBA Function to talk to MS Access
"Ron de Bruin" wrote: I use a dynamic range (list1 , list2 and list 3 ) in the example workbook =OFFSET(Criteria!$K$1,0,0,COUNTA(Criteria!$K:$K),1 ) See Debra's site http://www.contextures.com/xlNames01.html#Dynamic -- Regards Ron de Bruin http://www.rondebruin.nl "ChadF" wrote in message ... Ok, I've followed the example listed in the website and produced the following function based on that : Option Explicit Sub Import_Positions(InputVar As String, MyDatabaseFilePathName As String, ClearRange As Boolean) Dim MyConnection As String Dim MySQL As String Dim MyDatabase As Object Dim I As Integer Dim str1 As Variant 'If ClearRange = True clear all cells in column K:O Debug.Print "I am in the subroutine, Input value is " & InputVar If ClearRange Then Sheets(DestSheetRange.Parent.Name).Range(DestSheet Range.Address, DestSheetRange.Offset(0, 4)).EntireColumn.ClearContents 'Create connection string MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" MyConnection = MyConnection & "Data Source=" & MyDatabaseFilePathAndName & ";" MySQL = "SELECT Position FROM PositionRate WHERE CompanyID = " & InputVar & ";" ' Open the database and copy the data On Error GoTo SomethingWrong Set MyDatabase = CreateObject("adodb.recordset") MyDatabase.Open MySQL, MyConnection, 0, 1, 1 ' Check to make sure we received data and copy the data If Not MyDatabase.EOF Then 'Copy to K:M in the Criteria sheet (Columns are hidden) DestSheetRange.Offset(0, 1).CopyFromRecordset MyDatabase Else MsgBox "No records returned from : PositionRate Table", vbCritical End If MyDatabase.Close Set MyDatabase = Nothing Exit Sub SomethingWrong: On Error GoTo 0 Set MyDatabase = Nothing MsgBox "Error copying data", vbCritical, "Test Access data to Excel" End Sub -------------------------------------------------------- The way I 'invoke' this function is through a named range variable in Excel by doing Insert-Name-Define let's say I give it a name "myRanges" and define it for what I think the sub is returning (a range)... like so, myRanges = (Worksheet1!$A$3, Worksheet1!$B$2, TRUE) and then setting a validation pulldown menu based on this output. Cell defined to be =myRanges (Much of this function was cut-n-pasted from the example shown in http://www.rondebruin.nl/accessexcel.htm) When I did all the above, Excel came back to me with an error - source evaluates to an error. When I tried looking at the error output screen in the VBA editor windows, I didnt see anything to help. I would appreciate any suggestions.... Thank you, Chad "Ron de Bruin" wrote: Hi ChadF Maybe you like the example workbook with the Data validation cells in the download on this page. It read all unique values in the data validation list http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl "ChadF" wrote in message ... I'm new to VBA programming, would appreciate some advice, I have an Excel spreadsheet that needs a pulldown menu based on a SQL query statement. Would like to define a cell (or a named range) like this : =(MyQueryTable(Worksheet1!$A$3)) where MyQueryTable is my new VBA function that does a very simple SQL query call to a MS Access mdb file in the same directory as the excel sheet. so, the SQL statement is something like this "SELECT Position FROM PositionRate WHERE CompanyID = " & InputParameter & ";" InputParameter is what i feed into this function (Worksheet!$A$3) How would I do this ? Appreciate your help. Chad |
VBA Function to talk to MS Access
Hey Ron,
Just wanted to get back with you a little bit ... your example works very well. I was able to craft a subroutine based on your macro that does queries on the fly to a MS Access database. Final code looks like this : Public Sub GetRate() Dim MyConnection As String Dim MySQL As String Dim MyDatabase As Object Dim MyDatabaseFilePathAndName As String MyDatabaseFilePathAndName = ThisWorkbook.Path & "\Statics.mdb" 'Create connection string MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" MyConnection = MyConnection & "Data Source=" & _ MyDatabaseFilePathAndName & ";" Sheets("Sheet1").Range("D4:D10").ClearContents 'MySQL = "SELECT Position FROM PositionRate WHERE CompanyID = " & InputVar & ";" MySQL = "Select [Company] FROM Company" ' Open the database and copy the data On Error GoTo SomethingWrong Set MyDatabase = CreateObject("adodb.recordset") MyDatabase.Open MySQL, MyConnection, 0, 1, 1 ' Check to make sure we received data and copy the data If Not MyDatabase.EOF Then 'Return value of function (returns a range) Sheets("Sheet1").Range("D4").CopyFromRecordset MyDatabase End If MyDatabase.Close Set MyDatabase = Nothing Exit Sub SomethingWrong: On Error GoTo 0 Set MyDatabase = Nothing MsgBox "Error copying data", vbCritical, "Test Access data to Excel" End Sub '----------------------------- I'm now making a series of subroutines / single-return functions that are based on variations of the SQL logic outlined above. Thanks so much ) Chad "Ron de Bruin" wrote: I use a dynamic range (list1 , list2 and list 3 ) in the example workbook =OFFSET(Criteria!$K$1,0,0,COUNTA(Criteria!$K:$K),1 ) See Debra's site http://www.contextures.com/xlNames01.html#Dynamic -- Regards Ron de Bruin http://www.rondebruin.nl "ChadF" wrote in message ... Ok, I've followed the example listed in the website and produced the following function based on that : Option Explicit Sub Import_Positions(InputVar As String, MyDatabaseFilePathName As String, ClearRange As Boolean) Dim MyConnection As String Dim MySQL As String Dim MyDatabase As Object Dim I As Integer Dim str1 As Variant 'If ClearRange = True clear all cells in column K:O Debug.Print "I am in the subroutine, Input value is " & InputVar If ClearRange Then Sheets(DestSheetRange.Parent.Name).Range(DestSheet Range.Address, DestSheetRange.Offset(0, 4)).EntireColumn.ClearContents 'Create connection string MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" MyConnection = MyConnection & "Data Source=" & MyDatabaseFilePathAndName & ";" MySQL = "SELECT Position FROM PositionRate WHERE CompanyID = " & InputVar & ";" ' Open the database and copy the data On Error GoTo SomethingWrong Set MyDatabase = CreateObject("adodb.recordset") MyDatabase.Open MySQL, MyConnection, 0, 1, 1 ' Check to make sure we received data and copy the data If Not MyDatabase.EOF Then 'Copy to K:M in the Criteria sheet (Columns are hidden) DestSheetRange.Offset(0, 1).CopyFromRecordset MyDatabase Else MsgBox "No records returned from : PositionRate Table", vbCritical End If MyDatabase.Close Set MyDatabase = Nothing Exit Sub SomethingWrong: On Error GoTo 0 Set MyDatabase = Nothing MsgBox "Error copying data", vbCritical, "Test Access data to Excel" End Sub -------------------------------------------------------- The way I 'invoke' this function is through a named range variable in Excel by doing Insert-Name-Define let's say I give it a name "myRanges" and define it for what I think the sub is returning (a range)... like so, myRanges = (Worksheet1!$A$3, Worksheet1!$B$2, TRUE) and then setting a validation pulldown menu based on this output. Cell defined to be =myRanges (Much of this function was cut-n-pasted from the example shown in http://www.rondebruin.nl/accessexcel.htm) When I did all the above, Excel came back to me with an error - source evaluates to an error. When I tried looking at the error output screen in the VBA editor windows, I didnt see anything to help. I would appreciate any suggestions.... Thank you, Chad "Ron de Bruin" wrote: Hi ChadF Maybe you like the example workbook with the Data validation cells in the download on this page. It read all unique values in the data validation list http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl "ChadF" wrote in message ... I'm new to VBA programming, would appreciate some advice, I have an Excel spreadsheet that needs a pulldown menu based on a SQL query statement. Would like to define a cell (or a named range) like this : =(MyQueryTable(Worksheet1!$A$3)) where MyQueryTable is my new VBA function that does a very simple SQL query call to a MS Access mdb file in the same directory as the excel sheet. so, the SQL statement is something like this "SELECT Position FROM PositionRate WHERE CompanyID = " & InputParameter & ";" InputParameter is what i feed into this function (Worksheet!$A$3) How would I do this ? Appreciate your help. Chad |
| All times are GMT +1. The time now is 01:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com