In my EXCEL template, I have to import a lot of text files that come in as
CSVs. Setting the boundaries turns out to be very time-consuming.
Assuming, for the sake of argument, that I could find some rules as to where
to set boundaries, is there any way that a macro could be written to have it
delete all preset boundaries and then insert boundaries between the columns
based on these rules?

Thanks!
Grace

Grace
If you can define boundaries then VBA can set them.
Cheers
Nigel

"Grace" wrote in message
...
In my EXCEL template, I have to import a lot of text files that come in as
CSVs. Setting the boundaries turns out to be very time-consuming.
Assuming, for the sake of argument, that I could find some rules as to
where
to set boundaries, is there any way that a macro could be written to have
it
delete all preset boundaries and then insert boundaries between the
columns
based on these rules?

Thanks!
Grace

Thank you, Nigel. This is great news. I am starting to believe that EXCEL
can be automated to clean windows, if I can ever get as smart as you guys!
Normally, when you open a CSV file, it has certain boundaries it thinks are
right and you have to delete them if you don't want them, in addition to
inserting ones that you do like. However, it looks like the recorded
commands can just include the boundaries where you want them. Does that
sound correct?

I have tried to piece together what I need from some of the other samples I
have gotten here but am getting a compile error. It seems like that,
whenever a command wraps around to the next line, when I try to copy it,
something gets messed up, especially because it is hard to tell how many
spacebars are being used. In any event,the compile error is in this row (I
think the problem is right after the Open(F) part): Maybe an extra space or
misplaced comma. Can you find the error?

Set wbSource = Workbooks.Open(F) _ , Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(56, 1), Array(58, 2), Array(67, 1),
Array(76, 1), Array(85 _
, 1), Array(99, 1), Array(109, 1), Array(117, 1)),
TrailingMinusNumbers:=True

I have one other question. The source file F will not be an EXCEL file, in
this case, but a simple text file. Do I need to change the command:

F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")

here, to accommodate that?

Thanks,
Grace

"Nigel" wrote in message
...
Grace
If you can define boundaries then VBA can set them.
Cheers
Nigel

"Grace" wrote in message
...
In my EXCEL template, I have to import a lot of text files that come in
as
CSVs. Setting the boundaries turns out to be very time-consuming.
Assuming, for the sake of argument, that I could find some rules as to
where
to set boundaries, is there any way that a macro could be written to
have
it
delete all preset boundaries and then insert boundaries between the
columns
based on these rules?

Thanks!
Grace

As long as you're opening .txt files (well, any plain text file that doesn't
have an extension of .CSV), you should be ok. But VBA treats .CSV files like
comma separated values. Your code is pretty much ignored with .CSV's.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim wbSource As Workbook

myFileName = Application.GetOpenFilename("Text files, *.txt")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'....

End Sub

(I don't like one character variables--personal preference only!)

========
You can even import multiple files (in the same folder). Just click on the
first and ctrl-click on subsequent:

Option Explicit
Sub testme2()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wbSource As Workbook

myFileNames = Application.GetOpenFilename _
(filefilter:="Text files, *.txt", MultiSelect:=True)

If IsArray(myFileNames) Then
'keep going
Else
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'do more things (copy it elsewhere???)
'maybe even close it without saving
wbSource.Close savechanges:=False

Next iCtr

'....

End Sub




Grace wrote:

Thank you, Nigel. This is great news. I am starting to believe that EXCEL
can be automated to clean windows, if I can ever get as smart as you guys!
Normally, when you open a CSV file, it has certain boundaries it thinks are
right and you have to delete them if you don't want them, in addition to
inserting ones that you do like. However, it looks like the recorded
commands can just include the boundaries where you want them. Does that
sound correct?

I have tried to piece together what I need from some of the other samples I
have gotten here but am getting a compile error. It seems like that,
whenever a command wraps around to the next line, when I try to copy it,
something gets messed up, especially because it is hard to tell how many
spacebars are being used. In any event,the compile error is in this row (I
think the problem is right after the Open(F) part): Maybe an extra space or
misplaced comma. Can you find the error?

Set wbSource = Workbooks.Open(F) _ , Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(56, 1), Array(58, 2), Array(67, 1),
Array(76, 1), Array(85 _
, 1), Array(99, 1), Array(109, 1), Array(117, 1)),
TrailingMinusNumbers:=True

I have one other question. The source file F will not be an EXCEL file, in
this case, but a simple text file. Do I need to change the command:

F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")

here, to accommodate that?

Thanks,
Grace

"Nigel" wrote in message
...
Grace
If you can define boundaries then VBA can set them.
Cheers
Nigel

"Grace" wrote in message
...
In my EXCEL template, I have to import a lot of text files that come in
as
CSVs. Setting the boundaries turns out to be very time-consuming.
Assuming, for the sake of argument, that I could find some rules as to
where
to set boundaries, is there any way that a macro could be written to
have
it
delete all preset boundaries and then insert boundaries between the
columns
based on these rules?

Thanks!
Grace





--

Dave Peterson

I must be a GD idiot! Your testme macro worked great. Then, I tried to add
on (before and after) to it, so as to copy and paste it into the calling
workbook and it is stopping at:

Windows(wbTarget).Activate

in the macro below. Please look at the macro below and tell me what stupid
thing is wrong. THANKS. I am trying to get it to paste the result back
into the calling workbook:

Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace
'

'
Dim myFileName As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True
Set wbSource = ActiveWorkbook
Range("A1:J2000").Select '
Selection.Copy
Windows(wbTarget).Activate
Sheets("EOPOutlier").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

"Dave Peterson" wrote in message
...
As long as you're opening .txt files (well, any plain text file that
doesn't
have an extension of .CSV), you should be ok. But VBA treats .CSV files
like
comma separated values. Your code is pretty much ignored with .CSV's.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim wbSource As Workbook

myFileName = Application.GetOpenFilename("Text files, *.txt")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'....

End Sub

(I don't like one character variables--personal preference only!)

========
You can even import multiple files (in the same folder). Just click on
the
first and ctrl-click on subsequent:

Option Explicit
Sub testme2()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wbSource As Workbook

myFileNames = Application.GetOpenFilename _
(filefilter:="Text files, *.txt", MultiSelect:=True)

If IsArray(myFileNames) Then
'keep going
Else
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'do more things (copy it elsewhere???)
'maybe even close it without saving
wbSource.Close savechanges:=False

Next iCtr

'....

End Sub




Grace wrote:

Thank you, Nigel. This is great news. I am starting to believe that
EXCEL
can be automated to clean windows, if I can ever get as smart as you
guys!
Normally, when you open a CSV file, it has certain boundaries it thinks
are
right and you have to delete them if you don't want them, in addition to
inserting ones that you do like. However, it looks like the recorded
commands can just include the boundaries where you want them. Does that
sound correct?

I have tried to piece together what I need from some of the other
samples I
have gotten here but am getting a compile error. It seems like that,
whenever a command wraps around to the next line, when I try to copy it,
something gets messed up, especially because it is hard to tell how many
spacebars are being used. In any event,the compile error is in this row
(I
think the problem is right after the Open(F) part): Maybe an extra
space or
misplaced comma. Can you find the error?

Set wbSource = Workbooks.Open(F) _ , Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(56, 1), Array(58, 2), Array(67,
1),
Array(76, 1), Array(85 _
, 1), Array(99, 1), Array(109, 1), Array(117, 1)),
TrailingMinusNumbers:=True

I have one other question. The source file F will not be an EXCEL file,
in
this case, but a simple text file. Do I need to change the command:

F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")

here, to accommodate that?

Thanks,
Grace

"Nigel" wrote in message
...
Grace
If you can define boundaries then VBA can set them.
Cheers
Nigel

"Grace" wrote in message
...
In my EXCEL template, I have to import a lot of text files that come
in
as
CSVs. Setting the boundaries turns out to be very time-consuming.
Assuming, for the sake of argument, that I could find some rules as
to
where
to set boundaries, is there any way that a macro could be written to
have
it
delete all preset boundaries and then insert boundaries between the
columns
based on these rules?

Thanks!
Grace





--

Dave Peterson

Since you're pasting values, how about just plopping the values into the range.

I used column A to get the last row of the imported text file:

Option Explicit
Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace

Dim myFileName As Variant
Dim wbTarget As Workbook
Dim RngToCopy As Range

Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

With ActiveSheet
Set RngToCopy = .Range("A1:J" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

wbTarget.Worksheets("EOPOutlier").Range("a1") _
.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub

Grace wrote:

I must be a GD idiot! Your testme macro worked great. Then, I tried to add
on (before and after) to it, so as to copy and paste it into the calling
workbook and it is stopping at:

Windows(wbTarget).Activate

in the macro below. Please look at the macro below and tell me what stupid
thing is wrong. THANKS. I am trying to get it to paste the result back
into the calling workbook:

Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace
'

'
Dim myFileName As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True
Set wbSource = ActiveWorkbook
Range("A1:J2000").Select '
Selection.Copy
Windows(wbTarget).Activate
Sheets("EOPOutlier").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

"Dave Peterson" wrote in message
...
As long as you're opening .txt files (well, any plain text file that
doesn't
have an extension of .CSV), you should be ok. But VBA treats .CSV files
like
comma separated values. Your code is pretty much ignored with .CSV's.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim wbSource As Workbook

myFileName = Application.GetOpenFilename("Text files, *.txt")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'....

End Sub

(I don't like one character variables--personal preference only!)

========
You can even import multiple files (in the same folder). Just click on
the
first and ctrl-click on subsequent:

Option Explicit
Sub testme2()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wbSource As Workbook

myFileNames = Application.GetOpenFilename _
(filefilter:="Text files, *.txt", MultiSelect:=True)

If IsArray(myFileNames) Then
'keep going
Else
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'do more things (copy it elsewhere???)
'maybe even close it without saving
wbSource.Close savechanges:=False

Next iCtr

'....

End Sub




Grace wrote:

Thank you, Nigel. This is great news. I am starting to believe that
EXCEL
can be automated to clean windows, if I can ever get as smart as you
guys!
Normally, when you open a CSV file, it has certain boundaries it thinks
are
right and you have to delete them if you don't want them, in addition to
inserting ones that you do like. However, it looks like the recorded
commands can just include the boundaries where you want them. Does that
sound correct?

I have tried to piece together what I need from some of the other
samples I
have gotten here but am getting a compile error. It seems like that,
whenever a command wraps around to the next line, when I try to copy it,
something gets messed up, especially because it is hard to tell how many
spacebars are being used. In any event,the compile error is in this row
(I
think the problem is right after the Open(F) part): Maybe an extra
space or
misplaced comma. Can you find the error?

Set wbSource = Workbooks.Open(F) _ , Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(56, 1), Array(58, 2), Array(67,
1),
Array(76, 1), Array(85 _
, 1), Array(99, 1), Array(109, 1), Array(117, 1)),
TrailingMinusNumbers:=True

I have one other question. The source file F will not be an EXCEL file,
in
this case, but a simple text file. Do I need to change the command:

F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")

here, to accommodate that?

Thanks,
Grace

"Nigel" wrote in message
...
Grace
If you can define boundaries then VBA can set them.
Cheers
Nigel

"Grace" wrote in message
...
In my EXCEL template, I have to import a lot of text files that come
in
as
CSVs. Setting the boundaries turns out to be very time-consuming.
Assuming, for the sake of argument, that I could find some rules as
to
where
to set boundaries, is there any way that a macro could be written to
have
it
delete all preset boundaries and then insert boundaries between the
columns
based on these rules?

Thanks!
Grace





--

Dave Peterson


--

Dave Peterson

It is bombing out on your last command. I get Application defined or object
defined error. It seems to have copied in only about the top ten rows into
the EOPOutlier sheet. In case it matters, the target file has protection
installed to the right of column J.

Dean

"Dave Peterson" wrote in message
...
Since you're pasting values, how about just plopping the values into the
range.

I used column A to get the last row of the imported text file:

Option Explicit
Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace

Dim myFileName As Variant
Dim wbTarget As Workbook
Dim RngToCopy As Range

Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

With ActiveSheet
Set RngToCopy = .Range("A1:J" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
End With

wbTarget.Worksheets("EOPOutlier").Range("a1") _
.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub

Grace wrote:

I must be a GD idiot! Your testme macro worked great. Then, I tried to
add
on (before and after) to it, so as to copy and paste it into the calling
workbook and it is stopping at:

Windows(wbTarget).Activate

in the macro below. Please look at the macro below and tell me what
stupid
thing is wrong. THANKS. I am trying to get it to paste the result back
into the calling workbook:

Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace
'

'
Dim myFileName As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True
Set wbSource = ActiveWorkbook
Range("A1:J2000").Select '
Selection.Copy
Windows(wbTarget).Activate
Sheets("EOPOutlier").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

"Dave Peterson" wrote in message
...
As long as you're opening .txt files (well, any plain text file that
doesn't
have an extension of .CSV), you should be ok. But VBA treats .CSV
files
like
comma separated values. Your code is pretty much ignored with .CSV's.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim wbSource As Workbook

myFileName = Application.GetOpenFilename("Text files, *.txt")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'....

End Sub

(I don't like one character variables--personal preference only!)

========
You can even import multiple files (in the same folder). Just click
on
the
first and ctrl-click on subsequent:

Option Explicit
Sub testme2()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wbSource As Workbook

myFileNames = Application.GetOpenFilename _
(filefilter:="Text files, *.txt", MultiSelect:=True)

If IsArray(myFileNames) Then
'keep going
Else
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'do more things (copy it elsewhere???)
'maybe even close it without saving
wbSource.Close savechanges:=False

Next iCtr

'....

End Sub




Grace wrote:

Thank you, Nigel. This is great news. I am starting to believe
that
EXCEL
can be automated to clean windows, if I can ever get as smart as you
guys!
Normally, when you open a CSV file, it has certain boundaries it
thinks
are
right and you have to delete them if you don't want them, in
addition to
inserting ones that you do like. However, it looks like the
recorded
commands can just include the boundaries where you want them. Does
that
sound correct?

I have tried to piece together what I need from some of the other
samples I
have gotten here but am getting a compile error. It seems like
that,
whenever a command wraps around to the next line, when I try to copy
it,
something gets messed up, especially because it is hard to tell how
many
spacebars are being used. In any event,the compile error is in this
row
(I
think the problem is right after the Open(F) part): Maybe an extra
space or
misplaced comma. Can you find the error?

Set wbSource = Workbooks.Open(F) _ , Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(56, 1), Array(58, 2),
Array(67,
1),
Array(76, 1), Array(85 _
, 1), Array(99, 1), Array(109, 1), Array(117, 1)),
TrailingMinusNumbers:=True

I have one other question. The source file F will not be an EXCEL
file,
in
this case, but a simple text file. Do I need to change the command:

F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")

here, to accommodate that?

Thanks,
Grace

"Nigel" wrote in message
...
Grace
If you can define boundaries then VBA can set them.
Cheers
Nigel

"Grace" wrote in message
...
In my EXCEL template, I have to import a lot of text files that
come
in
as
CSVs. Setting the boundaries turns out to be very
time-consuming.
Assuming, for the sake of argument, that I could find some rules
as
to
where
to set boundaries, is there any way that a macro could be
written to
have
it
delete all preset boundaries and then insert boundaries between
the
columns
based on these rules?

Thanks!
Grace





--

Dave Peterson


--

Dave Peterson

It uses column A to determine the last row to copy. Is that ok?

And I'd try it with the protection off. Maybe there's a locked cell where you
don't expect it.

If it works, try looking for that locked cell.

Grace wrote:

It is bombing out on your last command. I get Application defined or object
defined error. It seems to have copied in only about the top ten rows into
the EOPOutlier sheet. In case it matters, the target file has protection
installed to the right of column J.

Dean

"Dave Peterson" wrote in message
...
Since you're pasting values, how about just plopping the values into the
range.

I used column A to get the last row of the imported text file:

Option Explicit
Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace

Dim myFileName As Variant
Dim wbTarget As Workbook
Dim RngToCopy As Range

Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

With ActiveSheet
Set RngToCopy = .Range("A1:J" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
End With

wbTarget.Worksheets("EOPOutlier").Range("a1") _
.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub

Grace wrote:

I must be a GD idiot! Your testme macro worked great. Then, I tried to
add
on (before and after) to it, so as to copy and paste it into the calling
workbook and it is stopping at:

Windows(wbTarget).Activate

in the macro below. Please look at the macro below and tell me what
stupid
thing is wrong. THANKS. I am trying to get it to paste the result back
into the calling workbook:

Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace
'

'
Dim myFileName As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True
Set wbSource = ActiveWorkbook
Range("A1:J2000").Select '
Selection.Copy
Windows(wbTarget).Activate
Sheets("EOPOutlier").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

"Dave Peterson" wrote in message
...
As long as you're opening .txt files (well, any plain text file that
doesn't
have an extension of .CSV), you should be ok. But VBA treats .CSV
files
like
comma separated values. Your code is pretty much ignored with .CSV's.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim wbSource As Workbook

myFileName = Application.GetOpenFilename("Text files, *.txt")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'....

End Sub

(I don't like one character variables--personal preference only!)

========
You can even import multiple files (in the same folder). Just click
on
the
first and ctrl-click on subsequent:

Option Explicit
Sub testme2()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wbSource As Workbook

myFileNames = Application.GetOpenFilename _
(filefilter:="Text files, *.txt", MultiSelect:=True)

If IsArray(myFileNames) Then
'keep going
Else
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'do more things (copy it elsewhere???)
'maybe even close it without saving
wbSource.Close savechanges:=False

Next iCtr

'....

End Sub




Grace wrote:

Thank you, Nigel. This is great news. I am starting to believe
that
EXCEL
can be automated to clean windows, if I can ever get as smart as you
guys!
Normally, when you open a CSV file, it has certain boundaries it
thinks
are
right and you have to delete them if you don't want them, in
addition to
inserting ones that you do like. However, it looks like the
recorded
commands can just include the boundaries where you want them. Does
that
sound correct?

I have tried to piece together what I need from some of the other
samples I
have gotten here but am getting a compile error. It seems like
that,
whenever a command wraps around to the next line, when I try to copy
it,
something gets messed up, especially because it is hard to tell how
many
spacebars are being used. In any event,the compile error is in this
row
(I
think the problem is right after the Open(F) part): Maybe an extra
space or
misplaced comma. Can you find the error?

Set wbSource = Workbooks.Open(F) _ , Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(56, 1), Array(58, 2),
Array(67,
1),
Array(76, 1), Array(85 _
, 1), Array(99, 1), Array(109, 1), Array(117, 1)),
TrailingMinusNumbers:=True

I have one other question. The source file F will not be an EXCEL
file,
in
this case, but a simple text file. Do I need to change the command:

F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")

here, to accommodate that?

Thanks,
Grace

"Nigel" wrote in message
...
Grace
If you can define boundaries then VBA can set them.
Cheers
Nigel

"Grace" wrote in message
...
In my EXCEL template, I have to import a lot of text files that
come
in
as
CSVs. Setting the boundaries turns out to be very
time-consuming.
Assuming, for the sake of argument, that I could find some rules
as
to
where
to set boundaries, is there any way that a macro could be
written to
have
it
delete all preset boundaries and then insert boundaries between
the
columns
based on these rules?

Thanks!
Grace





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Actually, in the interim, I dumbed it down. I simply copied from a1:J200 of
the source to a1:J2000 of the target, including lots of blank rows, I'm
sure. It doesn't need to be as smart as you are!

Thanks!

"Dave Peterson" wrote in message
...
It uses column A to determine the last row to copy. Is that ok?

And I'd try it with the protection off. Maybe there's a locked cell where
you
don't expect it.

If it works, try looking for that locked cell.

Grace wrote:

It is bombing out on your last command. I get Application defined or
object
defined error. It seems to have copied in only about the top ten rows
into
the EOPOutlier sheet. In case it matters, the target file has
protection
installed to the right of column J.

Dean

"Dave Peterson" wrote in message
...
Since you're pasting values, how about just plopping the values into
the
range.

I used column A to get the last row of the imported text file:

Option Explicit
Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace

Dim myFileName As Variant
Dim wbTarget As Workbook
Dim RngToCopy As Range

Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

With ActiveSheet
Set RngToCopy = .Range("A1:J" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
End With

wbTarget.Worksheets("EOPOutlier").Range("a1") _
.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value _
= RngToCopy.Value

End Sub

Grace wrote:

I must be a GD idiot! Your testme macro worked great. Then, I
tried to
add
on (before and after) to it, so as to copy and paste it into the
calling
workbook and it is stopping at:

Windows(wbTarget).Activate

in the macro below. Please look at the macro below and tell me what
stupid
thing is wrong. THANKS. I am trying to get it to paste the result
back
into the calling workbook:

Sub EOP_Audit_Import()
'
' Better_Audit_Import Macro
' Macro recorded 6/7/2004 by Grace
'

'
Dim myFileName As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbTarget = ActiveWorkbook
myFileName = Application.GetOpenFilename("All files, *.*")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True
Set wbSource = ActiveWorkbook
Range("A1:J2000").Select '
Selection.Copy
Windows(wbTarget).Activate
Sheets("EOPOutlier").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

"Dave Peterson" wrote in message
...
As long as you're opening .txt files (well, any plain text file
that
doesn't
have an extension of .CSV), you should be ok. But VBA treats .CSV
files
like
comma separated values. Your code is pretty much ignored with
..CSV's.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim wbSource As Workbook

myFileName = Application.GetOpenFilename("Text files, *.txt")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1), _
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'....

End Sub

(I don't like one character variables--personal preference only!)

========
You can even import multiple files (in the same folder). Just
click
on
the
first and ctrl-click on subsequent:

Option Explicit
Sub testme2()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wbSource As Workbook

myFileNames = Application.GetOpenFilename _
(filefilter:="Text files, *.txt",
MultiSelect:=True)

If IsArray(myFileNames) Then
'keep going
Else
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(56, 1),
_
Array(58, 2), Array(67, 1), Array(76, 1), Array(85, 1), _
Array(99, 1), Array(109, 1), Array(117, 1)), _
TrailingMinusNumbers:=True

Set wbSource = ActiveWorkbook
'do more things (copy it elsewhere???)
'maybe even close it without saving
wbSource.Close savechanges:=False

Next iCtr

'....

End Sub




Grace wrote:

Thank you, Nigel. This is great news. I am starting to believe
that
EXCEL
can be automated to clean windows, if I can ever get as smart as
you
guys!
Normally, when you open a CSV file, it has certain boundaries it
thinks
are
right and you have to delete them if you don't want them, in
addition to
inserting ones that you do like. However, it looks like the
recorded
commands can just include the boundaries where you want them.
Does
that
sound correct?

I have tried to piece together what I need from some of the
other
samples I
have gotten here but am getting a compile error. It seems like
that,
whenever a command wraps around to the next line, when I try to
copy
it,
something gets messed up, especially because it is hard to tell
how
many
spacebars are being used. In any event,the compile error is in
this
row
(I
think the problem is right after the Open(F) part): Maybe an
extra
space or
misplaced comma. Can you find the error?

Set wbSource = Workbooks.Open(F) _ , Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(10, 1), Array(56, 1), Array(58, 2),
Array(67,
1),
Array(76, 1), Array(85 _
, 1), Array(99, 1), Array(109, 1), Array(117, 1)),
TrailingMinusNumbers:=True

I have one other question. The source file F will not be an
EXCEL
file,
in
this case, but a simple text file. Do I need to change the
command:

F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")

here, to accommodate that?

Thanks,
Grace

"Nigel" wrote in message
...
Grace
If you can define boundaries then VBA can set them.
Cheers
Nigel

"Grace" wrote in message
...
In my EXCEL template, I have to import a lot of text files
that
come
in
as
CSVs. Setting the boundaries turns out to be very
time-consuming.
Assuming, for the sake of argument, that I could find some
rules
as
to
where
to set boundaries, is there any way that a macro could be
written to
have
it
delete all preset boundaries and then insert boundaries
between
the
columns
based on these rules?

Thanks!
Grace





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson