none
MS Access VBA Send to Excel Protected Sheet. RRS feed

  • Question

  •  Hello Everyone,

                I am using Access to send data to Excel sheet using VBA it is working great, Yet it seems that I have run into a problem. We now need the Excel sheet to be Protected. And now I get Run-time error 424 Object Required. This is my Code below which works fine if the sheet is not protected. 

    Private Sub Command84_Click()

    Dim mysheet As Object, myfield As Variant, xlApp As Object

    ' Set object variable equal to the OLE object.
    Set xlApp = CreateObject("Excel.Application")


    ' Set mysheet = GetObject("\\FF:\DOCUMENTS\QUOTES\Quotes 2015\2015 AAAA master quote sheet with 12 ROWS.xls", "excel.sheet").
    Set mysheet = xlApp.Workbooks.Open("\\C:\2015 New Fire-Rated Master Quote Sheet with 16 ROWS.xlsx").Sheets(1)
    ActiveSheet.Protect Password:="fire rated", UserInterfaceOnly:=True
    ' Put the value of the ToExcel text box into the cell on the

    myfield = Me!Reference
    mysheet.cells(3, 10).Value = myfield
    myfield = Me!Address1
    mysheet.cells(3, 3).Value = myfield
    myfield = Me!company
    mysheet.cells(2, 3).Value = myfield
    myfield = Me!City
    mysheet.cells(4, 3).Value = myfield
    myfield = Me!State
    mysheet.cells(4, 4).Value = myfield
    myfield = Me!Zip
    mysheet.cells(4, 5).Value = myfield
    myfield = Me!Contact
    mysheet.cells(5, 3).Value = myfield
    myfield = Me!EMail
    mysheet.cells(7, 9).Value = myfield
    myfield = Me!Phone
    mysheet.cells(6, 3).Value = myfield
    myfield = Me!Fax
    mysheet.cells(7, 3).Value = myfield
    myfield = Me!Quote
    mysheet.cells(1, 10).Value = myfield
    myfield = Me!Date
    mysheet.cells(2, 10).Value = myfield


    ' Set the Visible property of the sheet to True, save the
    ' sheet, and quit Microsoft Excel.
    mysheet.Application.Windows("2015 New Fire-Rated Master Quote Sheet with 16 ROWS.xlsx").Visible = True
    mysheet.Application.activeworkbook.Save
    mysheet.Application.activeworkbook.Close
    xlApp.Quit

    ' Clear the object variable.
    Set mysheet = Nothing

    End Sub

    Thursday, July 30, 2015 7:36 PM

Answers

  • Hi Chrisdata,

    Based on your original error message, it seems that your excel file has been opened by other threads. Have you interop with the file in other methods? I made a simple test with your code, and it worked correctly.

    Sub OpenExcelTest()
        Dim mysheet As Object, myfield As Variant, xlApp As Excel.Application
    
         ' Set object variable equal to the OLE object.
         Set xlApp = CreateObject("Excel.Application")
    
    
         ' Set mysheet = GetObject("\\FF:\DOCUMENTS\QUOTES\Quotes 2015\2015 AAAA master quote sheet with 12 ROWS.xls", "excel.sheet").
         Set mysheet = xlApp.Workbooks.Open("\\C:\Users\v-tazho\Desktop\Test.xlsx").Sheets(1)
         mysheet.Protect Password:="fire rated", UserInterfaceOnly:=True
         ' Put the value of the ToExcel text box into the cell on the
         Debug.Print mysheet.Application.ActiveWorkbook.ReadOnly
         mysheet.Cells(3, 10).Value = "myfield"
         mysheet.Application.Windows("Test.xlsx").Visible = True
         mysheet.Application.ActiveWorkbook.Save
         mysheet.Application.ActiveWorkbook.Close
         xlApp.Quit
         'Set xlApp = Nothing
         ' Clear the object variable.
         Set mysheet = Nothing
    End Sub

    In the code above, I replace “ActiveSheet” with “mysheet”, it generated error when I test the method in second time. You could make a test with my code to check the “Debug.Print mysheet.Application.ActiveWorkbook.ReadOnly” value.

    Best Regards,

    Edward

               

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Chrisdata Wednesday, August 5, 2015 12:54 PM
    Sunday, August 2, 2015 6:58 AM

All replies

  • Hi,

    Which line of code is getting the error? Have you tried using the Unprotect method? Just curious...

    Thursday, July 30, 2015 7:42 PM
  • Hi Chris,

    Your sheet needs to be unprotected before you make your changes and then protected again once finished.


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Thursday, July 30, 2015 9:59 PM
  • This line is giving me the error ( ActiveSheet.Protect Password:="fire rated", UserInterfaceOnly:=True ) 

    Rune Time Error 1004

    Friday, July 31, 2015 12:10 PM
  • Hi Chris,

    I think you may be missing our point. Since the sheet was already protected, you may have to unprotect it first (see the link I provided earlier for how to do that). Then let us know if that doesn't work after you've tried it. Thanks.

    Friday, July 31, 2015 2:28 PM
  •  Hi Using ActiveWorkbook.Unprotect I get the same error 
    Friday, July 31, 2015 3:35 PM
  • Hi Chris,

    Okay, thanks for trying. But let's back up a bit. The error message said that the file is "read-only." Have you verified that it is not? Also, was the worksheet protected with a password? If so, have you tried including it in the Unprotect method? For example:

    ActiveWorkbook.Unprotect(password)

    Friday, July 31, 2015 3:46 PM
  •  Yes the sheet has a password, No the file is not read only, yes I tried to put the password into the formula. 
    • Edited by Chrisdata Friday, July 31, 2015 7:37 PM
    Friday, July 31, 2015 5:11 PM
  • Hi Chris,

    I don't do much programming in Excel, so I'm not sure if this will make any difference, but could you please try it this way? Thanks.

    ActiveWorkbook.Unprotect(fire rated)

    or maybe like this:

    ActiveWorkbook.Unprotect("fire rated")

    I'm just trying to guess if you needed the parenthesis (brackets) in the syntax. Cheers!

    Friday, July 31, 2015 8:46 PM
  • Hi Chrisdata,

    Based on your original error message, it seems that your excel file has been opened by other threads. Have you interop with the file in other methods? I made a simple test with your code, and it worked correctly.

    Sub OpenExcelTest()
        Dim mysheet As Object, myfield As Variant, xlApp As Excel.Application
    
         ' Set object variable equal to the OLE object.
         Set xlApp = CreateObject("Excel.Application")
    
    
         ' Set mysheet = GetObject("\\FF:\DOCUMENTS\QUOTES\Quotes 2015\2015 AAAA master quote sheet with 12 ROWS.xls", "excel.sheet").
         Set mysheet = xlApp.Workbooks.Open("\\C:\Users\v-tazho\Desktop\Test.xlsx").Sheets(1)
         mysheet.Protect Password:="fire rated", UserInterfaceOnly:=True
         ' Put the value of the ToExcel text box into the cell on the
         Debug.Print mysheet.Application.ActiveWorkbook.ReadOnly
         mysheet.Cells(3, 10).Value = "myfield"
         mysheet.Application.Windows("Test.xlsx").Visible = True
         mysheet.Application.ActiveWorkbook.Save
         mysheet.Application.ActiveWorkbook.Close
         xlApp.Quit
         'Set xlApp = Nothing
         ' Clear the object variable.
         Set mysheet = Nothing
    End Sub

    In the code above, I replace “ActiveSheet” with “mysheet”, it generated error when I test the method in second time. You could make a test with my code to check the “Debug.Print mysheet.Application.ActiveWorkbook.ReadOnly” value.

    Best Regards,

    Edward

               

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Chrisdata Wednesday, August 5, 2015 12:54 PM
    Sunday, August 2, 2015 6:58 AM
  •  Thanks to everyone,  Yes I have it working, Your last reply helped me to get it together. Again Many thanks to everyone. 
    Wednesday, August 5, 2015 12:54 PM