kyprogrammer
Registered User.
- Local time
- Today, 04:38
- Joined
- Oct 5, 2016
- Messages
- 14
So, I have been stuck on this code for a few hours now. I know it's something simple and if I had a vacation and come back I would figure it out and end up smacking my head on my desk. However, the crux of the module is to open one excel file from an access database find a value and select the 100 cells toward the right of that selected cell.
Later those cell values will be loaded in an array and then the database will open a template to paste those values for a capability study based off those 100 entries.
Right now I am at the point of selecting those cells, but access keeps giving me the old "Run-time error '91': Object variable or with block not set".
It runs the first time fine then all other times it throws the error. I just need it to select the rows so I can get to the next step which is to load those selected cell values in that array. (suggestions to that point would be great as well)
Here is the full module so far
Option Explicit
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim myCellChar, myCellNum, myEndRange As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RNG As Range
Public Function AccessForm()
Dim myMonth, myYear, myRow, myCol As Integer
Dim myPartFile, myFolder, myFind, myFilePath As String
Dim myLocale As Variant
myMonth = Month(Form_frm_Cap_Study.txt_Begin)
myYear = Year(Form_frm_Cap_Study.txt_Begin)
myPartFile = Form_frm_Cap_Study.cbo_Part & ".xlsm"
myFolder = myYear & "-" & "0" & myMonth ' IF statement to come later for months that are double digits
myFind = Form_frm_Cap_Study.txt_Weld_Number
myFilePath = "Z:\Destruct Data\Data Graphs\Monthly Destruct Data" & myFolder & "" & myPartFile
Debug.Print myFilePath
Set Xl = New Excel.Application
Set XlBook = Xl.Workbooks.Open(myFilePath)
Set XlSheet = Xl.Sheets("Data")
'opens the excel sheet where the data is coming from and finds the cell in which the user inputs the weld number to search for
Xl.Application.Visible = True
Xl.Parent.Windows(1).Visible = True
XlSheet.Activate
Set RNG = XlSheet.Cells.Find(What:=myFind, lookin:=xlValues, Lookat:=xlWhole)
myLocale = RNG.Address
'finds the row and col number
myRow = RNG.Row
myCol = RNG.Column
'selects the found cell
If RNG Is Nothing Then
Else
RNG.Select
End If
'This section selects the data to the right of the selected cell and encompasses 100 cells for max data
ActiveCell.Offset(, 1).Resize(1, 100).Select
Set Xl = Nothing
Set XlBook = Nothing
End Function
Later those cell values will be loaded in an array and then the database will open a template to paste those values for a capability study based off those 100 entries.
Right now I am at the point of selecting those cells, but access keeps giving me the old "Run-time error '91': Object variable or with block not set".
It runs the first time fine then all other times it throws the error. I just need it to select the rows so I can get to the next step which is to load those selected cell values in that array. (suggestions to that point would be great as well)
Here is the full module so far
Option Explicit
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim myCellChar, myCellNum, myEndRange As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RNG As Range
Public Function AccessForm()
Dim myMonth, myYear, myRow, myCol As Integer
Dim myPartFile, myFolder, myFind, myFilePath As String
Dim myLocale As Variant
myMonth = Month(Form_frm_Cap_Study.txt_Begin)
myYear = Year(Form_frm_Cap_Study.txt_Begin)
myPartFile = Form_frm_Cap_Study.cbo_Part & ".xlsm"
myFolder = myYear & "-" & "0" & myMonth ' IF statement to come later for months that are double digits
myFind = Form_frm_Cap_Study.txt_Weld_Number
myFilePath = "Z:\Destruct Data\Data Graphs\Monthly Destruct Data" & myFolder & "" & myPartFile
Debug.Print myFilePath
Set Xl = New Excel.Application
Set XlBook = Xl.Workbooks.Open(myFilePath)
Set XlSheet = Xl.Sheets("Data")
'opens the excel sheet where the data is coming from and finds the cell in which the user inputs the weld number to search for
Xl.Application.Visible = True
Xl.Parent.Windows(1).Visible = True
XlSheet.Activate
Set RNG = XlSheet.Cells.Find(What:=myFind, lookin:=xlValues, Lookat:=xlWhole)
myLocale = RNG.Address
'finds the row and col number
myRow = RNG.Row
myCol = RNG.Column
'selects the found cell
If RNG Is Nothing Then
Else
RNG.Select
End If
'This section selects the data to the right of the selected cell and encompasses 100 cells for max data
ActiveCell.Offset(, 1).Resize(1, 100).Select
Set Xl = Nothing
Set XlBook = Nothing
End Function