Hi all,

I had requests:
1. open file dialog to select ASCII file
2. import this file into excel sheet, not open another workbook, is
inserted into one worksheet
3. pop-up a message windows to show what file is selected.

So what I code:

Sub GetImportFile()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant

'Set up list of file filters
Filt = "Product Data File (*.pd*),*.pd"


'Display *.* by default
FilterIndex = 2

'Set the dialog box caption
Title = "Select a Product Data File to Import"

'Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

Workbooks.OpenText FileName:=Application.GetOpenFilename _
(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title),
Origin:=-535, StartRow:=1 _
, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array
(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1)),
TrailingMinusNumbers:=True

'Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
' Exit Sub
'End If

Display full path and name of the file
MsgBox "You selected " & FileName


End Sub

The above code, I got 2 times file dialog, and one time import file,
but this importing is to new workbook,
which is not my target.

Could anybody help?

BTW: what is the difference between GetOpenFilename and
workbooks.opentext?

TIA

z

I won't muck with your code, but I'll suggest an alternative to this:

2. import this file into excel sheet, not open another workbook, is
inserted into one worksheet

Open it as a new workbook, then either move the sheet into place in the
target workbook, or if you are adding the data to an existing sheet, copy
the data, paste it wherever, and delete the new workbook.

If you use Application.ScreenUpdating wisely. nobody will notice that the
data didn't just magically appear.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"skycloud" wrote in message
...
Hi all,

I had requests:
1. open file dialog to select ASCII file
2. import this file into excel sheet, not open another workbook, is
inserted into one worksheet
3. pop-up a message windows to show what file is selected.

So what I code:

Sub GetImportFile()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant

'Set up list of file filters
Filt = "Product Data File (*.pd*),*.pd"


'Display *.* by default
FilterIndex = 2

'Set the dialog box caption
Title = "Select a Product Data File to Import"

'Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

Workbooks.OpenText FileName:=Application.GetOpenFilename _
(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title),
Origin:=-535, StartRow:=1 _
, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array
(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1)),
TrailingMinusNumbers:=True

'Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
' Exit Sub
'End If

Display full path and name of the file
MsgBox "You selected " & FileName


End Sub

The above code, I got 2 times file dialog, and one time import file,
but this importing is to new workbook,
which is not my target.

Could anybody help?

BTW: what is the difference between GetOpenFilename and
workbooks.opentext?

TIA

z

2 times file dialog:
Application.GetOpenFilename is called twice: first at FileName =
Application.GetOpenFilename then at Workbooks.OpenText
FileName:=Application.GetOpenFilename.


i recommend prefixing your variables with their type to help distinguish
that they are variables and not something else:

dim strFileName as string

strFileName = Application.GetOpenFilename (FileFilter:=Filt,
FilterIndex:=FilterIndex, Title:=Title)

Workbooks.OpenText FileName:= strFileName, Origin:=-535, StartRow:=1, ...

"skycloud" wrote:

Hi all,

I had requests:
1. open file dialog to select ASCII file
2. import this file into excel sheet, not open another workbook, is
inserted into one worksheet
3. pop-up a message windows to show what file is selected.

So what I code:

Sub GetImportFile()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant

'Set up list of file filters
Filt = "Product Data File (*.pd*),*.pd"


'Display *.* by default
FilterIndex = 2

'Set the dialog box caption
Title = "Select a Product Data File to Import"

'Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

Workbooks.OpenText FileName:=Application.GetOpenFilename _
(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title),
Origin:=-535, StartRow:=1 _
, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,