Paul and Ashish, You guys have been terrific - thank you for ALL your help!!!
Sincerely, Kent ________________________________ From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Paul Schreiner Sent: September 28, 2011 2:46 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Hidden Columns and Save Location your example should save the file as: "\\server8\cpd\CompReserve\kents<file:///\\server8\cpd\CompReserve\kents> folder\TEST FOLDER.xls" if you're wanting to save it with the current name in the folder called: "\\server8\cpd\CompReserve\kents<file:///\\server8\cpd\CompReserve\kents> folder\TEST FOLDER" then you should use: "\\server8\cpd\CompReserve\kents<file:///\\server8\cpd\CompReserve\kents> folder\TEST FOLDER\" & wkb.name (note the "\" at the end of the folder name.) If you REALLY want to check to make sure it's working the way you want it, use a variable to build the filename: fName = "\\server8\cpd\CompReserve\kents<file:///\\server8\cpd\CompReserve\kents> folder\TEST FOLDER\" & wkb.name then wkb.saveas fName Paul ----------------------------------------- "Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can." - John Wesley ----------------------------------------- ________________________________ From: "Boucher, Kent C" <kent.bouc...@tbs-sct.gc.ca> To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> Sent: Wed, September 28, 2011 2:31:59 PM Subject: RE: $$Excel-Macros$$ Hidden Columns and Save Location Hi Ashish, Thank you for getting back to me so quickly. I managed to get the columns and rows unhidden thanks to your advice, however, I am unable to change the saving location - it keeps saving to the original folder and not to the destination folder ("\\server8\cpd\CompReserve\kents folder\TEST FOLDER" & ".xls")? Here is the code I have put in place that doesn't seem to be working: With wkb.VBProject.VBComponents("Thisworkbook").CodeModule .DeleteLines 1, .CountOfLines End With MsgBox wkb.Path wkb.SaveAs "\\server8\cpd\CompReserve\kents folder\TEST FOLDER" & ".xls" Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub ________________________________ From: excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com ] On Behalf Of ashish koul Sent: September 28, 2011 12:22 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Hidden Columns and Save Location add For Each wk In wkb.Sheets wk.select Cells.EntireRow.Hidden = False Cells.EntireColumn.Hidden = False and replace wkb.Path & "/" in wkb.SaveAs wkb.Path & "/" & Left(wkb.Name, InStrRev(wkb.Name, "")) & ".xls" with ur path "c:\" etc On Wed, Sep 28, 2011 at 9:11 PM, HarryP Knuckles <kent.bouc...@tbs-sct.gc.ca<mailto:kent.bouc...@tbs-sct.gc.ca>> wrote: Good morning guys, You guys have been super helpful! I have a macro that is working well but would like to do two things with it. 1) I would like to have all the columns and rows automatically unhidden for each worksheet. 2) I would like the save location changed to: 'EMC-SNAS (Server7)' \ Excel Workbook to Rename\ I have attached the code of my macro to help make sense of my questions. Any help would be greatly appreciated!! Regards, Kent ______________________________________________________________________________________ Sub test() Application.DisplayAlerts = False Application.ScreenUpdating = False Dim wkb As Workbook Dim wk As Worksheet Dim VBProj As Object flpth = Application.GetOpenFilename("Excel File (*.xls*)," & "*xls*") On Error Resume Next Set wkb = Workbooks.Open(flpth, Password:="apple") On Error Resume Next Set wkb = Workbooks.Open(flpth) wkb.Unprotect "apple" For Each wk In wkb.Sheets wk.Unprotect "apple" Next wk Application.Wait (Now + TimeValue("0:00:5")) ' code to open vba editor written by NateO FROM Mr Excel Set VBProj = ActiveWorkbook.VBProject Application.VBE.MainWindow.Visible = True 'open VB Application.SendKeys "%{f11}" Application.SendKeys "apple", True Application.SendKeys "~", True VBProj.VBE.SelectedVBComponent.Activate ' '' For Each wk In wkb.Sheets '' With wkb.VBProject.VBComponents(wk.CodeName).CodeModule '' .DeleteLines 1, .CountOfLines '' End With ' Next wk With wkb.VBProject.VBComponents("Thisworkbook").CodeModule .DeleteLines 1, .CountOfLines End With MsgBox wkb.Path wkb.SaveAs wkb.Path & "/" & Left(wkb.Name, InStrRev(wkb.Name, "")) & ".xls" Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub __________________________________________________________________________________________ -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com<mailto:excel-macros@googlegroups.com> <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul http://www.excelvbamacros.com/ P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com<http://www.excel-macros.blogspot.com/> 4. Learn VBA Macros at http://www.quickvba.blogspot.com<http://www.quickvba.blogspot.com/> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com<http://exceldailytip.blogspot.com/> To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com<http://www.excel-macros.blogspot.com/> 4. Learn VBA Macros at http://www.quickvba.blogspot.com<http://www.quickvba.blogspot.com/> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com<http://exceldailytip.blogspot.com/> To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
