Disable sheet protection on one file from another file

[KGee]

I need to access an Excel file from an external program in order to read data from one of the sheets in the Excel file. The file/sheet I need to access is protected so it is not accessible to the external program I am using. The file has code in the Workbook_Open and Workbook_BeforeClose events which hides and unhides the DATA sheet in using xlSheetVisible\xlSheetVeryHidden.

I want to write a macro in one file which will open the Excel file in question and save a copy of the file with the DATA sheet visible so my external program can access the information in the DATA sheet. Once it has read the information I will have the external program delete the copy of the file.

Can someone provide guidance on how to go about writing the macro to make a copy of the file with the DATA sheet unprotected? Also, the VBA in the file I need to open/copy is protected with a password which I can hard code into the macro if necessary.

Thanks

 

[taigovinda]

It could look *something like* this if you wanted code to run from an Excel file to open 'my protected file' and make a copy of the very hidden 'hidden data sheet' into a new book 'temp copy':

sub OpenCopyHiddenSheetClose()
dim wkBksrc as workbook, wkBkdest as workbook
set wkBksrc = workbooks.open("c:\documents and settings\my documents\my protected file")
with wkbksrc.sheets("hidden data sheet")
  .hidden = xlsheetvisible
  set wkbkdest = .copy
end with
wkbksrc.close false 'close without saving
wkbkdest.saveas "c:\documents and settings\my documents\temp copy" filetype:=-4143 '-4143 is to save as excel 2003; filetype only needed if running from >2003
wkbkdest.close true

end sub

I'm sure there are mistakes and/or typos in there, but you sound like you have the chops to make it work with just a rough idea

 

[KGee]

Thanks for the input...it was enough for me to get it working.

Sub copysheet()
  Dim wkbkSource As Workbook
 
  Set wkbkSource = Workbooks.Open("Protected File.xls")
 
  wkbkSource.Protect structure:=False, Windows:=False
  wkbkSource.Sheets("Sheet To Copy").Copy
 
  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs Filename:="New File.xls"
  Application.DisplayAlerts = True
  ActiveWorkbook.Close
 
  wkbkSource.Close False
End Sub