AllowEdit Range on excel spreadsheet via Access VBA

[peace77]

Hi All,
I am struggling a bit in Access VBA. I export files from Access vba and then open the file and do some formatting and then password protect that excel file. However I want the user to able to edit two columns. I try below code but it is not doing the job. It gives me runtime error 1004 Method ‘Columns’ of object’_Global failed. Please help


With xlApp
.Visible = False
.Workbooks.Open (outpath + outfile1)
.ActiveSheet.Protection.AllowEditRanges.Add Title:="myRange", Range:=Columns("O"), Password:="pass"
End with


Thanks,

Peace

 

[the_net_2.0]

peace,

If I were in your situation, here's what I would do:

open a new worksheet and start recording a macro. highlight the 2 columns you want to be editable, then open the 'format cells' dialog > protection tab. check all of the boxes in there and close the dialog. then go to the sheet protection and protect it. stop recording the macro and copy the code that excel produced in Module 1. Use the 'relative' portions of the code to do the same thing through access when you've got the worksheet open in Excel's instance.

I would assume that they error is comming from the file being protected. Similar to using an ADO query to look at an Excel sheet whereby the method won't let you open the source if it is password protected. It will simply throw an error.

Protecting sheets and even the whole workbook is typically just as good as password protecting the file. If you're doing a lot of work with vba, it will yield less headaches as well.

 

[peace77]

Thanks "the_net_2.0"

I tried your solution, but I still have the same error message. Also the excel spreadsheet is not protected when I run alloweditrange code.