In Excel-2007 ImportTextFile dialog box problem

[Mahesh_D]

Hi,

I want to know the name of the file being used when importing it using "Get
External Data from Text" option.

So on clicking this option, I have added the following code. But it doesn't
give the filename.


Try
Dim dlg As Excel.Dialog
Dim userAction As Long
dlg =
ThisAddIn.Application.Dialogs(Excel.XlBuiltInDialog.xlDialogImportTextFile)
userAction = dlg.Show()

Debug.WriteLine("Name -> " + dlg.Name.ToString())
Catch ex As Exception
Debug.WriteLine("Exception is -> " + ex.Message)
End Try


how should i get name of the file being imported ?



Thanks
Mahesh

 

[Doug Robbins - Word MVP on news.microsoft.com]

You could do something like this to import a text file instead of using that
dialog

Dim Source As String
Dim SourceName As String
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Import Text File"
.InitialFileName = "*.txt"
If .Show = -1 Then
Source = .SelectedItems(1)
Else
MsgBox "You cancelled the Text File Import".
Exit Sub
End If
End With
Set fd = Nothing
SourceName = Mid(Source, InStrRev(Source, "\") + 1)
SourceName = Left(SourceName, InStr(SourceName, ".") - 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Source, Destination:=Range("$A$1"))
.Name = SourceName
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(76, 6, 13, 7, 25)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

You may want to change the settings of some of the attributes in the second
With End With construction as that code is a modification of that
generated by the macro recorder
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Mahesh_D]

Thanks Doug for your reply.

It works same as "Get external data form text"

Now, for "Get External Data from Access" , i am changing connections as
"FINDER"

With ActiveSheet.QueryTables.Add(Connection:= "FINDER;" &
Source, Destination:=Range("$A$1"))

This code, imports file data properly. But it doesnot give output same as
given by "Get extenal data from access" by default.


thanks
mahesh