Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a file in a macro
I have created a macro that imports a file and makes necessary changes. The
file location and name are embedded in the macro and must be change prior to running the macro. How can I present a dialog box that allows the user to select the file to be imported? My current macro is below: Sub FixWorksheet() ' ' FixWorksheet Macro ' Macro recorded 8/10/2007 by Robert Harris ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;\\server\folder\filename.txt" _ , Destination:=Range("A1")) .Name = "filename" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 3, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, _ 1, 3, 1, 1, 3, 3) .Refresh BackgroundQuery:=False End With Rows("1:4").Select Selection.Delete Shift:=xlUp Range("AB2").Select ActiveCell.FormulaR1C1 = _ "=IF(AND(OR(RC[-16]=""OTHER"",RC[-16]=""SELF""),LEFT(RC[-26],LEN(RC[-17]))=RC[-17]),IF(RC[-27]=0,"""",RC[-27]),"""")" Range("AC2").Select ActiveCell.FormulaR1C1 = "=IF(AND(OR(RC[-17]=""OTHER"",RC[-17]=""SELF""),LEFT(RC[-27],LEN(RC[-18]))=RC[-18]),RC[-20],"""")" Range("AB2:AC2").Select Selection.AutoFill Destination:=Range("AB2:AC10000"), Type:=xlFillDefault Columns("AC:AC").Select Selection.NumberFormat = "m/d/yyyy" ' End Sub Thanks for your help. (I am just starting to explore programming so be as specific as possible) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a file in a macro
Hi Rob
You can use GetOpenFilename together with the ChDirNet function When you use ChDirNet you can start in the networkfolder See how I use it in this macro http://www.rondebruin.nl/txtcsv.htm I use multiselect here to give the user a option to select for more files. Set it to false for one file and change this line If you do that Change If IsArray(TxtFileNames) Then to If TxtFileNames< False Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... I have created a macro that imports a file and makes necessary changes. The file location and name are embedded in the macro and must be change prior to running the macro. How can I present a dialog box that allows the user to select the file to be imported? My current macro is below: Sub FixWorksheet() ' ' FixWorksheet Macro ' Macro recorded 8/10/2007 by Robert Harris ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;\\server\folder\filename.txt" _ , Destination:=Range("A1")) .Name = "filename" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 3, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, _ 1, 3, 1, 1, 3, 3) .Refresh BackgroundQuery:=False End With Rows("1:4").Select Selection.Delete Shift:=xlUp Range("AB2").Select ActiveCell.FormulaR1C1 = _ "=IF(AND(OR(RC[-16]=""OTHER"",RC[-16]=""SELF""),LEFT(RC[-26],LEN(RC[-17]))=RC[-17]),IF(RC[-27]=0,"""",RC[-27]),"""")" Range("AC2").Select ActiveCell.FormulaR1C1 = "=IF(AND(OR(RC[-17]=""OTHER"",RC[-17]=""SELF""),LEFT(RC[-27],LEN(RC[-18]))=RC[-18]),RC[-20],"""")" Range("AB2:AC2").Select Selection.AutoFill Destination:=Range("AB2:AC10000"), Type:=xlFillDefault Columns("AC:AC").Select Selection.NumberFormat = "m/d/yyyy" ' End Sub Thanks for your help. (I am just starting to explore programming so be as specific as possible) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a file in a macro
Thank you - Your code had exact the information required to resolve my issue.
"Ron de Bruin" wrote: Hi Rob You can use GetOpenFilename together with the ChDirNet function When you use ChDirNet you can start in the networkfolder See how I use it in this macro http://www.rondebruin.nl/txtcsv.htm I use multiselect here to give the user a option to select for more files. Set it to false for one file and change this line If you do that Change If IsArray(TxtFileNames) Then to If TxtFileNames< False Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... I have created a macro that imports a file and makes necessary changes. The file location and name are embedded in the macro and must be change prior to running the macro. How can I present a dialog box that allows the user to select the file to be imported? My current macro is below: Sub FixWorksheet() ' ' FixWorksheet Macro ' Macro recorded 8/10/2007 by Robert Harris ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;\\server\folder\filename.txt" _ , Destination:=Range("A1")) .Name = "filename" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 3, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, _ 1, 3, 1, 1, 3, 3) .Refresh BackgroundQuery:=False End With Rows("1:4").Select Selection.Delete Shift:=xlUp Range("AB2").Select ActiveCell.FormulaR1C1 = _ "=IF(AND(OR(RC[-16]=""OTHER"",RC[-16]=""SELF""),LEFT(RC[-26],LEN(RC[-17]))=RC[-17]),IF(RC[-27]=0,"""",RC[-27]),"""")" Range("AC2").Select ActiveCell.FormulaR1C1 = "=IF(AND(OR(RC[-17]=""OTHER"",RC[-17]=""SELF""),LEFT(RC[-27],LEN(RC[-18]))=RC[-18]),RC[-20],"""")" Range("AB2:AC2").Select Selection.AutoFill Destination:=Range("AB2:AC10000"), Type:=xlFillDefault Columns("AC:AC").Select Selection.NumberFormat = "m/d/yyyy" ' End Sub Thanks for your help. (I am just starting to explore programming so be as specific as possible) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting file | Excel Worksheet Functions | |||
Selecting the most recent file in a folder by Macro/VBA script | Excel Programming | |||
selecting a file automatically | Excel Programming | |||
Selecting File | Excel Programming | |||
Selecting File and FilePath | Excel Programming |