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

--
e1
-----------------------------------------------------------------------
e18's Profile: http://www.excelforum.com/member.php...nfo&userid=735
View this thread: http://www.excelforum.com/showthread.php?threadid=51841

Application.Dialogs(xlDialogOpen).Show


then select a text file and click open.

--
Regards,
Tom Ogilvy




"e18" wrote in message
...

I often open textfiles in Excel and stores them as sheets in one
workbook. I have a macro for this which I have to edit almost everytime
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 the
macro. I guess(hope) there is an easy answer to this, but I still have
not figured it out on my own...

Thakk for all hjelp!


--
e18
------------------------------------------------------------------------
e18's Profile:
http://www.excelforum.com/member.php...fo&userid=7355
View this thread: http://www.excelforum.com/showthread...hreadid=518411

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

Best regards
Erlend


--
e18
------------------------------------------------------------------------
e18's Profile: http://www.excelforum.com/member.php...fo&userid=7355
View this thread: http://www.excelforum.com/showthread...hreadid=518411

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

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

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

--
e1
-----------------------------------------------------------------------
e18's Profile: http://www.excelforum.com/member.php...nfo&userid=735
View this thread: http://www.excelforum.com/showthread.php?threadid=51841

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

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.


--
e18
------------------------------------------------------------------------
e18's Profile: http://www.excelforum.com/member.php...fo&userid=7355
View this thread: http://www.excelforum.com/showthread...hreadid=518411

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