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:D").Hidden = False 'unhide columns
ActiveSheet.Columns("D: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:D").Hidden = False 'unhide columns
ActiveSheet.Columns("D: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