rinser
05-29-2009, 01:24 AM
Hi everybody,
I was working on some code that would create a new folder in My Documents and within that folder export the current worksheet and save it there with a particular name. After doing so I need it to return to the original worksheet and clear contents.
I would also need to export to the new sheet only contents of the cells without any macros or buttons.
If someone has any idea what is wrong with my code please help!
Dim strFilename, strDirname, strPathname, strDefpath As String
Dim wbSource, wbDest As Workbook
strDirname = "Audit Tool" & " " & Format(Now(), "yyyymmdd hh-mm")
strFilename = "Check" & " " & Format(Now(), "yyyymmdd")
strDefpath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\"
MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "\" & strFilename
Set wbSource = ActiveWorkbook
ActiveSheet.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs Filename:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Set wbSource = ActiveWorkbook
ActiveSheet.Cells.ClearContents
ActiveSheet.Cells.ClearFormats
Application.ScreenUpdating = True
I also posted this topic here:
http://www.excelforum.com/excel-programming/685350-create-folder-and-export-sheet-not-working.html
I was working on some code that would create a new folder in My Documents and within that folder export the current worksheet and save it there with a particular name. After doing so I need it to return to the original worksheet and clear contents.
I would also need to export to the new sheet only contents of the cells without any macros or buttons.
If someone has any idea what is wrong with my code please help!
Dim strFilename, strDirname, strPathname, strDefpath As String
Dim wbSource, wbDest As Workbook
strDirname = "Audit Tool" & " " & Format(Now(), "yyyymmdd hh-mm")
strFilename = "Check" & " " & Format(Now(), "yyyymmdd")
strDefpath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\"
MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "\" & strFilename
Set wbSource = ActiveWorkbook
ActiveSheet.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs Filename:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Set wbSource = ActiveWorkbook
ActiveSheet.Cells.ClearContents
ActiveSheet.Cells.ClearFormats
Application.ScreenUpdating = True
I also posted this topic here:
http://www.excelforum.com/excel-programming/685350-create-folder-and-export-sheet-not-working.html