Results 1 to 8 of 8

Thread: [RESOLVED] Updating Excel Spreadsheet Issue

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2021
    Posts
    4

    Resolved [RESOLVED] Updating Excel Spreadsheet Issue

    tl;dr: I'm trying to export data from certain Label.Captions into a spreadsheet by finding the next blank row/column and inputting the data automatically.

    Hello all! I'm new to the forum and super stoked that others still use VB6. I realize there's new versions out, but VB6 is the one I used ~12 years ago in high school, and I haven't actually programmed since then and I'm REALLY out of touch. (Like, very out of touch. Required google to do the simplest tasks)

    Okay, so here's the story. I'm making a program that assigns point values to different RPG stats for a board game I'm making. I want to export those results to an excel spreadsheet without overwriting the previous results. I'm doing it this way to just make it easier on myself when I use the spreadsheet & photoshop to export prototype cards.

    I'm mostly getting Object Required errors and it's driving me bonkers. I have one Button that opens the actual spreadsheet itself, and another button the user (AKA: Me) would press to update the spreadsheet with the new information.

    Here is the code for the button to open the spreadsheet:

    Code:
    Private Sub OpenSheet_Click()
    Dim oXLApp As Excel.Application       'Declare the object variables
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    
      Set oXLApp = New Excel.Application  'Create a new instance of Excel
     
      Set oXLBook = oXLApp.Workbooks.Open("c:\Users\EmoKi\Documents\MyXL.xlsx") 'Open an existing workbook
      Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
      
      oXLApp.Visible = True               'Show it to the user
     
      Set oXLBook = Nothing               'Disconnect from Excel (let the user take over)
      Set oXLApp = Nothing
      Set oXLSheet = Nothing
    End Sub
    Here is the code to update the spreadsheet:
    Code:
    Private Sub UpdateSheet_Click()
    Dim oXLApp As Excel.Application         'Declare the object variables
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    Dim HPNumber As String
      'I have the next few lines commented out because I dont need
      'a new instance or workbook opened.
      'Set oXLApp = New Excel.Application    'Create a new instance of Excel
      'Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook
      Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
    
         LastRow = oXLSheet.UsedRange.Rows.Offset(1).Select
         LastCol = oXLSheet.UsedRange.Columns.Offset(1).Select
    
    
    
    
    oXLApp.Visible = True                'Show it to the user
      Set oXLSheet = Nothing               'Disconnect from all Excel objects (let the user take over)
      Set oXLBook = Nothing
      Set oXLApp = Nothing
    
    '######################################################################
    answer = MsgBox("Save this creation in the spreadsheet?", vbExclamation + vbYesNo, "Confirm")
    If answer = vbYes Then
    
    oXLSheet.Cells(LastRow, LastCol).Value = HPNumber
    
    Else
    End If
    End Sub
    What am I doing wrong? It keeps asking for an object, and I thought I told it to use the currently opened worksheet, find the next row and column that are completely empty, and start transferring data. The name of the stats (in order on the spreadsheet) are:
    Name, Type, HP, MP, MATK, RATK, MaATK, MDEF, RDEF, MaDEF, Immune, Weakness, Hind/Fly/Climb, Damage Ability

    Name and Type are both Text Boxes (user inputs the data themselves)
    HP, MP, MATK, RATK, MaATK, MDEF, RDEF, MaDEF are all labels
    Immune, Weakness, Hind/Fly/Climb, Damage Ability are all checkboxes (Will come up "YES" in the speadsheet if checked, "NO" if unchecked)

    Screenshot posted below as well in case anymore information is needed. It looks super generic but it'll only be used by me so I'm not too worried about it haha.

    Name:  PointBuilderScreenshot.jpg
Views: 133
Size:  47.5 KB

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,352

    Re: Updating Excel Spreadsheet Issue

    after you open the excel workbook, you set the variables to nothing, you need to keep the variables in scope, currently they are dimensioned at procedure level, in both procedures, some need to be dimensioned ONLY at module level (top of the module in the general section) so that they remain in scope and are valid in all procedures, but do not set them to nothing until you are finished with the workbook or closing your app, just figure which ones you need to keep in scope, use only the minimum required, probably oxlapp and oxlbook, the rest should stay at procedure level
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2021
    Posts
    4

    Re: Updating Excel Spreadsheet Issue

    Quote Originally Posted by westconn1 View Post
    after you open the excel workbook, you set the variables to nothing, you need to keep the variables in scope, currently they are dimensioned at procedure level, in both procedures, some need to be dimensioned ONLY at module level (top of the module in the general section) so that they remain in scope and are valid in all procedures, but do not set them to nothing until you are finished with the workbook or closing your app, just figure which ones you need to keep in scope, use only the minimum required, probably oxlapp and oxlbook, the rest should stay at procedure level
    So I should put my main DIMs into the Form Load section? Below is my altered code. I'm still receiving error "run-time error 424 Object Required" on the line "Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet" in the "UpdateSheet_Click" button, but if you remove it or comment it out, you get it again for the lines "LastRow = .." and "LastCol = ..."

    Form_Load:
    Code:
    Private Sub Form_Load()
    Dim oXLApp As Excel.Application       'Declare the object variables
    Dim oXLBook As Excel.Workbook
    End Sub
    OpenSheet_Click:
    Code:
    Private Sub OpenSheet_Click()
      Set oXLApp = New Excel.Application  'Create a new instance of Excel
      Set oXLBook = oXLApp.Workbooks.Open("c:\Users\EmoKi\Documents\MyXL.xlsx") 'Open an existing workbook
      oXLApp.Visible = True               'Show it to the user
    End Sub
    UpdateSheet_Click:
    Code:
    Private Sub UpdateSheet_Click()
    Dim oXLSheet As Excel.Worksheet
    Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
    Dim HPNumber As String
    
    
         LastRow = oXLSheet.UsedRange.Rows.Offset(1).Select
         LastCol = oXLSheet.UsedRange.Columns.Offset(1).Select
    
    answer = MsgBox("Save this creation in the spreadsheet?", vbExclamation + vbYesNo, "Confirm")
    If answer = vbYes Then
    
    oXLSheet.Cells(LastRow, LastCol).Value = HPNumber
    
    Else
    End If
    End Sub
    Last edited by Emo Kills Best; Mar 10th, 2021 at 06:35 PM.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,352

    Re: Updating Excel Spreadsheet Issue

    So I should put my main DIMs into the Form Load section?
    No
    at module level (top of the module in the general section)
    Code:
    Dim oXLApp As Excel.Application       'Declare the object variables
    Dim oXLBook As Excel.Workbook
    Private Sub Form_Load()
    
    End Sub
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2021
    Posts
    4

    Re: Updating Excel Spreadsheet Issue

    Quote Originally Posted by westconn1 View Post
    No

    Code:
    Dim oXLApp As Excel.Application       'Declare the object variables
    Dim oXLBook As Excel.Workbook
    Private Sub Form_Load()
    
    End Sub
    Okay, I've done that. I tried running it and when I clicked Update Sheet and got "Run-Time ereror '424' Object Required." Clicking debug highlights the line
    Code:
    Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
    in the Update Form button. Removing that line gives "Run-time error '91': Object variable or With block variable not set." Clicking debug highlights the line
    Code:
    LastRow = oXLSheet.UsedRange.Rows.Offset(1).Select
    and again for the LastCol if you remove the LastRow line.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,352

    Re: Updating Excel Spreadsheet Issue

    you have to run the opensheet procedure before updatesheet so that the oxlapp and oxlbook need to be set first
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2021
    Posts
    4

    Re: Updating Excel Spreadsheet Issue

    Quote Originally Posted by westconn1 View Post
    you have to run the opensheet procedure before updatesheet so that the oxlapp and oxlbook need to be set first
    That's how I always tested it. I figured the "Sets" needed to be declared before you tried saving, otherwise it wouldn't know where to save to.

    I ended up figuring it out, with the help of a really old youtube video and a few other articles.

    In the main form, I DIMed the following:
    Code:
    Private Sub Form_Load()
    Dim oXLApp As Excel.Application       'Declare the object variables
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    
    
    End Sub
    This is the code for my Open Excel Sheet button:
    Code:
    Private Sub OpenSheet_Click()
    If OpenSheet.Caption = "Open Excel Sheet" Then
        Set oXLApp = New Excel.Application  'Create a new instance of Excel
        Set oXLBook = oXLApp.Workbooks.Open("c:\Users\EmoKi\Documents\MyXL.xlsx") 'Open an existing workbook
        Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet
        Set emptyrow = Range("A1")
        oXLApp.Visible = True               'Show it to the user
        OpenSheet.Caption = "DON'T FORGET TO SAVE!"
    
    Else
        Answer = MsgBox("Sheet Already Opened.", vbExclamation + vbOKOnly, "Sheet Already in Use")
    End If
    End Sub
    and this is the code for my Update Sheet button:
    Code:
    Private Sub UpdateSheet_Click()
    'Checks for next empty row
    emptyrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        'Inputs each stat into the correct box.
        Cells(emptyrow, 1).Value = NameBox.Text
        Cells(emptyrow, 2).Value = TypeBox.Text
        Cells(emptyrow, 3).Value = HPNumber.Caption
        Cells(emptyrow, 4).Value = MPNumber.Caption
        Cells(emptyrow, 5).Value = MeleeATKNumber.Caption
        Cells(emptyrow, 6).Value = RangedATKNumber.Caption
        Cells(emptyrow, 7).Value = MagicATKNumber.Caption
        Cells(emptyrow, 8).Value = MeleeDEFNumber.Caption
        Cells(emptyrow, 9).Value = RangedDEFNumber.Caption
        Cells(emptyrow, 10).Value = MagicDEFNumber.Caption
    
    'Checks if the creature has an immunity.
    If ImmunitiesCheck.Value = 1 Then
        Cells(emptyrow, 11).Value = "YES"
        Else
        Cells(emptyrow, 11).Value = "NO"
    End If
    
    'Checks if the creature has a weakness
    If WeaknessCheck.Value = 1 Then
        Cells(emptyrow, 12).Value = "YES"
        Else
        Cells(emptyrow, 12).Value = "NO"
    End If
    
    'Checks if the creature has a hinderance ability, can fly, or climb
    If HindFlyClimbCheck.Value = 1 Then
        Cells(emptyrow, 13).Value = "YES"
        Else
        Cells(emptyrow, 13).Value = "NO"
    End If
    
    'Checks if the creature has a special damaging ability
    If DamageAbilityCheck.Value = 1 Then
        Cells(emptyrow, 14).Value = "YES"
        Else
        Cells(emptyrow, 14).Value = "NO"
    End If
    
    End Sub
    Thanks for the attempted help, I do appreciate it.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,352

    Re: [RESOLVED] Updating Excel Spreadsheet Issue

    Private Sub Form_Load()
    Dim oXLApp As Excel.Application 'Declare the object variables
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet


    End Sub
    these object variables are dimensioned within a procedure and are only in scope during the life of that procedure

    the variables in opensheet are not dimensioned at all, if you put option explicit at the top of your module you will get errors for undefined variables

    if this works at all i am really surprised, and it would be prone to failure even if there are no errors
    cells are not an object of vb6, while they would work in excel vba referring to cells in the currently active sheet. i do not see it should work at all in vb6

    however if you are happy with it, all is good
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width