How do I import a file to Excel with a set of specifications? I don't want
to have to set the fixed length every time I want to import the file.
--
Thanks As Always
Rip

Saved from a previous post:

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way you
like. Insert columns, add headers, freeze panes, widen columns, add filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet in that
macro workbook and assign this macro to that big button. I'll add a few
instructions to that sheet, too.

If the file name to open is always the same and in the same location, then I'm
about done. If the location or file name changes, I'll tweak the code to ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

Ripper wrote:

How do I import a file to Excel with a set of specifications? I don't want
to have to set the fixed length every time I want to import the file.
--
Thanks As Always
Rip

--

Dave Peterson

Dave,

How do I modify/add to this code so that once the text file has been opened
I can go back and forth to it not knowing the name of the file until after
it's been selected?

ie I think I need to declare a different variable for the name of the file I
just opened. The "myFileName" refers to the full path and I think I just
want the name of the file.

eg in my code I have
Windows("SupporterStats.txt").Activate
But I want to use the code, below, to open any text file and then run some
more code, before acting again on the unknown name text file.

Rob

"Dave Peterson" wrote in message
...
Saved from a previous post:

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way you
like. Insert columns, add headers, freeze panes, widen columns, add
filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet in
that
macro workbook and assign this macro to that big button. I'll add a few
instructions to that sheet, too.

If the file name to open is always the same and in the same location, then
I'm
about done. If the location or file name changes, I'll tweak the code to
ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code
here!

End Sub

Ripper wrote:

How do I import a file to Excel with a set of specifications? I don't
want
to have to set the fixed length every time I want to import the file.
--
Thanks As Always
Rip

--

Dave Peterson

I'd stay away from using the filename and I'd stay away from using the windows
collection, too. You can make a couple more variables that represent the
worksheet and workbook that holds the imported data.

Option Explicit
Sub Testme01()

Dim myFileName As Variant
dim wks as worksheet
dim wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

'while the .txt file is the activesheet
set wks = activesheet
set wkbk = activeworkbook 'or wks.parent

'then you can use:

with wks
.range("a1").value = "hi there"
end with


End Sub

RobN wrote:

Dave,

How do I modify/add to this code so that once the text file has been opened
I can go back and forth to it not knowing the name of the file until after
it's been selected?

ie I think I need to declare a different variable for the name of the file I
just opened. The "myFileName" refers to the full path and I think I just
want the name of the file.

eg in my code I have
Windows("SupporterStats.txt").Activate
But I want to use the code, below, to open any text file and then run some
more code, before acting again on the unknown name text file.

Rob

"Dave Peterson" wrote in message
...
Saved from a previous post:

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way you
like. Insert columns, add headers, freeze panes, widen columns, add
filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet in
that
macro workbook and assign this macro to that big button. I'll add a few
instructions to that sheet, too.

If the file name to open is always the same and in the same location, then
I'm
about done. If the location or file name changes, I'll tweak the code to
ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code
here!

End Sub

Ripper wrote:

How do I import a file to Excel with a set of specifications? I don't
want
to have to set the fixed length every time I want to import the file.
--
Thanks As Always
Rip

--

Dave Peterson

--

Dave Peterson

Thanks Dave, very useful as usual!

Could you explain though why, as you say, "I'd stay away from using the
filename and I'd stay away from using the windows
collection, too"?

I've done that a lot in various codes I've created and also from code I've
gleaned here, without a problem.

Could you also please explain the dot that proceeds lines of code within
With & End With statements. What are the general rules with that?

Rob

"Dave Peterson" wrote in message
...
I'd stay away from using the filename and I'd stay away from using the
windows
collection, too. You can make a couple more variables that represent the
worksheet and workbook that holds the imported data.

Option Explicit
Sub Testme01()

Dim myFileName As Variant
dim wks as worksheet
dim wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code
here!

'while the .txt file is the activesheet
set wks = activesheet
set wkbk = activeworkbook 'or wks.parent

'then you can use:

with wks
.range("a1").value = "hi there"
end with


End Sub

RobN wrote:

Dave,

How do I modify/add to this code so that once the text file has been
opened
I can go back and forth to it not knowing the name of the file until
after
it's been selected?

ie I think I need to declare a different variable for the name of the
file I
just opened. The "myFileName" refers to the full path and I think I just
want the name of the file.

eg in my code I have
Windows("SupporterStats.txt").Activate
But I want to use the code, below, to open any text file and then run
some
more code, before acting again on the unknown name text file.

Rob

"Dave Peterson" wrote in message
...
Saved from a previous post:

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way
you
like. Insert columns, add headers, freeze panes, widen columns, add
filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet
in
that
macro workbook and assign this macro to that big button. I'll add a
few
instructions to that sheet, too.

If the file name to open is always the same and in the same location,
then
I'm
about done. If the location or file name changes, I'll tweak the code
to
ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code
here!

End Sub

Ripper wrote:

How do I import a file to Excel with a set of specifications? I don't
want
to have to set the fixed length every time I want to import the file.
--
Thanks As Always
Rip

--

Dave Peterson

--

Dave Peterson

The easier one first.

You have to match the windows name exactly. So if the user does Window|New
window, you'll see something like: Textfile.txt:1 and textfile.txt:2.

So referring to windows("textfile.txt") will fail.

And (to me), it's more work to parse the name of the file than it is to just use
a variable that represents the workbook or worksheet.

In fact, I wouldn't use a workbook variable (well, sometimes). I'd just use:

dim wks as worksheet
workbooks.opentext ....
set wks = activesheet

Then if I need the workbook, I'd just use wks.parent.

And I think that using nice variable names makes the code easier to debug later
on:

TextWks.range("a1").value = "hi"

workbooks(filenamevariable).worksheets(1).range("a 1").value = "hi"






RobN wrote:

Thanks Dave, very useful as usual!

Could you explain though why, as you say, "I'd stay away from using the
filename and I'd stay away from using the windows
collection, too"?

I've done that a lot in various codes I've created and also from code I've
gleaned here, without a problem.

Could you also please explain the dot that proceeds lines of code within
With & End With statements. What are the general rules with that?

Rob

"Dave Peterson" wrote in message
...
I'd stay away from using the filename and I'd stay away from using the
windows
collection, too. You can make a couple more variables that represent the
worksheet and workbook that holds the imported data.

Option Explicit
Sub Testme01()

Dim myFileName As Variant
dim wks as worksheet
dim wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code
here!

'while the .txt file is the activesheet
set wks = activesheet
set wkbk = activeworkbook 'or wks.parent

'then you can use:

with wks
.range("a1").value = "hi there"
end with


End Sub

RobN wrote:

Dave,

How do I modify/add to this code so that once the text file has been
opened
I can go back and forth to it not knowing the name of the file until
after
it's been selected?

ie I think I need to declare a different variable for the name of the
file I
just opened. The "myFileName" refers to the full path and I think I just
want the name of the file.

eg in my code I have
Windows("SupporterStats.txt").Activate
But I want to use the code, below, to open any text file and then run
some
more code, before acting again on the unknown name text file.

Rob

"Dave Peterson" wrote in message
...
Saved from a previous post:

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way
you
like. Insert columns, add headers, freeze panes, widen columns, add
filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet
in
that
macro workbook and assign this macro to that big button. I'll add a
few
instructions to that sheet, too.

If the file name to open is always the same and in the same location,
then
I'm
about done. If the location or file name changes, I'll tweak the code
to
ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code
here!

End Sub

Ripper wrote:

How do I import a file to Excel with a set of specifications? I don't
want
to have to set the fixed length every time I want to import the file.
--
Thanks As Always
Rip

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson