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