Silverado
04-21-2010, 12:14 PM
I am a novice VBA user and I am having problems so I thought I would come to the forums.
I have an excel sheet with 12 worksheets (1 for each month) each sheet is identical in format to one another, only the data entered into the cells each month changes.
I want to take specific data from specific cells and load it into multiple access tables.
as in a table for Jan,feb,march..ect
currently I have my code working so I can pull data from the first sheet, into the table I created, but I do not know how to gather the same data from the other worksheets and distribute it to the appropriate tables in access.
This is the working part i have now. Can someone Please help ?
====================================
'Sub to open an Excel Spreadsheet
Public xl As New Excel.Application
Public xlw As Excel.Workbook
Public Sub OpenXLS(fName As String)
Set xlw = xl.Workbooks.Open(fName)
End Sub
=================================
Sub JamesSub()
Dim J As Integer
Dim DayNum As Single
Dim Hour As Single
Dim RawRead As Single
Dim Rawm3 As Single
Dim RawPRO As Single
Dim LvLON As Single
Dim LvLOFF As Single
Dim Dipped As Single
Dim PumpRead As Single
Dim PumpHrs As Single
OpenXLS ("C:\MW Monitoring ACCESS FORM\Code test\Sunderland.xls")
DoCmd.SetWarnings False
For J = 10 To 40
DayNum = xlw.Application.Cells(J, "a")
Hour = xlw.Application.Cells(J, "b")
RawRead = xlw.Application.Cells(J, "c")
Rawm3 = xlw.Application.Cells(J, "d")
RawPRO = xlw.Application.Cells(J, "e")
LvLON = xlw.Application.Cells(J, "h")
LvLOFF = xlw.Application.Cells(J, "i")
Dipped = xlw.Application.Cells(J, "j")
PumpRead = xlw.Application.Cells(J, "l")
PumpHrs = xlw.Application.Cells(J, "m")
DoCmd.RunSQL "INSERT INTO SunderlandJan (DayNum,Hour,RawRead,Rawm3,RawPRO,LvLON,LvLOFF,Dipped,PumpRead,PumpHrs) SELECT " & DayNum & "," & Hour & "," & RawRead & "," & Rawm3 & "," & RawPRO & "," & LvLON & "," & LvLOFF & "," & Dipped & "," & PumpRead & "," & PumpHrs & ""
Next J
DoCmd.SetWarnings True
MsgBox "Complete!"
End Sub
I have an excel sheet with 12 worksheets (1 for each month) each sheet is identical in format to one another, only the data entered into the cells each month changes.
I want to take specific data from specific cells and load it into multiple access tables.
as in a table for Jan,feb,march..ect
currently I have my code working so I can pull data from the first sheet, into the table I created, but I do not know how to gather the same data from the other worksheets and distribute it to the appropriate tables in access.
This is the working part i have now. Can someone Please help ?
====================================
'Sub to open an Excel Spreadsheet
Public xl As New Excel.Application
Public xlw As Excel.Workbook
Public Sub OpenXLS(fName As String)
Set xlw = xl.Workbooks.Open(fName)
End Sub
=================================
Sub JamesSub()
Dim J As Integer
Dim DayNum As Single
Dim Hour As Single
Dim RawRead As Single
Dim Rawm3 As Single
Dim RawPRO As Single
Dim LvLON As Single
Dim LvLOFF As Single
Dim Dipped As Single
Dim PumpRead As Single
Dim PumpHrs As Single
OpenXLS ("C:\MW Monitoring ACCESS FORM\Code test\Sunderland.xls")
DoCmd.SetWarnings False
For J = 10 To 40
DayNum = xlw.Application.Cells(J, "a")
Hour = xlw.Application.Cells(J, "b")
RawRead = xlw.Application.Cells(J, "c")
Rawm3 = xlw.Application.Cells(J, "d")
RawPRO = xlw.Application.Cells(J, "e")
LvLON = xlw.Application.Cells(J, "h")
LvLOFF = xlw.Application.Cells(J, "i")
Dipped = xlw.Application.Cells(J, "j")
PumpRead = xlw.Application.Cells(J, "l")
PumpHrs = xlw.Application.Cells(J, "m")
DoCmd.RunSQL "INSERT INTO SunderlandJan (DayNum,Hour,RawRead,Rawm3,RawPRO,LvLON,LvLOFF,Dipped,PumpRead,PumpHrs) SELECT " & DayNum & "," & Hour & "," & RawRead & "," & Rawm3 & "," & RawPRO & "," & LvLON & "," & LvLOFF & "," & Dipped & "," & PumpRead & "," & PumpHrs & ""
Next J
DoCmd.SetWarnings True
MsgBox "Complete!"
End Sub