I'm very new to visual basic and I was wondering how do I create a folder and
place the modified file from the macro into that new folder. Here's my code
so far. I would also like to name the folder and file to they day that the
macro is run if that's possible?

Option Explicit


Sub CRautomate()

'Application.OnTime TimeValue("012:49:00"), "CRautomate"


'-----------Declare all variables-----
Dim Col As Integer
Dim Row As Integer

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Dim newBook As Workbook
Dim newSheet As Worksheet

Workbooks.Open "I:\SW\users\CR Prioritization\Raw Data\template" 'Open
template file with dropdown menus

'--------Populate the spreadsheet----------------------------
Row = 1
Do Until Row = 25

For Col = 1 To 35
ActiveSheet.Cells(Row, Col) = Data.Cells((8 + Row), Col).Value

Next
Row = Row + 1
If Row = 2 Then Row = 3 'gets rid of .... field

Loop

ActiveSheet.Columns("B").Hidden = False 'unhide columns
ActiveSheet.Columns("D").ColumnWidth = 20 'make column D visible
ActiveSheet.Rows(2).Delete

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = "I:\SW\users\CR Prioritization"
.SearchSubFolders = True
End With




ActiveSheet.SaveAs "I:\SW\users\CR Prioritization\Automation Test
Folder\Open CR List" 'output file

Workbooks("Open CR List.xls").Close 'close the file which was opened

End Sub

Dim ExistingFolderName as string
Dim NewFolderName as string

existingfoldername = "I:\SW\users\CR Prioritization\Automation Test Folder"
if right(existingfoldername, 1) <> "\" then
existingfoldername = existingfoldername & "\"
end if

'no slashes in that date format!
newfoldername = existingfoldername & format(date,"yyyy_mm_dd")

on error resume next 'in case the folder can't be made
mkdir newfoldername
if err.number <> 0 then
msgbox "error creating folder!"
err.clear
end if



DanMsoeEE wrote:
>
> I'm very new to visual basic and I was wondering how do I create a folder and
> place the modified file from the macro into that new folder. Here's my code
> so far. I would also like to name the folder and file to they day that the
> macro is run if that's possible?
>
> Option Explicit
>
> Sub CRautomate()
>
> 'Application.OnTime TimeValue("012:49:00"), "CRautomate"
>
> '-----------Declare all variables-----
> Dim Col As Integer
> Dim Row As Integer
>
> Dim oExcel As Object
> Dim oBook As Object
> Dim oSheet As Object
>
> Dim newBook As Workbook
> Dim newSheet As Worksheet
>
> Workbooks.Open "I:\SW\users\CR Prioritization\Raw Data\template" 'Open
> template file with dropdown menus
>
> '--------Populate the spreadsheet----------------------------
> Row = 1
> Do Until Row = 25
>
> For Col = 1 To 35
> ActiveSheet.Cells(Row, Col) = Data.Cells((8 + Row), Col).Value
>
> Next
> Row = Row + 1
> If Row = 2 Then Row = 3 'gets rid of .... field
>
> Loop
>
> ActiveSheet.Columns("B").Hidden = False 'unhide columns
> ActiveSheet.Columns("D").ColumnWidth = 20 'make column D visible
> ActiveSheet.Rows(2).Delete
>
> With Application.FileSearch
> .NewSearch
> .FileType = msoFileTypeExcelWorkbooks
> .LookIn = "I:\SW\users\CR Prioritization"
> .SearchSubFolders = True
> End With
>
>
>
>
> ActiveSheet.SaveAs "I:\SW\users\CR Prioritization\Automation Test
> Folder\Open CR List" 'output file
>
> Workbooks("Open CR List.xls").Close 'close the file which was opened
>
> End Sub

--

Dave Peterson