Chirag R Raval
Member
Dear All Experts,
Though, As Routine for Loop, Loop on All files of folder , but requirement is Process Only specifically Listed (Partial Named)Files On Sheet, Not All Files Of Folder.
I have 2 codes , first code fill the series of dates between given 2 dates (Cell (“B3”) have start date & Cell (“B4”) have end date ) code return series or array of all dates between this two dates in vertical list form on sheet as start date to end date. You can name this code as Auto-fill Code”
And 2nd code another code open & process each file , as per given list as full path & full file name with extension. Given on sheet as file list. You can name this code as “File Processor Code”
Both code till not any relation to each other ,but requirement here is this code (file name process) should process each listed files which’s file names are partially mentioned .
I have 1 folder named “2017” & 12 files (we can have more) each files have monthly goods dispatch data in it,
File name’s first 8 characters look like a date form like “01052017”, or “01022018” etc.
For example (Like) full fine name , in folder named “2017” , is “01102017-DISP-INV INFO-ALL GUJ-46 COLIMNS-OCT-2017.xlsx”, Another file for example “01082017-DISP-INV INFO-ALL GUJ-46 COLIMNS-AUG-2017.xlsx” etc..
SCREEN SHOT OF FILE-FOLDER STRUCTURE
If Partial name on sheet , as just first 8 characters of full filename like “01102017”, or “018082017” etc..
Processing code should auto initialize full name based on partial name on sheet & process it.
First requirement is , in date code , I can not format dates filled/retuned by code in format “ddmmyyyy” OR “
In other words Like number “01052017” (remember that filled / returned as list dates are not is real date format, its may be number format…or text type…
Code no 1st “Auto fill dates Code ”
SCREEN SHOT OF END RESULT FOR AUTOFILL DATE CODE
Code No 2 "
“File Processor Code”
(1) 1st requirement about auto fill date code ..how to format dates as like "01012017" OR like "########"
(2) 2nd requirement is how to modify Process files code , that loop on given list,
(return by auto-fill date code (return as like "01052017" ) which become partial name for process file code. (Partial as first 8 character of file name if partial match with full, process that file).
Hope your help regarding this thread..
Regards,
Chirag Raval
Though, As Routine for Loop, Loop on All files of folder , but requirement is Process Only specifically Listed (Partial Named)Files On Sheet, Not All Files Of Folder.
I have 2 codes , first code fill the series of dates between given 2 dates (Cell (“B3”) have start date & Cell (“B4”) have end date ) code return series or array of all dates between this two dates in vertical list form on sheet as start date to end date. You can name this code as Auto-fill Code”
And 2nd code another code open & process each file , as per given list as full path & full file name with extension. Given on sheet as file list. You can name this code as “File Processor Code”
Both code till not any relation to each other ,but requirement here is this code (file name process) should process each listed files which’s file names are partially mentioned .
I have 1 folder named “2017” & 12 files (we can have more) each files have monthly goods dispatch data in it,
File name’s first 8 characters look like a date form like “01052017”, or “01022018” etc.
For example (Like) full fine name , in folder named “2017” , is “01102017-DISP-INV INFO-ALL GUJ-46 COLIMNS-OCT-2017.xlsx”, Another file for example “01082017-DISP-INV INFO-ALL GUJ-46 COLIMNS-AUG-2017.xlsx” etc..
SCREEN SHOT OF FILE-FOLDER STRUCTURE
If Partial name on sheet , as just first 8 characters of full filename like “01102017”, or “018082017” etc..
Processing code should auto initialize full name based on partial name on sheet & process it.
First requirement is , in date code , I can not format dates filled/retuned by code in format “ddmmyyyy” OR “
In other words Like number “01052017” (remember that filled / returned as list dates are not is real date format, its may be number format…or text type…
Code no 1st “Auto fill dates Code ”
Code:
'AUTOFILL DATES -LIST AS MONTH YEAR BETWEEN GIVEN TWO DATES ON SHEET
Sub AutoFillDateMonthyear()
Dim sc As Range
Dim Stdt As Date
Dim Edt As Date
Dim dDate As Date
Dim off As Integer
Stdt = ActiveSheet.Range("B3") ' start date
Edt = ActiveSheet.Range("B4") ' end date
Set sc = ActiveSheet.Range("A8") ' start cell
'
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
'
off = 0
'
For dDate = Stdt To Edt
If Format(dDate, "dd") = "01" Then
sc.Offset(off, 0) = Format(dDate, "mm yy")
' WHY CAN NOT CONVERT IT LIKE BELOW?
' sc.Offset(off, 0) = Format(dDate, "DDMMYYYY") as LOOK "01052017"???
off = off + 1
End If
Next dDate
'
'sc.Resize(off, 1).NumberFormat = "mm" & " - " & "yy"
' 'WE CAN MODIFY DATE FORMAT BELOW
' sc.Resize(off, 1).NumberFormat = "mm" & " - " & "yy"
'
End SubSCREEN SHOT OF END RESULT FOR AUTOFILL DATE CODE
Code No 2 "
“File Processor Code”
Code:
Sub LoopOpenAndProcessFilesAsListok()
Dim myDir As String
Dim r As Range
Dim fn As String
Dim msg As String
Dim destwbk As Workbook
Dim SWBK As Workbook
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set destwbk = ActiveWorkbook
' destwbk.Activate
myDir = "D:\INV INFO 46 COLUMNS ALL GUJARAT-MONTH WISE EXCEL FILE-2017\MONTH WISE SEPRATE FILES\2017\"
For Each r In Range("c2", Range("c" & Rows.count).End(xlUp))
fn = Dir(myDir & r.Value)
If fn = "" Then
msg = msg & vbLf & r.Value
Else
With Workbooks.Open(myDir & fn)
Set SWBK = ActiveWorkbook
SWBK.Sheets(1).Range("A1:D5").Copy
'
' destwbk.Activate
'
If destwbk.Sheets(1).Range("A10") = "" Then
destwbk.Sheets(1).Range("A10").PasteSpecial xlPasteValues
Else
destwbk.Sheets(1).Range("A10").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
SWBK.Close False
End If
End With
End If
Next
If Len(msg) Then
MsgBox "Not found" & msg
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub(1) 1st requirement about auto fill date code ..how to format dates as like "01012017" OR like "########"
(2) 2nd requirement is how to modify Process files code , that loop on given list,
(return by auto-fill date code (return as like "01052017" ) which become partial name for process file code. (Partial as first 8 character of file name if partial match with full, process that file).
Hope your help regarding this thread..
Regards,
Chirag Raval
Last edited:
