PDA

View Full Version : Solved: Copy a Hidden worksheet from a protected excel file and Save AS



ram117
11-11-2005, 04:07 AM
Hi everyBody :hi:


I wanted to copy a worksheet(Say Sheet1) from a password protected workbook and saveas another file(SAVEAS dialog box should appear), This workbook wiil be visible in the new file but it wiil be hidden in the source file. can anybody suggest me the VBA code for this.

Thanks and Regards

Ram

mvidas
11-11-2005, 12:33 PM
Hi Ram,

Try the followingSub CopyAHiddenWorksheetToNewBook()
Dim WS As Worksheet, WB As Workbook, vFile As String

Set WS = Sheets("Sheet3") 'the hidden sheet you want to copy

vFile = Application.GetSaveAsFilename(WS.Name & ".xls", _
"Excel Files,*.xls,All Files,*.*")
If LCase(vFile) = "false" Then Exit Sub
Set WB = Workbooks.Add(1)
WS.Copy After:=WB.Sheets(1)
Set WS = WB.Sheets(2)
WS.Visible = xlSheetVisible
Application.DisplayAlerts = False
WB.Sheets(1).Delete
Application.DisplayAlerts = True
WB.SaveAs vFile, xlWorkbookNormal
End SubShould do what you need
Matt

austenr
11-11-2005, 12:55 PM
Hey Matt,

Did I miss it or how did you get around the protection before you copy the sheet?

mvidas
11-11-2005, 01:00 PM
If it is the workbook protection found at Tools / Protection / Protect Workbook, it wouldn't make a difference. If it is the kind of protected workbook that needs a password to even view the file, Ram would have to include a line to open the workbook using that password, or just ask us and we can write that for him. If the password isn't known -- this is the wrong forum for that question.

Matt

austenr
11-11-2005, 01:02 PM
Sure. OK Thanks for clearing that up :thumb

mvidas
11-11-2005, 01:13 PM
I didn't try it when the hidden sheet was protected, but I can't imagine anyone would protect a hidden sheet :) It should work anyways, but not positive.
In any of my testing, aside from the password-protected workbook that cant be viewed without the PW, I couldn't find a way the protection mattered. I'm sure I didn't think of all options though

ram117
11-12-2005, 04:00 AM
Hi,

Mvidas you are right I protected the workbook and hidden the sheets, but the hidden sheet was not protected. thank you very much for giving the code and supportive.

Thanks and Regards

Ramana