Excel Macro to Process Range Selection based on table header and row

bikeski,

Please upload your file in .xls format - that way there will be no problems with macros during uploading.

Patrick

Here is a sample of the table I'm looking to auto fill.
Feb-Example.xlsx

Ron,

Thanks for the file. I have now read your question many times and looked both your file and the pdf, despite that I still don't understand what you are wanting. May I ask you to explain again.

Thanks

Patrick

Hi Patrick,

Sorry I'm not very clear here, hopefully some background information will help explain what I'd like to achieve. We manually copy our daily sales from our POS into the example Excel table (Feb-Example.xlsx). I'd like to create a macro which will fetch the data from the SQL database and paste the information into highlighted fields (range selection) of the Excel table. Ideally, I'd like highlight the cells to be updated, run the marco and have the table auto fill for the highlighted cells.

Programing wise, I was thinking of using a loop structure to step through each cell of the range selection, assemble the date field based on the cell placement in the Excel table, parse the date information (BegDate) to "Call StoreSales(BegDate, EndDate, Cell)" which will grab the data from the SQL table, then paste the day's total sales into the proper Excel table cell, then step to the next cell of the range selection and repeat until until the end of the range selection.

 I'm new to writing macros so hopefully what I'm asking makes sense.  

Thanks,
Ron

Ron,

I wasn't sure whether or not you wanted a macro that links to the SQL database. As that's what you are needing, I'm afraid it's outside my area of knowledge. I will ask for assistance on this question so that it gets attention from experts who can help - so please be patient.

If you don't get the help you need please post a comment in this thread as I will receive an automatic notification and will be able to follow it up.

Patrick

Hi Patrick,
Actually I have the VBA code to pull the data from the SQL tables.The call to StoreSales()has already been written. I need the help with parsing the range selection of the Excel table. How do I put together the date of the  cell based on it's position in the Excel table?

Thanks,
Ron

Ron,

In what form is the data extracted? In other words please give me an example of the data as extracted. Without that I can't even begin to suggest how to place it in the correct cells in the table.

Patrick

Hi Patrick,

The sub routine StoreSales(BegDate, EndDate, Cell) is as follows:

-----------------------
Public Sub StoreSales(BegDate1, EndDate1, Cell1)

Dim conSQL As ADODB.Connection
Dim strSQL As String
Dim i As Integer
Dim Total As Variant
Dim rs As ADODB.Recordset
Total = 0
i = 0

Set conSQL = New ADODB.Connection
conSQL.Open "Server=THEDUB;DRIVER=SQL Server;Database=WRC;Password=***;User ID=***"
   
strSQL = "Select Sum(PUBLIC_TransactionEntry.Quantity * PUBLIC_TransactionEntry.Price) AS sum_sales" & _
  " From PUBLIC_Transaction Inner Join" & _
  " PUBLIC_TransactionEntry On PUBLIC_Transaction.TransactionNumber =" & _
  " PUBLIC_TransactionEntry.TransactionNumber Inner Join" & _
  " PUBLIC_Item On PUBLIC_TransactionEntry.ItemID = PUBLIC_Item.ID Inner Join" & _
  " PUBLIC_Department On PUBLIC_Item.DepartmentID = PUBLIC_Department.ID" & _
  " Group By PUBLIC_TransactionEntry.Quantity, PUBLIC_Item.Description," & _
  " PUBLIC_TransactionEntry.Price , PUBLIC_Transaction.Time, PUBLIC_Department.Name" & _
  " Having (PUBLIC_Transaction.Time >= (" & BegDate1 & ") And PUBLIC_Transaction.Time <" & _
  "(" & EndDate1 & "))"
 
  Set rs = New ADODB.Recordset
  rs.Open strSQL, conSQL, adOpenStatic, adLockOptimistic
     
  Do While rs.EOF = False
        Total = Total + rs!sum_sales
        i = i + 1
        rs.MoveNext
  Loop
   
  Range(Cell1).Value = Total
  conSQL.Close

End Sub

-----------------------

It works fine in another excel file and returns the total to the cell specified on the Excel worksheet "Cell1". Testing the sub routine shows that it returns the correct numeric value and then the Excel worksheets sets the correct format.

Here are my thoughts how the routine I'd like to create would be laid out.  There is some code but mostly comments. It's the comments I need help in writing.

---start code------
  Dim Year1 As String
  Dim Month1 As String
   Dim Day1 As String
  Dim CellCol As String
  Dim BegDate As  String  
  Dim EndDate As String
  Dim Cell As String

' capture the range selected/highlighted cells on the Feb-Example worksheet

'go to the 1st selected cell of Feb-Example worksheet
 
'start loop
  ' calculate date of selected cell based on column and row position in the Excel table:
      month1 = the month is always in cell A1
       Day1 = the day is  listed in column A, same row as the cell
       Year1 = the year is the title of the  column (row 4)
       BegDate =  "'"&Month1&"/"Day1&"/"&Year1&"'"}

  ' verify if valid date, if not, send message and exit routine
 
   EndDate = BegDate 'both dates are the same since I'm just looking for daily totals

  ' Cell = current cell location in Feb-Example worksheet

  Call StoreSales(BegDate,  EndDate, Cell)  'this sub routine will post the total on "Cell"

  ' move to next cell of the selected/highlighted range on Fed-Example

 'If end of selected/highlighted range then exit

End Loop

----end code----

Hope this helps.

Thanks,
Ron

Hello,
I posted a simplified request on at:

https://www.experts-exchange.com/questions/25996152/Excel-Macro-to-Process-Range-Selection.html?fromWizard=true

thanks Patrick, I did re-post and came up with a solution. It's always interesting to see the many ways to solve a problem

Ron,

Thanks for the points.

Do you need further help on this question as the VBA code is incomplete.

I'm happy to help here...

Patrick