what is the way to select ASCII file and import it into excel sheet?

[skycloud]

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

 

[Jon Peltier]

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

 

[rogge]

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, ...