How to open the //Text Import Wizard// from within a macro?

[e18]

I often open textfiles in Excel and stores them as sheets in on
workbook. I have a macro for this which I have to edit almost everytim
because the column sizes the textfiles vary from time to time.

What I want to do is to open the "Text Import Wizard" from within th
macro. I guess(hope) there is an easy answer to this, but I still hav
not figured it out on my own...

Thakk for all hjelp

 

[Tom Ogilvy]

Application.Dialogs(xlDialogOpen).Show


then select a text file and click open.

 

[e18]

With a workbooks.count before and after I guess this will be great,
Thanks

Best regards
Erlend

 

[Guest]

To address your requirement to insert the text files as sheets in a single
workbook: If you don't want separate workbooks, you could embellish Tom's
suggestion as follows:

Sub GetTextFile()
Dim wbkTarget As Workbook

'Set a reference to your workbook.
'This assumes your workbook is active when you open the file.
Set wbkTarget = ActiveWorkbook 'otherwise, ..could also be
=Workbooks("MyBookName")

'Get the file.
'This will put the file in a new workbook,
'which will be deleted when we move the sheet to your wbk.
Application.ScreenUpdating = False
Application.Dialogs(xlDialogOpen).Show

'After the file opens it will be the ActiveWorkbook/ActiveSheet,
'so move it into your workbook.
ActiveSheet.Move After:=wbkTarget.Sheets(wbkTarget.Sheets.Count)

End Sub

Regards,
GS

 

[Guest]

maybe better:

Sub GetTextFile()
' This handles single file selection only.
' Assumes wbkTarget is active when the file is opened.

Dim wbkTarget As Workbook

'Set a reference to wbkTarget.
Set wbkTarget = ActiveWorkbook 'otherwise, ..could also be
=Workbooks("MyBookName")

'Get the file.
'This will put the file in a new workbook,
'which will be deleted when we move the sheet to wbkTarget.
Application.ScreenUpdating = False
Application.Dialogs(xlDialogOpen).Show

'After the file opens it will be the ActiveWorkbook/ActiveSheet,
'so move it into wbkTarget.
ActiveSheet.Move After:=wbkTarget.Sheets(wbkTarget.Sheets.Count)

End Sub

Sub GetTextFiles()
' This handles if user multi-selects files.
' Requires that wbkTarget is the only workbook open.
' Exits until only 1 workbook is open; -allows user to save.

Dim wbkTarget As Workbook, wnd As Window
Dim wbkVisible As Integer, iOpen As Integer
Dim msg As String

'Get a count of all visible windows
wbkVisible = 0
For Each wnd In Application.Windows
If wnd.Visible Then wbkVisible = wbkVisible + 1
Next

'If other workbooks open
If wbkVisible > 1 Then
msg = "This procedure requires that only the target workbook be open." &
vbCrLf
msg = msg & "You must close all other workbooks to proceed!" & vbCrLf
MsgBox msg 'add arguments as desired
Exit Sub
End If


'Set a reference to wbkTarget.
Set wbkTarget = ActiveWorkbook 'otherwise, ..could also be
=Workbooks("wbkTargetName")

'Get the count of all open books
iOpen = Application.Windows.Count

'Get the file(s).
'This will put each file in a new workbook,
'which will be deleted when we move each sheet to wbkTarget.
Application.ScreenUpdating = False
Application.Dialogs(xlDialogOpen).Show

'If any files were selected (or user didn't cancel)
If Application.Windows.Count > iOpen Then

'Move the sheet(s) into wbkTarget
For Each wnd In Application.Windows
If wnd.Visible And Not wnd.Caption = wbkTarget.Name Then
wnd.Activate
ActiveSheet.Move After:=wbkTarget.Sheets(wbkTarget.Sheets.Count)
End If
Next
End If

End Sub


Regards,
GS

 

[e18]

Thank you GS,
I did not see your second post until now.
After Tom Ogilvy's and your first post I ended up with this :


Sub GetTextFiles()

Dim beforeOpenAsci As Integer, afterOpenAsci As Integer, _
nTxtWbks As Integer, i As Integer, filTyp As Integer
Dim allTypes As String,filNamn As Variant
Dim wbkTarget As Workbook

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'--- New workbook as target
Workbooks.Add
Set wbkTarget = ActiveWorkbook
Do While wbkTarget.Sheets.Count > 1
ActiveSheet.Delete
Loop

'--- set directory
If MsgBox("Start in same directory as last?", 4, "...") = vbNo The
_
ChDir "\\...(snip)....\IE\Favorites\Links\SHORTCUTS"


If MsgBox("Fixed width?" & Chr(10) & Chr(10) & _
"(NO opens the Open Text Wizard)" , 4, "...") = vbNo _
Then

'--- NO: Open textfiles with "Open Text Wizard"
beforeOpenAsci = Workbooks.Count
Application.Dialogs(xlDialogOpen).Show
afterOpenAsci = Workbooks.Count
nTxtWbks = afterOpenAsci - beforeOpenAsci
For i = 1 To nTxtWbks
ActiveSheet.Mov
After:=wbkTarget.Sheets(wbkTarget.Sheets.Count)
ActiveWindow.ActivateNext
Next i
wbkTarget.Activate

Else

'--- YES: Works on fixed width columns, faster
allTypes = "Text (*.txt),*.txt," & _
"Volume files (*.vol),*.vol," & _
"Edited BoComp output (*.rsmtxt),*.rsmtxt," & _
"All files (*.*),*.*"
filTyp = 1
filNamn = Application.GetOpenFilename(allTypes, filTyp, "Open"
, True)
'Think I got this one here at the forum once
If TypeName(filNamn) = "Boolean" Then Exit Sub
For i = 1 To UBound(filNamn)
Workbooks.OpenText Filename:=filNamn(i)
ActiveSheet.Move After:=wbkTarget.Sheet
(wbkTarget.Sheets.Count)
Next i

End If

End Sub


It works fine but doesn't look as professional as your second pos
...and I guess the "ActivateNext" reveals my "programming"(=recording
skill

 

[Guest]

Hi e18,

I'm glad you finally got the revised code. As I said in my email to you, I
need to do this frequently myself, and your request inspired me to do
something about that. I actually worked it through to make sure it handles
multi-selecting the files properly, because I didn't want to have to open
them individually if I got a quantity to import all at once. I also wanted to
be able to import single files without having to close any other workbooks. I
suppose I could combine everything into one procedure, but I can live with
doing things this way for now. Anyway, these are what I'm using and I'm more
than happy to share them.

Thanks for the inspiration!
Regards,
GS

 

[e18]

Hi again!

Never got that email of yours...
Anyways: If all your textfiles have fixed width columns you could try
the bottomlast -else- part of my macro. If it works it will save you
some boring clicking when you open a lot of files.

Regards
Erlend

just discovered the second flaw in my code:
-the first and obvious is the blank workbook with one
sheet left behind if choosing cancel in the open file dialog
-second: if the is macro run first thing after excel startup, the first
textfile replaces the default empty book1 at startup, and is not
collected together with the rest of the files.

 

[Guest]

Hi Erland,

<<If all your textfiles have fixed width columns you could try
the bottomlast -else- part of my macro>>
'** I usually import the textfiles with the default settings in the Import
Wizard. For example, when the wizard opens I just click Finish. I did add a
line to both procedures to autofit the columns though.


<<just discovered the second flaw in my code: -the first and obvious is the
blank workbook with one sheet left behind if choosing cancel in the open file
dialog>>
'** With my multi-select code, if the user cancels, nothing happens.
'** With the single-select code, if the user cancels, the active sheet gets
moved to the end. I will add the same check to see if the user cancelled.
-Thanks for pointing this out.


<<if the is macro run first thing after excel startup, the first textfile
replaces the default empty book1 at startup, and is not collected together
with the rest of the files>>
'** With both procedures, it is assumed the active workbook is a saved file.
If you're putting these into a new file then save the default Book1
beforehand. -Sorry I did not mention this, but my understanding was you are
periodically inserting textfiles into an existing workbook.

Regards,
Garry