I'm fairly new to VBA so I am wondering if someone can help. I have
a .txt file that I have imported into excel and I have been able to
delete all of the unwanted areas. I'm trying to find a way to cut and
paste this data into another workbook. However, the workbook is a
template so I have to fit the right data into the right position or
then when reading the data it will make no sense. Right now, I have
gotten to a point where I can copy then paste special, transpose, then
delete so it acts as a cut and paste. But I can't figure out how to
take that data and put it into a new workbook. Also, is there a way to
do a fixed width and use comma separators? Because when I try it does
nothing.
Carlos,
I'm not exactly clear as to what you are doing, but here are a few
ideas that may be helpful.
You could try saving the template file as a normal file and then
manipulate the normal file.
Workbooks can be referenced like worksheets--you can use an index
number or a name. For example, Workbooks(1).Activate will select the
workbook with an index number of 1 and the first worksheet in that
workbook. Or, you can use a naming structure.
Workbooks("Book1").Activate. An alternative way is to creat a
separate object using the Set statement. For example, Set wbk1 =
Workbooks(1) will create a workbook object that will behave like a
workbook object. You could then code wbk1.Activate and it will do the
same thing as Workbooks("Book1").Activate. As a side note, a For Each
wbk in Workbooks Loop is helpful in setting up workbook objects using
the Set statement. You could do something like the following:
wbk1 = ThisWorkbook
For Each wbk In Workbooks
If wbk.Name Like "Bo*" Then
Set wbk2 = wbk
End If
Next
wbk2.Activate
wbk1.Activate
Keep in mind though, that ANY workbook with a name thate starts with
"Bo" will be set to wbk2 (i.e. Book1, Book2, Boring Workbook, etc.)
because we are using the Like operator.
As for VBA code to do Text to Columns, you can try out the Split
Function or look up the Parse Function in the VBE Help. Split will
take a value and delimit it accordingly. For example, if you delimit
$A$1 by "$" then you can break up the address into a Column and
Number. The following should help
textVal = $A$1
split0 = Split(textVal, "$")(0)
'returns nothing
split1 = Split(textVal, "$")(1)
'returns A
split2 = Split(textVal, "$")(2)
'returns 1
split3 = Split(textVal, "$")(3)
'returns an error because it doesn't exist
The number at the end of the Split Function depends on how many
delimiters are in the value. "$A$1" has 2 delimiters, so there will
be 3 values available. "Me:You:Mike:Tom" has 3 delimiters, so ther
will be 4 values available.
I hope this helps and that it is along the lines of what you are
looking for.
Matt
