N
Need Help Fast!
I have an array in excel and an Access database. In the array there are three
columns. They are StateFip, CommodityCode and Practice Code. Based on these 3
values I want Excel to run my macro, which goes into Access, retrieves data
specific to these 3 values and pastes into the workbook and carries out
multiple calculations. When this is done I want it to save the file based on
3 cells and then go down to the next line in the array and repeat all of
these steps. With help from many posters on here I am so close. I will post
the code and then make some comments in the end.
Option Explicit
Private mcnToDatabase As Connection
Private mwksResults As Excel.Worksheet
Private Const STATE_FIPS_COL = 0
Private Const COMMODITY_COLUMN = 1
Private Const PRACTICE_COL = 2
Public Const dbpath = "D:\Profiles\cherring\Desktop\New Folder\Automateopt
updated!\DevelopIndexOut_no_Price_vol.mdb"
Private Const CS = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Mode=Share Deny None;Jet OLEDB:Engine Type=4;Data Source="
Private Const CLIENT_TAB = "CLIENT"
Private Const ALT_TAB = "ALT1"
Sub MainMacro1()
Call Run(dbpath)
End Sub
Public Sub Run(dbpath As String)
Dim lDataRow As Long
Dim lData As Long
Dim alData() As Long
Dim s As String
Dim r As String
Dim t As String
s = Replace(Worksheets("CountyYield").Range("A5").Text, " ", "")
r = Worksheets("CountyYield").Range("B5").Text
t = Worksheets("CountyYield").Range("C5").Text
'Set asData = info in Excel
For lDataRow = 0 To UBound(GetCurrentData, 1) - 1
alData = GetCurrentData
Main dbpath, alData(lDataRow, STATE_FIPS_COL), alData(lData,
COMMODITY_COLUMN), alData(lData, PRACTICE_COL)
'RunSolver
'Save as new workbook
'Next lDataRow
ActiveWorkbook.SaveAs Filename:="D:\Profiles\cherring\Desktop\states\" &
s & "_" & r & "_" & t & ".xls"
Next lDataRow
End Sub
Private Function GetCurrentData() As Long()
Dim alTemp() As Long
Dim iCol As Long
Dim iEnd As Long
Dim iRow As Long
Set mwksResults = Application.Worksheets("mwksResults")
iEnd = mwksResults.Range("A2").End(xlDown).Row
ReDim alTemp(iEnd - 2, 2)
For iRow = 2 To iEnd
For iCol = 1 To 3
alTemp(iRow - 2, iCol - 1) = mwksResults.Cells(iRow, iCol).Value
Next iCol
Next iRow
GetCurrentData = alTemp
Erase alTemp
End Function
Private Sub ConnectToDatabase(dbpath As String)
End Sub
Basically it starts out with Macro1() and then goes into the PublicSubRun.
When it does that I want it to go into the array and do what I explained
before. My problem is that it goes into the PrivateFunction, loops through
that a bunch of times, then does the macro. Then it repeats. My only problem
is it just keeps bringing up the same data from Access because it's not
actually looping through the array when I have it say next ldatarow. Does
anyone know how to fix this?
columns. They are StateFip, CommodityCode and Practice Code. Based on these 3
values I want Excel to run my macro, which goes into Access, retrieves data
specific to these 3 values and pastes into the workbook and carries out
multiple calculations. When this is done I want it to save the file based on
3 cells and then go down to the next line in the array and repeat all of
these steps. With help from many posters on here I am so close. I will post
the code and then make some comments in the end.
Option Explicit
Private mcnToDatabase As Connection
Private mwksResults As Excel.Worksheet
Private Const STATE_FIPS_COL = 0
Private Const COMMODITY_COLUMN = 1
Private Const PRACTICE_COL = 2
Public Const dbpath = "D:\Profiles\cherring\Desktop\New Folder\Automateopt
updated!\DevelopIndexOut_no_Price_vol.mdb"
Private Const CS = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Mode=Share Deny None;Jet OLEDB:Engine Type=4;Data Source="
Private Const CLIENT_TAB = "CLIENT"
Private Const ALT_TAB = "ALT1"
Sub MainMacro1()
Call Run(dbpath)
End Sub
Public Sub Run(dbpath As String)
Dim lDataRow As Long
Dim lData As Long
Dim alData() As Long
Dim s As String
Dim r As String
Dim t As String
s = Replace(Worksheets("CountyYield").Range("A5").Text, " ", "")
r = Worksheets("CountyYield").Range("B5").Text
t = Worksheets("CountyYield").Range("C5").Text
'Set asData = info in Excel
For lDataRow = 0 To UBound(GetCurrentData, 1) - 1
alData = GetCurrentData
Main dbpath, alData(lDataRow, STATE_FIPS_COL), alData(lData,
COMMODITY_COLUMN), alData(lData, PRACTICE_COL)
'RunSolver
'Save as new workbook
'Next lDataRow
ActiveWorkbook.SaveAs Filename:="D:\Profiles\cherring\Desktop\states\" &
s & "_" & r & "_" & t & ".xls"
Next lDataRow
End Sub
Private Function GetCurrentData() As Long()
Dim alTemp() As Long
Dim iCol As Long
Dim iEnd As Long
Dim iRow As Long
Set mwksResults = Application.Worksheets("mwksResults")
iEnd = mwksResults.Range("A2").End(xlDown).Row
ReDim alTemp(iEnd - 2, 2)
For iRow = 2 To iEnd
For iCol = 1 To 3
alTemp(iRow - 2, iCol - 1) = mwksResults.Cells(iRow, iCol).Value
Next iCol
Next iRow
GetCurrentData = alTemp
Erase alTemp
End Function
Private Sub ConnectToDatabase(dbpath As String)
End Sub
Basically it starts out with Macro1() and then goes into the PublicSubRun.
When it does that I want it to go into the array and do what I explained
before. My problem is that it goes into the PrivateFunction, loops through
that a bunch of times, then does the macro. Then it repeats. My only problem
is it just keeps bringing up the same data from Access because it's not
actually looping through the array when I have it say next ldatarow. Does
anyone know how to fix this?