User Interface for Viewing a Collection of Data Files

I have a new task to merge a collection of four daily generated data files
..csv format for user review. I have created the macro to merge and sort the
data into a primary worksheet. My delema is that I need to create a dialog
to display and enable the user to select the next collection of 4 files to be
precessed by the macro.
The datafiles have a nameing convention of ClientMxx_yyyymmddhhmmss.csv .
How can I take the user file selections and apply each to my existing macro
via a dialog box ? TIA

You could use Application.GetOpenFileName and set multiselect to True and
the filter to csv.

Check the user has selected 4 files and if so process the files.

If you post your current code someone may offer the required modification...

Tim.

--
Tim Williams
Palo Alto, CA


"JKramer" <[email protected]> wrote in message
news:[email protected]...
> I have a new task to merge a collection of four daily generated data files
> .csv format for user review. I have created the macro to merge and sort
the
> data into a primary worksheet. My delema is that I need to create a
dialog
> to display and enable the user to select the next collection of 4 files to
be
> precessed by the macro.
> The datafiles have a nameing convention of ClientMxx_yyyymmddhhmmss.csv .
> How can I take the user file selections and apply each to my existing
macro
> via a dialog box ? TIA

This is what I have put together so far:

Sub BuildDataMacro()
'
' BuildDataMacro Macro
' Macro recorded 1/5/2006 by
'
' Keyboard Shortcut: Ctrl+i
'
Workbooks.Open Filename:="C:\Input_Files\ClientM65_20051214175118.csv"
Range("J6:O6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("A6").Select
Cells.Replace What:="CD", Replacement:="CD65", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.SpecialCells(xlLastCell).Select
Range("A3870").Select
Workbooks.Open Filename:="C:\Input_Files\ClientM103_20051214174422.csv"
Range("J6:O6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("A6").Select
Cells.Replace What:="CD", Replacement:="CD103", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
Selection.Find(What:="CD103", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A9").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("ClientM65_20051214175118.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select
Range("A7731").Select
Workbooks.Open Filename:="C:\Input_Files\ClientM108_20051214144503.csv"
Range("J6:O6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A6").Select
Cells.Replace What:="CD", Replacement:="CD108", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="cd108", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("ClientM65_20051214175118.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select
Range("A11592").Select
Workbooks.Open Filename:="C:\Input_Files\ServerM51_20051214143810.csv"
ActiveWindow.LargeScroll Down:=1
Range("A32").Select
ActiveWindow.LargeScroll Down:=1
Range("A63").Select
ActiveWindow.LargeScroll Down:=1
Range("J107:O107").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents
Rows("107:107").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("A107"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A107").Select
Cells.Find(What:="sd51", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Rows("11690:11690").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("ClientM65_20051214175118.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select
Rows("1:4").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Rows("5:5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A5:O15447").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A5").Select
ActiveCell.SpecialCells(xlLastCell).Select
Range("A1").Select
Columns("C:C").EntireColumn.AutoFit
End Sub


"Tim Williams" wrote:

> You could use Application.GetOpenFileName and set multiselect to True and
> the filter to csv.
>
> Check the user has selected 4 files and if so process the files.
>
> If you post your current code someone may offer the required modification...
>
> Tim.
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "JKramer" <[email protected]> wrote in message
> news:[email protected]...
> > I have a new task to merge a collection of four daily generated data files
> > .csv format for user review. I have created the macro to merge and sort
> the
> > data into a primary worksheet. My delema is that I need to create a
> dialog
> > to display and enable the user to select the next collection of 4 files to
> be
> > precessed by the macro.
> > The datafiles have a nameing convention of ClientMxx_yyyymmddhhmmss.csv .
> > How can I take the user file selections and apply each to my existing
> macro
> > via a dialog box ? TIA
>
>
>