Can't Modify Excel sheet from Access

[steebo81]

I have software for a scale that uploads and downloads data to it. The data can be transferred to an Excel spreadsheet, which I've been using to create Macros to pretty the information up for the end user. I've been adding data to access to try and make this process simple enough for the user to do themselves, but I have only been learning code via recorded macros in Excel, google, and what I remember from a class I took over a decade ago. I have a simple form that will (when I get it right) let the user change the animal number for every PLU in the spreadsheet. There is a frame with 5 option buttons (cow, hog, sheep, lamb, & goat), a text box for animal number, and a command button to change the Excel file. The command button executes the code below. I have tried many different variations and gotten many different errors, but I can't get it right. There is probably a good handful of broken things in this, so I apologize in advance. Currently, my error is "Compile Error: Method or data member not found". Any help would be super appreciated.

Private Sub cmdModify_Click()


Dim X As Integer


Dim strAnimalNumber As String


Dim PLUPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Get animal number from form text box
txtAnimalNumber.SetFocus
strAnimalNumber = txtAnimalNumber.Text

'Set path based on frame option value
If fraMeatType.Value = 1 Then
PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Cow.xlsx"
ElseIf fraMeatType.Value = 2 Then
PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Hog.xlsx"
ElseIf fraMeatType.Value = 3 Then
PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Lamb.xlsx"
ElseIf fraMeatType.Value = 4 Then
PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Sheep.xlsx"
ElseIf fraMeatType.Value = 5 Then
PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Goat.xlsx"
End If

'Open Excel and correct workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(PLUPath)

'Show workbook
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define the sheet in the Workbook as XlSheet
Set XlSheet = XlBook.Worksheets(1)

'Count rows
numrows = XlSheet.Range("A2", XlSheet.Range("A2").End(xlDown)).Rows.Count

'Focus needs to be here for loop start
XlSheet.Range("B2").Select

For X = 1 To numrows

XlSheet.ActiveCell.Text = strAnimalNumber 'Set animal number on PLU
XlSheet.ActiveCell.Offset(1, 0).Select 'Go to next cell down

Next

'Save workbook and quit Excel
Xl.ActiveWorkbook.Save
Xl.ActiveWorkbook.Close
Xl.Quit

End Sub

 

[alansidman]

When you get the error and click on the debug button, which line of your code is highlighted?

 

[steebo81]

When you get the error and click on the debug button, which line of your code is highlighted?

'Highlighted in Yellow
Private Sub cmdModify_Click()

'and the section ".ActiveCell" of the following line in the For Loop has also been automatically selected
XlSheet.ActiveCell.Text = strAnimalNumber

Thanks!

 

[Norie]

There is no ActiveCell property for worksheets.

When automating Excel from another application you shouldn't use Select/Activate/ActiveCell/ActiveSheet etc.

Try this amended code.

Private Sub cmdModify_Click()
Dim X As Long
Dim numrows As Long
Dim strAnimalNumber As String
Dim PLUPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim XLRange As Excel.Range

    'Get animal number from form text box
    txtAnimalNumber.SetFocus
    strAnimalNumber = txtAnimalNumber.Text

    'Set path based on frame option value
    If fraMeatType.Value = 1 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Cow.xlsx"
    ElseIf fraMeatType.Value = 2 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Hog.xlsx"
    ElseIf fraMeatType.Value = 3 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Lamb.xlsx"
    ElseIf fraMeatType.Value = 4 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Sheep.xlsx"
    ElseIf fraMeatType.Value = 5 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Goat.xlsx"
    End If

    'Open Excel and correct workbook
    Set Xl = CreateObject("Excel.Application")
    Set XlBook = GetObject(PLUPath)

    'Show workbook
    Xl.Visible = True
    XlBook.Windows(1).Visible = True

    'Define the sheet in the Workbook as XlSheet
    Set XlSheet = XlBook.Worksheets(1)

    'Count rows
    numrows = XlSheet.Range("A2", XlSheet.Range("A" & XlSheet.Rows.Count).End(xlUp)).Rows.Count

    'Focus needs to be here for loop start
    Set XLRange = XlSheet.Range("B2")

    For X = 1 To numrows

        XLRange.Value = strAnimalNumber    'Set animal number on PLU
        Set XLRange = XLRange.Offset(1)

    Next X

    'Save workbook and quit Excel
    XlBook.Close SaveChanges:=True
    Xl.Quit

End Sub

By the way, if you just want to put the same string value in a range of cells you could do it without looping.

Something like this.

XlSheet.Range("A2", XlSheet.Range("A" & XlSheet.Rows.Count).End(xlUp)).Offset(,1).Value = strAnimalNumber

PS Text is a read only property.

 

[steebo81]

Also, I see in your signature about using code tags. I will do that in the future. Sorry, new to the forum posting

 

[steebo81]

There is no ActiveCell property for worksheets.

When automating Excel from another application you shouldn't use Select/Activate/ActiveCell/ActiveSheet etc.

Try this amended code.

Private Sub cmdModify_Click()
Dim X As Long
Dim numrows As Long
Dim strAnimalNumber As String
Dim PLUPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim XLRange As Excel.Range

    'Get animal number from form text box
    txtAnimalNumber.SetFocus
    strAnimalNumber = txtAnimalNumber.Text

    'Set path based on frame option value
    If fraMeatType.Value = 1 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Cow.xlsx"
    ElseIf fraMeatType.Value = 2 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Hog.xlsx"
    ElseIf fraMeatType.Value = 3 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Lamb.xlsx"
    ElseIf fraMeatType.Value = 4 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Sheep.xlsx"
    ElseIf fraMeatType.Value = 5 Then
        PLUPath = CurrentProject.Path & "\GSY\Access\Practice\Goat.xlsx"
    End If

    'Open Excel and correct workbook
    Set Xl = CreateObject("Excel.Application")
    Set XlBook = GetObject(PLUPath)

    'Show workbook
    Xl.Visible = True
    XlBook.Windows(1).Visible = True

    'Define the sheet in the Workbook as XlSheet
    Set XlSheet = XlBook.Worksheets(1)

    'Count rows
    numrows = XlSheet.Range("A2", XlSheet.Range("A" & XlSheet.Rows.Count).End(xlUp)).Rows.Count

    'Focus needs to be here for loop start
    Set XLRange = XlSheet.Range("B2")

    For X = 1 To numrows

        XLRange.Value = strAnimalNumber    'Set animal number on PLU
        Set XLRange = XLRange.Offset(1)

    Next X

    'Save workbook and quit Excel
    XlBook.Close SaveChanges:=True
    Xl.Quit

End Sub

By the way, if you just want to put the same string value in a range of cells you could do it without looping.

Something like this.

XlSheet.Range("A2", XlSheet.Range("A" & XlSheet.Rows.Count).End(xlUp)).Offset(,1).Value = strAnimalNumber

PS Text is a read only property.

Thank you so much! I only got one error, but the code assumed the file was already open. Apologies, I should have mentioned that. But it works! Also, Norie. I applied your suggestion for replacing my loop. That was also really cool, you saved me a few lines. Hopefully, I can get some more practice in and save more on my own in future projects.

Again, thanks so much for the help. Everything worked great!