0

I was able to adapt this link for my project, but I am looking for a way to duplicate for multiple rows. Here is my query:

SELECT data_0.InvoiceNumber, 
       data_0.CustomerNumber, 
       data_0.CustomerName, 
       data_0.Address, 
       data_0.City, 
       data_0.State, 
       data_0.Zip, 
       data_0.Zone, 
       data_0.PartNumber, 
       data_0.PartDescription
FROM bitnami_wordpress.data data_0
WHERE (data_0.InvoiceNumber=?)

The invoicenumber is the parameter and that is filled with =Sheet2!$A$4

Ideally, I would enter my parameters in Column A and Columns B:K are filled with the data from the database, is there a way to do that without setting up a query for each individual row, almost like "Fill Down"?

4
  • 1
    Your command should like this ,,, "SELECT name FROM user **WHERE data.Invno=" & Worksheets("Sheet2").Range("A4").Value**. Sep 18, 2018 at 4:44
  • is there a way to do that without setting up a query for each individual row Of course. Create VBA macro as a Publuc Function which returns 2-dimentional array. Obtain data from MySQL and copy it from recordset to output array. Assign this function to a destination range as an array function. PS. I do not recommend to define this function as a volatile...
    – Akina
    Sep 18, 2018 at 5:43
  • @Akina, since i found your comment just below of mine so I thought that you were addressing to me & I've posted the comments ! If was not to me then I just delete it. !! ☺ Sep 18, 2018 at 11:49
  • @Akina can you elaborate a little, I am very inexperienced with VBA and mySQL, thanks! Sep 25, 2018 at 20:24

1 Answer 1

0

can you elaborate a little, I am very inexperienced with VBA and mySQL, thanks!

How to create array function.

Create new workbook. Open VBA editor. Insert - Module. Copy the below code to it:

Option Base 0

Public Function ReturnArray(Optional val As Integer = 1) As Variant()
' Application.Volatile
Dim tmp()
ReDim tmp(1, 1)
tmp(0, 0) = val
tmp(0, 1) = val + 1
tmp(1, 0) = val + 2
tmp(1, 1) = val + 3
ReturnArray = tmp
End Function

Select A1:B2 on a worksheet. Insert the below formula to a formula field:

=ReturnArray()

or

=ReturnArray(1)

or

=ReturnArray(C1)

Finalize entering a formula with Ctrl-Enter to show that it is array formula. The destination range will be filled with calculated values.

If you need to re-calculate values because the source value (C1 cell in last formula) was changed you must do it manually by pressing F9.

You may force auto-calculation by uncommenting Application.Volatile - but you must remember that any change on the worksheet will fire recalculation. If your code connects to external server this can be extremely expensive.

The communication with MySQL server do not differ from the communication from any another DB server. Install proper MySQL connector, create proper data source using it, and use ADODB for to obtain/edit the data you need. This process is well-documented in MSDN.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .