Dim rng As Range
Set rng = Activeworkbook.sheets("...
'loop through range and pull out values
For i = 1 To rng.Columns.Count
strValueFromRange = rng(i).Value 'gets the value certain spot in range
Next
'YOUR CODE
appExcel.Workbooks.Open strFolderPath & strPath
appExcel.Visible = True
Dim rng As Range
Dim sht As Worksheet
'loop through each sheet
For a = 1 To ActiveWorkbook.Sheets.Count
With ActiveWorkbook.Sheets(a)
'set range
Set rng = Range("B10:D10")
'get value from range
For i = 1 To rng.Columns.Count
strValueFromRange = rng(i).Value
'execute SQL INSERT
Next
'set NEW range selection
Set rng = Range("B2:D2")
'get value from range
For i = 1 To rng.Columns.Count
strValueFromRange2 = rng(i).Value
'execute SQL INSERT
Next
End With
Next a
'YOUR CODE
appExcel.Workbooks.Open strFolderPath & strPath
appExcel.Visible = True
Dim rng As Range
Dim sht As Worksheet
'loop through each sheet
For a = 1 To ActiveWorkbook.Sheets.Count
With ActiveWorkbook.Sheets(a)
'set range
Set rng = .Range("B10:D10")
'get value from range
For i = 1 To rng.Columns.Count
strValueFromRange = rng(i).Value
'execute SQL INSERT
Next
'set NEW range selection
Set rng = .Range("B2:D2")
'get value from range
For i = 1 To rng.Columns.Count
strValueFromRange2 = rng(i).Value
'execute SQL INSERT
Next
End With
Next a
Public Function MYPREMLOAD2()
Dim strPath As String, strFolderPath As String
Dim appExcel As Excel.Application
Dim MyDB As DAO.Database, MyRS As DAO.Recordset
DoCmd.SetWarnings False
strsql = "Delete * From tblPolicyLoad;"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblPolicyLoad", dbOpenDynaset)
strFolderPath = "X:\Special Risk\miketesting\KARA\"
strPath = "X:\Special Risk\miketesting\KARA\*.xls"
strPath = Dir(strPath, vbNormal)
Set appExcel = CreateObject("Excel.Application")
Do While strPath <> ""
appExcel.Workbooks.Open strFolderPath & strPath
appExcel.Visible = True
With ActiveWorkbook.Sheets("test")
With MyRS
.AddNew
!Benefit_Period = UCase(.Range("C8").Value)
!EFFECTIVE_DATE = UCase(.Range("C9").Value)
!Filename = UCase(.Range("C10").Value)
.Update
End With
'execute SQL INSERT
End With
appExcel.ActiveWorkbook.Close
strPath = Dir
Loop
appExcel.Quit
Set appExcel = Nothing
MyRS.Close
Set MyRS = Nothing
MsgBox "This Process has completed!"
End Function
Public Function MYPREMLOAD2()
Dim strPath As String, strFolderPath As String
Dim appExcel As Excel.Application
Dim MyDB As DAO.Database, MyRS As DAO.Recordset
DoCmd.SetWarnings False
strsql = "Delete * From tblPolicyLoad;"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblPolicyLoad", dbOpenDynaset)
strFolderPath = "X:\Special Risk\miketesting\KARA\"
strPath = "X:\Special Risk\miketesting\KARA\*.xls"
strPath = Dir(strPath, vbNormal)
Set appExcel = CreateObject("Excel.Application")
Do While strPath <> ""
appExcel.Workbooks.Open strFolderPath & strPath
appExcel.Visible = True
With appExcel.ActiveWorkbook.Sheets("test")
With MyRS
.AddNew
!Benefit_Period = UCase(.Range("C8").Value)
!EFFECTIVE_DATE = UCase(.Range("C9").Value)
!Filename = UCase(.Range("C10").Value)
.Update
End With
'execute SQL INSERT
End With
appExcel.ActiveWorkbook.Close
strPath = Dir
Loop
appExcel.Quit
Set appExcel = Nothing
MyRS.Close
Set MyRS = Nothing
MsgBox "This Process has completed!"
End Function
Title | # Comments | Views | Activity |
---|---|---|---|
How can I modify this Excel worksheet with a script? | 15 | 43 | |
Excel 2016 VBA - userform and z-Order of controls | 11 | 33 | |
MS Excel "--" in Formula What is it doing? | 3 | 12 | |
Dynamic Excel Countdown Graphic | 21 | 18 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
17 Experts available now in Live!