your example should save the file as:
"\\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 folder\TEST FOLDER"
then you should use:
"\\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 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>
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
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
Regards
Ashish Koul
http://www.excelvbamacros.com/
PBefore 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
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
--
----------------------------------------------------------------------------------
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
