hellfire45
Active Member
- Joined
- Jun 7, 2014
- Messages
- 462
Hi Guys. Ya'll are the best so I'm sure somebody can decipher this. I have a script below that is intended to go through a folder and remove any .xlsx files which contain the string stored in the variable active_warehouse and move them over to the archive. I included the target_ext variable because I wanted the code to skip any files that were not .xlsx. There are 6 files in the warehouse holder below. 3 of them are .txt and 3 of them are .xlsx
I expect the code to check the .txt files and move on sequentially down the list of files in the folder but it just keeps checking the same file 5 times and then exits the loop.
Can somebody please suggest what might be wrong with this programming? I attached a file of the folder as well. Thanks so much!
I expect the code to check the .txt files and move on sequentially down the list of files in the folder but it just keeps checking the same file 5 times and then exits the loop.
Can somebody please suggest what might be wrong with this programming? I attached a file of the folder as well. Thanks so much!
VBA Code:
Sub archive_wh()
Dim strFileSpec As String
Dim strFileName As String
Dim extfind As String
Dim FileInFromFolder As Object
Dim destinationpath As String
Dim fso As Object
Dim sel_warehouse As Integer
Dim active_warehouse As String
Dim sel_Year As Integer
Dim active_year As String
Dim WAREHOUSECOUNTS_STRFOLDER As String
Dim WAREHOUSECOUNTS_ARCHIVE As String
Dim target_ext As String
Dim filename_lessext As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
sel_warehouse = Application.Match("Select Warehouse", Dash.Columns(1), 0)
active_warehouse = Dash.Cells(sel_warehouse + 1, 1).Value
sel_Year = Application.Match("Select Year", Dash.Rows(sel_warehouse), 0)
active_year = Dash.Cells(sel_warehouse + 1, sel_Year).Value
WAREHOUSECOUNTS_STRFOLDER = "R:\Materials\INVENTORY CONTROL FILE\IN PROGRESS\WAREHOUSE INVENTORY AUTOMATION\WAREHOUSE COUNTS\"
WAREHOUSECOUNTS_ARCHIVE = "R:\Materials\INVENTORY CONTROL FILE\IN PROGRESS\WAREHOUSE INVENTORY AUTOMATION\ARCHIVE\WAREHOUSE COUNTS\"
target_ext = ".xlsx"
If Dir(WAREHOUSECOUNTS_ARCHIVE & "\" & active_year & "\", vbDirectory) = "" Then
MkDir (WAREHOUSECOUNTS_ARCHIVE & "\" & active_year & "\")
End If
destinationpath = WAREHOUSECOUNTS_ARCHIVE & "\" & active_year & "\"
strFileSpec = WAREHOUSECOUNTS_STRFOLDER & "*.*"
Set fso = CreateObject("Scripting.FileSystemObject")
For Each FileInFromFolder In fso.getfolder(WAREHOUSECOUNTS_STRFOLDER).Files
strFileName = Dir(strFileSpec)
If InStr(1, strFileName, active_warehouse, 1) > 0 And InStr(1, strFileName, target_ext, 1) > 0 Then
fso.MoveFile Source:=WAREHOUSECOUNTS_STRFOLDER & strFileName, Destination:=destinationpath & strFileName
End If
Next FileInFromFolder 'next iteration in loop
End Sub