Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Interface for Viewing a Collection of Data Files
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Interface for Viewing a Collection of Data Files
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_2005121417442 2.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_2005121414450 3.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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Interface in VBA | Excel Programming | |||
QUI Expert: Excel-based User Interface or OO User Interface? | Excel Programming | |||
GUI Expert: Excel-based User Interface or OO User Interface? | Excel Programming | |||
Unable to add user-defined data types to a collection | Excel Programming | |||
User Interface | Excel Programming |