If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Input Box
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 workbook’s 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 |
#2
|
|||
|
|||
Input Box
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 workbook’s 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 |
#3
|
|||
|
|||
Input Box
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 workbook’s 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 |
Thread Tools | |
Display Modes | |
|
|