Can someone show me how to use VBA to collect part of a folder and file name
from an input box?

e.g. Const CSVFile As String = "Z:\FILES\ACCOUNTING\PAYROLL\DAILY PROGRESS
REPORTS (DPRs)\WE 5-9-10\WE 5-9-10.csv"

I would like to use an input box to change the date of the folder €œWE
5-9-10€� and the file €œWE 5-9-10.csv€�

This is the scenario€¦ I have excel files that are emailed to me daily,
typically four to six separate workbooks per day. The information in the
spreadsheets is for payroll, so having all the data in one workbook at the
end of the week makes it much easier to process. I patched together the
following VBA to append each workbooks data to a .csv file, but there has to
be a better way to do this. Any help offered would be greatly appreciated.

VBA I'm currently using:

Sub Append2CSV()
Dim tmpCSV As String 'string to hold the CSV info
Dim f As Integer
Dim myRng As String
Dim myRng2 As String
Dim myRng3 As String

myRng = Application.InputBox("Enter a number")


Const CSVFile As String = "Z:\FILES\ACCOUNTING\PAYROLL\DAILY PROGRESS
REPORTS (DPRs)\WE 5-9-10\WE 5-9-10.csv"

f = FreeFile
myRng2 = "A2:N"
myRng3 = myRng2 & myRng

Open CSVFile For Append As #f
tmpCSV = Range2CSV(Range(myRng3))
Print #f, tmpCSV
Close #f
End Sub

Function Range2CSV(list) As String
Dim tmp As String
Dim cr As Long
Dim r As Range

If TypeName(list) = "Range" Then
cr = 1

For Each r In list.Cells
If r.Row = cr Then
If tmp = vbNullString Then
tmp = r.Value
Else
tmp = tmp & "," & r.Value
End If
Else
cr = cr + 1
If tmp = vbNullString Then
tmp = r.Value
Else
tmp = tmp & Chr(10) & r.Value
End If
End If
Next
End If

Range2CSV = tmp
End Function

If I understand correctly you want the csvfile name to be created dynamically
from the date input..Try the below


Dim CSVFile As String, varData As Variant

varData = InputBox("Enter Date")
If Not IsDate(varData) Then MsgBox "Invalid Date": Exit Sub

CSVFile = "Z:\FILES\ACCOUNTING\PAYROLL\DAILY PROGRESS REPORTS " & _
"(DPRs)\WE " & Format(CDate(varData), "m-d-yy") & "\WE " & _
Format(CDate(varData), "m-d-yy") & ".csv"

MsgBox CSVFile


--
Jacob (MVP - Excel)


"Mark" wrote:

Can someone show me how to use VBA to collect part of a folder and file name
from an input box?

e.g. Const CSVFile As String = "Z:\FILES\ACCOUNTING\PAYROLL\DAILY PROGRESS
REPORTS (DPRs)\WE 5-9-10\WE 5-9-10.csv"

I would like to use an input box to change the date of the folder €œWE
5-9-10€� and the file €œWE 5-9-10.csv€�

This is the scenario€¦ I have excel files that are emailed to me daily,
typically four to six separate workbooks per day. The information in the
spreadsheets is for payroll, so having all the data in one workbook at the
end of the week makes it much easier to process. I patched together the
following VBA to append each workbooks data to a .csv file, but there has to
be a better way to do this. Any help offered would be greatly appreciated.

VBA I'm currently using:

Sub Append2CSV()
Dim tmpCSV As String 'string to hold the CSV info
Dim f As Integer
Dim myRng As String
Dim myRng2 As String
Dim myRng3 As String

myRng = Application.InputBox("Enter a number")


Const CSVFile As String = "Z:\FILES\ACCOUNTING\PAYROLL\DAILY PROGRESS
REPORTS (DPRs)\WE 5-9-10\WE 5-9-10.csv"

f = FreeFile
myRng2 = "A2:N"
myRng3 = myRng2 & myRng

Open CSVFile For Append As #f
tmpCSV = Range2CSV(Range(myRng3))
Print #f, tmpCSV
Close #f
End Sub

Function Range2CSV(list) As String
Dim tmp As String
Dim cr As Long
Dim r As Range

If TypeName(list) = "Range" Then
cr = 1

For Each r In list.Cells
If r.Row = cr Then
If tmp = vbNullString Then
tmp = r.Value
Else
tmp = tmp & "," & r.Value
End If
Else
cr = cr + 1
If tmp = vbNullString Then
tmp = r.Value
Else
tmp = tmp & Chr(10) & r.Value
End If
End If
Next
End If

Range2CSV = tmp
End Function

Thank you Jacob.

Do you know of way to save(append) to an excel file, rather than .csv?

-Mark Elkins

"Jacob Skaria" wrote:

If I understand correctly you want the csvfile name to be created dynamically
from the date input..Try the below


Dim CSVFile As String, varData As Variant

varData = InputBox("Enter Date")
If Not IsDate(varData) Then MsgBox "Invalid Date": Exit Sub

CSVFile = "Z:\FILES\ACCOUNTING\PAYROLL\DAILY PROGRESS REPORTS " & _
"(DPRs)\WE " & Format(CDate(varData), "m-d-yy") & "\WE " & _
Format(CDate(varData), "m-d-yy") & ".csv"

MsgBox CSVFile


--
Jacob (MVP - Excel)


"Mark" wrote:

Can someone show me how to use VBA to collect part of a folder and file name
from an input box?

e.g. Const CSVFile As String = "Z:\FILES\ACCOUNTING\PAYROLL\DAILY PROGRESS
REPORTS (DPRs)\WE 5-9-10\WE 5-9-10.csv"

I would like to use an input box to change the date of the folder €œWE
5-9-10€� and the file €œWE 5-9-10.csv€�

This is the scenario€¦ I have excel files that are emailed to me daily,
typically four to six separate workbooks per day. The information in the
spreadsheets is for payroll, so having all the data in one workbook at the
end of the week makes it much easier to process. I patched together the
following VBA to append each workbooks data to a .csv file, but there has to
be a better way to do this. Any help offered would be greatly appreciated.

VBA I'm currently using:

Sub Append2CSV()
Dim tmpCSV As String 'string to hold the CSV info
Dim f As Integer
Dim myRng As String
Dim myRng2 As String
Dim myRng3 As String

myRng = Application.InputBox("Enter a number")


Const CSVFile As String = "Z:\FILES\ACCOUNTING\PAYROLL\DAILY PROGRESS
REPORTS (DPRs)\WE 5-9-10\WE 5-9-10.csv"

f = FreeFile
myRng2 = "A2:N"
myRng3 = myRng2 & myRng

Open CSVFile For Append As #f
tmpCSV = Range2CSV(Range(myRng3))
Print #f, tmpCSV
Close #f
End Sub

Function Range2CSV(list) As String
Dim tmp As String
Dim cr As Long
Dim r As Range

If TypeName(list) = "Range" Then
cr = 1

For Each r In list.Cells
If r.Row = cr Then
If tmp = vbNullString Then
tmp = r.Value
Else
tmp = tmp & "," & r.Value
End If
Else
cr = cr + 1
If tmp = vbNullString Then
tmp = r.Value
Else
tmp = tmp & Chr(10) & r.Value
End If
End If
Next
End If

Range2CSV = tmp
End Function