VBA - UserForm Reset Option
There are towo functions shown below. The difference is the OPEN method
insists on a pre-existing file and the SAVEAS allows you to use either a
pre-existing filename or a new file name. Both return a Fullpathname along
with the filename.
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
MsgBox "Save as " & fileSaveName
End If
fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen < False Then
MsgBox "Open " & fileToOpen
End If
"megatron08" wrote:
Thanks this is what I needed.
Can I create an Application.InputBox to prompt the user to find a file like
in the following macro? Is it even possible?
Sub Import()
'
' Import Macro
' Macro recorded 08/06/2008 by christopherr
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\Financial Services\FS2008\Woodworkers\ER
Reports\052008\Test\Original Weyerhaeuser.TOC May 08.200806172100086.txt" _
, Destination:=Range("A1"))
.Name = "Original Weyerhaeuser.NelsonTrust.200806172055088"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Totals"
End Sub
Any suggestions/ideas would be great.
TIA
~Christopher
"Joel" wrote:
I don't like using recorded macro without making changes. The recorded
macros use "Selection". I pefer to specify Worksheet names and Ranges.
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
with Sheets("Sheet2")
MyRange.Copy
.Paste 'really shoud have a range here. Can cause errors.
.Columns("E:E").EntireColumn.AutoFit
.Columns("H:H").EntireColumn.AutoFit
.Columns("A:A").EntireColumn.AutoFit
end with
Here is my modifed code with your amcro
Sub Worksheet()
'
' WorkSheet Macro
' Macro recorded 08/06/2008 by christopherr
'
'
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
MyRange.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Sheets("Totals").Select
Application.CutCopyMode = False
End Sub
|