Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unprotect Excel sheet with Access 2003 VBA 2

Status
Not open for further replies.

vamoose

Programmer
Oct 16, 2005
320
MX
Hello, I am using MSAccess and Excel 2003 for this example. I am attempting to open, modify and print an Excel document from within inside Access VBA. The open and print functions are working just fine. I cannot seem to figure out how to unprotect the sheet so I can modify a cells color. I do not wish to save the changes, just print and close. Any ideas, thank you. This is the code so far:

Dim appexcel As Object: Set appexcel = CreateObject("Excel.Application")
appexcel.Workbooks.Open Path & FilenameSheet

Worksheets("Sheet1").Unprotect "sherman"
Worksheets("Sheet1").Range("D3:D12").Interior.ColorIndex = 41
Worksheets("Sheet1").Protect "sherman"

appexcel.Visible = False: appexcel.ActiveWindow.SelectedSheets.PrintOut Copies:=1

I get the error: Compile error. Sub or Function not defined with Worksheets highlighted.
 
Replace this:
Worksheets
with this (3 times):
appexcel.Worksheets

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Hi

Code like below

With appexcel.Workbooks(activeworkbook.Name)
.Worksheets("Sheet1").Unprotect "sherman"
.Worksheets("Sheet1").Range("D3:D12").Interior.ColorIndex = 41
.Worksheets("Sheet1").Protect "sherman"
End With

or pass workbook name as sting instead of activeworkbook.name

Stefen
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top