Excel - Use Variable Cell Value (Sheet1!A2) in From MySQL Database Query
I'm trying to add a variable value, based on the value of cell A2 in Sheet1 of my Excel Workbook, to the following MySQL Query in the Query Editor:
SELECT o.Name, o.OrganizationId
FROM Organization AS o
WHERE o.OrganizationId = Sheet1!A2
ORDER BY o.Name ASC
I've tried using a Customized Parameter, but it doesn't seem to work with MySQL queries.
Another thing I've tried running the query through a VBA script:
Sub VoucherQuery()
queryString = "SELECT Name, OrganizationId FROM [Vouchers!$A1:B1000000]
WHERE OrganizationId=" & Worksheets("Sheet1").Range("A2").Value
End Sub
But wasn't able to combine this with a query I looked up.
The final attempt had me create a new Workbook and Get Data from Workbook, where I pulled the data from the Workbook with the original SQL Query (same query as above, without the WHERE clause), and tried to add a Condition, but I couldn't find any code to edit with that way of pulling data.
Does anyone know how to achieve this with the Get Data -> From MySQL Database Query Editor in Excel ?
mysql excel vba
edited Dec 31 '18 at 16:11
Kubie
1,3251518
asked Dec 31 '18 at 14:40
Michiel van DijkMichiel van Dijk
461112
add a comment |
I'm trying to add a variable value, based on the value of cell A2 in Sheet1 of my Excel Workbook, to the following MySQL Query in the Query Editor:
SELECT o.Name, o.OrganizationId
FROM Organization AS o
WHERE o.OrganizationId = Sheet1!A2
ORDER BY o.Name ASC
I've tried using a Customized Parameter, but it doesn't seem to work with MySQL queries.
Another thing I've tried running the query through a VBA script:
Sub VoucherQuery()
queryString = "SELECT Name, OrganizationId FROM [Vouchers!$A1:B1000000]
WHERE OrganizationId=" & Worksheets("Sheet1").Range("A2").Value
End Sub
But wasn't able to combine this with a query I looked up.
The final attempt had me create a new Workbook and Get Data from Workbook, where I pulled the data from the Workbook with the original SQL Query (same query as above, without the WHERE clause), and tried to add a Condition, but I couldn't find any code to edit with that way of pulling data.
Does anyone know how to achieve this with the Get Data -> From MySQL Database Query Editor in Excel ?
mysql excel vba
edited Dec 31 '18 at 16:11
Kubie
1,3251518
asked Dec 31 '18 at 14:40
Michiel van DijkMichiel van Dijk
461112
Hi. First off I am not an Excel user (at the moment) but am familiar somewhat with MySQL. If you 'hard code' your Excel sheet value in the MySQL WHERE clause, do you get any results? Maybe if you do, there is a connectivity issue between MySQL and Excel somewhere? Just tossing out ideas. Sorry I couldn't be of more help.
– J2112O
Dec 31 '18 at 14:50
@J2112O Sorry for the late reply, read over this comment. It connects fine when I'm running other queries, just not with a variable value from Excel in the WHERE clause
– Michiel van Dijk
Jan 7 at 14:57
add a comment |
I'm trying to add a variable value, based on the value of cell A2 in Sheet1 of my Excel Workbook, to the following MySQL Query in the Query Editor:
SELECT o.Name, o.OrganizationId
FROM Organization AS o
WHERE o.OrganizationId = Sheet1!A2
ORDER BY o.Name ASC
I've tried using a Customized Parameter, but it doesn't seem to work with MySQL queries.
Another thing I've tried running the query through a VBA script:
Sub VoucherQuery()
queryString = "SELECT Name, OrganizationId FROM [Vouchers!$A1:B1000000]
WHERE OrganizationId=" & Worksheets("Sheet1").Range("A2").Value
End Sub
But wasn't able to combine this with a query I looked up.
The final attempt had me create a new Workbook and Get Data from Workbook, where I pulled the data from the Workbook with the original SQL Query (same query as above, without the WHERE clause), and tried to add a Condition, but I couldn't find any code to edit with that way of pulling data.
Does anyone know how to achieve this with the Get Data -> From MySQL Database Query Editor in Excel ?
mysql excel vba
edited Dec 31 '18 at 16:11
Kubie
1,3251518
asked Dec 31 '18 at 14:40
Michiel van DijkMichiel van Dijk
461112
I'm trying to add a variable value, based on the value of cell A2 in Sheet1 of my Excel Workbook, to the following MySQL Query in the Query Editor:
SELECT o.Name, o.OrganizationId
FROM Organization AS o
WHERE o.OrganizationId = Sheet1!A2
ORDER BY o.Name ASC
I've tried using a Customized Parameter, but it doesn't seem to work with MySQL queries.
Another thing I've tried running the query through a VBA script:
Sub VoucherQuery()
queryString = "SELECT Name, OrganizationId FROM [Vouchers!$A1:B1000000]
WHERE OrganizationId=" & Worksheets("Sheet1").Range("A2").Value
End Sub
But wasn't able to combine this with a query I looked up.
The final attempt had me create a new Workbook and Get Data from Workbook, where I pulled the data from the Workbook with the original SQL Query (same query as above, without the WHERE clause), and tried to add a Condition, but I couldn't find any code to edit with that way of pulling data.
Does anyone know how to achieve this with the Get Data -> From MySQL Database Query Editor in Excel ?
mysql excel vba
mysql excel vba
edited Dec 31 '18 at 16:11
Kubie
1,3251518
asked Dec 31 '18 at 14:40
Michiel van DijkMichiel van Dijk
461112
edited Dec 31 '18 at 16:11
Kubie
1,3251518
asked Dec 31 '18 at 14:40
Michiel van DijkMichiel van Dijk
461112
edited Dec 31 '18 at 16:11
Kubie
1,3251518
edited Dec 31 '18 at 16:11
Kubie
1,3251518
edited Dec 31 '18 at 16:11
Kubie
1,3251518
1,3251518
asked Dec 31 '18 at 14:40
Michiel van DijkMichiel van Dijk
461112
asked Dec 31 '18 at 14:40
Michiel van DijkMichiel van Dijk
461112
asked Dec 31 '18 at 14:40
Michiel van DijkMichiel van Dijk
461112
461112
Hi. First off I am not an Excel user (at the moment) but am familiar somewhat with MySQL. If you 'hard code' your Excel sheet value in the MySQL WHERE clause, do you get any results? Maybe if you do, there is a connectivity issue between MySQL and Excel somewhere? Just tossing out ideas. Sorry I couldn't be of more help.
– J2112O
Dec 31 '18 at 14:50
@J2112O Sorry for the late reply, read over this comment. It connects fine when I'm running other queries, just not with a variable value from Excel in the WHERE clause
– Michiel van Dijk
Jan 7 at 14:57
add a comment |
Hi. First off I am not an Excel user (at the moment) but am familiar somewhat with MySQL. If you 'hard code' your Excel sheet value in the MySQL WHERE clause, do you get any results? Maybe if you do, there is a connectivity issue between MySQL and Excel somewhere? Just tossing out ideas. Sorry I couldn't be of more help.
– J2112O
Dec 31 '18 at 14:50
@J2112O Sorry for the late reply, read over this comment. It connects fine when I'm running other queries, just not with a variable value from Excel in the WHERE clause
– Michiel van Dijk
Jan 7 at 14:57
Hi. First off I am not an Excel user (at the moment) but am familiar somewhat with MySQL. If you 'hard code' your Excel sheet value in the MySQL WHERE clause, do you get any results? Maybe if you do, there is a connectivity issue between MySQL and Excel somewhere? Just tossing out ideas. Sorry I couldn't be of more help.
– J2112O
Dec 31 '18 at 14:50
Hi. First off I am not an Excel user (at the moment) but am familiar somewhat with MySQL. If you 'hard code' your Excel sheet value in the MySQL WHERE clause, do you get any results? Maybe if you do, there is a connectivity issue between MySQL and Excel somewhere? Just tossing out ideas. Sorry I couldn't be of more help.
– J2112O
Dec 31 '18 at 14:50
@J2112O Sorry for the late reply, read over this comment. It connects fine when I'm running other queries, just not with a variable value from Excel in the WHERE clause
– Michiel van Dijk
Jan 7 at 14:57
@J2112O Sorry for the late reply, read over this comment. It connects fine when I'm running other queries, just not with a variable value from Excel in the WHERE clause
– Michiel van Dijk
Jan 7 at 14:57
add a comment |
1 Answer
1
active
oldest
votes
Not sure about the Query Editor but this macro should work for you situation.
Would just need to fill in your connection information under the comment "fill these out below" and replace schema with your actual schema in the query string
Sub SendQuery()
Dim conn As Object
Dim rs1 As Object
Dim sqlstr As String
Dim val, server_name, database_name, user_id, password As String
Set conn = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
val = Trim(CStr(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value))
' fill these out below
server_name = ""
database_name = ""
user_id = ""
password = ""
' connect to database
conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=3"
' setup select string
sqlstr = "SELECT o.Name, o.OrganizationId FROM schema.Organization As o WHERE o.OrganizationId = '" & val & "' ORDER BY o.Name ASC"
' send query
rs1.Open sqlstr, conn, adOpenStatic
' copy data to sheet
With ThisWorkbook.Worksheets("Sheet1").Cells(1, 1)
.ClearContents
.CopyFromRecordset rs1
End With
' cleanup
rs1.Close
Set rs1 = Nothing
End Sub
answered Dec 31 '18 at 15:10
KubieKubie
1,3251518
Thanks, I've only had a chance to try this today. It's giving me the following error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Drive Manager] Data source name not found and no default driver specified
– Michiel van Dijk
Jan 3 at 7:46
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53988642%2fexcel-use-variable-cell-value-sheet1a2-in-from-mysql-database-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Not sure about the Query Editor but this macro should work for you situation.
Would just need to fill in your connection information under the comment "fill these out below" and replace schema with your actual schema in the query string
Sub SendQuery()
Dim conn As Object
Dim rs1 As Object
Dim sqlstr As String
Dim val, server_name, database_name, user_id, password As String
Set conn = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
val = Trim(CStr(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value))
' fill these out below
server_name = ""
database_name = ""
user_id = ""
password = ""
' connect to database
conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=3"
' setup select string
sqlstr = "SELECT o.Name, o.OrganizationId FROM schema.Organization As o WHERE o.OrganizationId = '" & val & "' ORDER BY o.Name ASC"
' send query
rs1.Open sqlstr, conn, adOpenStatic
' copy data to sheet
With ThisWorkbook.Worksheets("Sheet1").Cells(1, 1)
.ClearContents
.CopyFromRecordset rs1
End With
' cleanup
rs1.Close
Set rs1 = Nothing
End Sub
answered Dec 31 '18 at 15:10
KubieKubie
1,3251518
Thanks, I've only had a chance to try this today. It's giving me the following error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Drive Manager] Data source name not found and no default driver specified
– Michiel van Dijk
Jan 3 at 7:46
add a comment |
Not sure about the Query Editor but this macro should work for you situation.
Would just need to fill in your connection information under the comment "fill these out below" and replace schema with your actual schema in the query string
Sub SendQuery()
Dim conn As Object
Dim rs1 As Object
Dim sqlstr As String
Dim val, server_name, database_name, user_id, password As String
Set conn = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
val = Trim(CStr(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value))
' fill these out below
server_name = ""
database_name = ""
user_id = ""
password = ""
' connect to database
conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=3"
' setup select string
sqlstr = "SELECT o.Name, o.OrganizationId FROM schema.Organization As o WHERE o.OrganizationId = '" & val & "' ORDER BY o.Name ASC"
' send query
rs1.Open sqlstr, conn, adOpenStatic
' copy data to sheet
With ThisWorkbook.Worksheets("Sheet1").Cells(1, 1)
.ClearContents
.CopyFromRecordset rs1
End With
' cleanup
rs1.Close
Set rs1 = Nothing
End Sub
answered Dec 31 '18 at 15:10
KubieKubie
1,3251518
Thanks, I've only had a chance to try this today. It's giving me the following error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Drive Manager] Data source name not found and no default driver specified
– Michiel van Dijk
Jan 3 at 7:46
add a comment |
Not sure about the Query Editor but this macro should work for you situation.
Would just need to fill in your connection information under the comment "fill these out below" and replace schema with your actual schema in the query string
Sub SendQuery()
Dim conn As Object
Dim rs1 As Object
Dim sqlstr As String
Dim val, server_name, database_name, user_id, password As String
Set conn = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
val = Trim(CStr(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value))
' fill these out below
server_name = ""
database_name = ""
user_id = ""
password = ""
' connect to database
conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=3"
' setup select string
sqlstr = "SELECT o.Name, o.OrganizationId FROM schema.Organization As o WHERE o.OrganizationId = '" & val & "' ORDER BY o.Name ASC"
' send query
rs1.Open sqlstr, conn, adOpenStatic
' copy data to sheet
With ThisWorkbook.Worksheets("Sheet1").Cells(1, 1)
.ClearContents
.CopyFromRecordset rs1
End With
' cleanup
rs1.Close
Set rs1 = Nothing
End Sub
answered Dec 31 '18 at 15:10
KubieKubie
1,3251518
Not sure about the Query Editor but this macro should work for you situation.
Would just need to fill in your connection information under the comment "fill these out below" and replace schema with your actual schema in the query string
Sub SendQuery()
Dim conn As Object
Dim rs1 As Object
Dim sqlstr As String
Dim val, server_name, database_name, user_id, password As String
Set conn = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
val = Trim(CStr(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value))
' fill these out below
server_name = ""
database_name = ""
user_id = ""
password = ""
' connect to database
conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=3"
' setup select string
sqlstr = "SELECT o.Name, o.OrganizationId FROM schema.Organization As o WHERE o.OrganizationId = '" & val & "' ORDER BY o.Name ASC"
' send query
rs1.Open sqlstr, conn, adOpenStatic
' copy data to sheet
With ThisWorkbook.Worksheets("Sheet1").Cells(1, 1)
.ClearContents
.CopyFromRecordset rs1
End With
' cleanup
rs1.Close
Set rs1 = Nothing
End Sub
answered Dec 31 '18 at 15:10
KubieKubie
1,3251518
edited Dec 31 '18 at 16:32
answered Dec 31 '18 at 15:10
KubieKubie
1,3251518
answered Dec 31 '18 at 15:10
KubieKubie
1,3251518
answered Dec 31 '18 at 15:10
KubieKubie
1,3251518
1,3251518
Thanks, I've only had a chance to try this today. It's giving me the following error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Drive Manager] Data source name not found and no default driver specified
– Michiel van Dijk
Jan 3 at 7:46
add a comment |
Thanks, I've only had a chance to try this today. It's giving me the following error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Drive Manager] Data source name not found and no default driver specified
– Michiel van Dijk
Jan 3 at 7:46
Thanks, I've only had a chance to try this today. It's giving me the following error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Drive Manager] Data source name not found and no default driver specified
– Michiel van Dijk
Jan 3 at 7:46
Thanks, I've only had a chance to try this today. It's giving me the following error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Drive Manager] Data source name not found and no default driver specified
– Michiel van Dijk
Jan 3 at 7:46
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53988642%2fexcel-use-variable-cell-value-sheet1a2-in-from-mysql-database-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Hi. First off I am not an Excel user (at the moment) but am familiar somewhat with MySQL. If you 'hard code' your Excel sheet value in the MySQL WHERE clause, do you get any results? Maybe if you do, there is a connectivity issue between MySQL and Excel somewhere? Just tossing out ideas. Sorry I couldn't be of more help.
– J2112O
Dec 31 '18 at 14:50
@J2112O Sorry for the late reply, read over this comment. It connects fine when I'm running other queries, just not with a variable value from Excel in the WHERE clause
– Michiel van Dijk
Jan 7 at 14:57