You can probably use VBA code using the Dir() function to get a list of files,
probably a Form displaying them, and the TransferText method... but I have NO
clue what's involved in "prep"ing the files, nor what issues you will have
with detecting duplicate files or duplicate data, or how the file names are
chosen. Care to post some more details?
John W. Vinson [MVP]
Thanks for your reply. Sure -
Basically, I have about 157 csv data files. They're all labeled as
such "SUN_TRKR_CINCINNATI_0319_0401_02.CSV" TRKR & CSV is about the
only text that is consistant with the rest of the files.
As far as prepping goes - I run a basic macro "LoopFolders_Cincinnati"
which opens each CVS files and run another macro over them. This
other macro simply eliminates the first row and descends a single
column out of 83 columns. The macro will save the changes and I'm
ready to manually import.
I have several reporting areas - So, I make sure I manually import
"Cincinnati" csv data (roughly 15 reports for a single area) into its
very own "CincinnatiDataTable" and I'll just repeat these steps for
other areas.
Dim aryFiles
Dim oFSO
Sub LoopFolders_Cincinnati()
Dim i As Integer
Set oFSO = CreateObject("Scripting.FileSystemObject")
selectFiles "C:\Documents and Settings\Desktop\TRACKERS\DATA
CINCINNATI"
Set oFSO = Nothing
End Sub
'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr
Set Folder = oFSO.GetFolder(sPath)
For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr
For Each file In Folder.Files
If file.Type = "Microsoft Excel Comma Separated Values File"
Then
Workbooks.Open Filename:=file.Path
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("BW2"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Next file
End Sub
Thanks for your assistance. Simplymidori
