Hello,
I would like a little help with the following please. I have found a "file open dialog" script on the internet and would like to use it with the macro that I already have so far. I have a spreadsheet that I want to press "select file 1" and import the .SPF file into tab data_ZOS_1 and then button 2 to import another .SPF file into tab data_ZOS_2 but am unsure on how to tie them both together. Any help would be appreciated.
Sub FileOpenDialogBox()
'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
With Application.FileDialog(msoFileDialogFilePicker)
'Makes sure the user can select only one file
.AllowMultiSelect = False
'Filter to just the following types of files to narrow down selection options
.Filters.Add "SPF files", "*.SPF"
'Show the dialog box
.Show
'Store in fullpath variable
fullpath = .SelectedItems.Item(1)
End With
'It's a good idea to still check if the file type selected is accurate.
'Quit the procedure if the user didn't select the type of file we need.
If InStr(fullpath, ".SPF") = 0 Then
Exit Sub
End If
MY macro
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+g
'
Sheets("Sheet1").Select
Sheets.Add After:=Sheets(Sheets.Count)
ActiveCell.FormulaR1C1 = ""
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Data_ZOS_1"
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\MyScripts\Zos_Files\AFO400_1_006_B38TUE1_ZOS.SPF", Destination:= _
Range("$A$1"))
.Name = "AFO400_1_006_B38TUE1_ZOS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "+"
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
End Sub
I would like a little help with the following please. I have found a "file open dialog" script on the internet and would like to use it with the macro that I already have so far. I have a spreadsheet that I want to press "select file 1" and import the .SPF file into tab data_ZOS_1 and then button 2 to import another .SPF file into tab data_ZOS_2 but am unsure on how to tie them both together. Any help would be appreciated.
Sub FileOpenDialogBox()
'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
With Application.FileDialog(msoFileDialogFilePicker)
'Makes sure the user can select only one file
.AllowMultiSelect = False
'Filter to just the following types of files to narrow down selection options
.Filters.Add "SPF files", "*.SPF"
'Show the dialog box
.Show
'Store in fullpath variable
fullpath = .SelectedItems.Item(1)
End With
'It's a good idea to still check if the file type selected is accurate.
'Quit the procedure if the user didn't select the type of file we need.
If InStr(fullpath, ".SPF") = 0 Then
Exit Sub
End If
MY macro
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+g
'
Sheets("Sheet1").Select
Sheets.Add After:=Sheets(Sheets.Count)
ActiveCell.FormulaR1C1 = ""
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Data_ZOS_1"
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\MyScripts\Zos_Files\AFO400_1_006_B38TUE1_ZOS.SPF", Destination:= _
Range("$A$1"))
.Name = "AFO400_1_006_B38TUE1_ZOS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "+"
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
End Sub
