PDA

View Full Version : Solved: Create folder & export sheet, not working



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

rinser
05-29-2009, 04:54 AM
Hi guys!

The issue was solved at the mentioned link!

Cheers!