Accessing workbook after .OpenText

[Phil_V]

Hi all,

I have the following code in an Excel document:

Please Login or Register  to view this content.

Ok, the problem I have at the moment is that I need to open this csv file, which is performed fine above into a new workbook. However how do I then get a 'pointer' to that workbook. I need to be able to perform operations on the opened .csv file, and my original workbook, (the one with the macro in it), and then copy data out of the .csv file into the original workbook.

I'm guessing it would be something like:

with Workbooks(2).Worksheets(1).... etc, but I don't know how to make sure I am talking about the right workbook.

Any ideas?

Thanks,

Phil

[Tom Ogilvy]

You have the name of the workbook in Name

Dim name
Dim fullname
Dim bk as Workbook
name = Dir(ActiveWorkbook.Path & "\*.csv")
While name <> ""
fullname = ActiveWorkbook.Path & "\" & name
MsgBox fullname
Workbooks.OpenText _
Filename:=fullname, _
DataType:=xlDelimited, _
comma:=True
set bk = workbooks(name)
' now use bk to refer to the workbook

' Some code to go here....
name = Dir
Wend

--
Regards,
Tom Ogilvy


"Phil_V" wrote:

>
> Hi all,
>
> I have the following code in an Excel document:
>
>
> Code:
> --------------------
> Dim name
> Dim fullname
> name = Dir(ActiveWorkbook.Path & "\*.csv")
> While name <> ""
> fullname = ActiveWorkbook.Path & "\" & name
> MsgBox fullname
> Workbooks.OpenText _
> Filename:=fullname, _
> DataType:=xlDelimited, _
> comma:=True
>
> ' Some code to go here....
> name = Dir
> Wend
> --------------------
>
>
> Ok, the problem I have at the moment is that I need to open this csv
> file, which is performed fine above into a new workbook. However how do
> I then get a 'pointer' to that workbook. I need to be able to perform
> operations on the opened .csv file, and my original workbook, (the one
> with the macro in it), and then copy data out of the .csv file into the
> original workbook.
>
> I'm guessing it would be something like:
>
> with Workbooks(2).Worksheets(1).... etc, but I don't know how to make
> sure I am talking about the right workbook.
>
> Any ideas?
>
> Thanks,
>
> Phil
>
>
> --
> Phil_V
> ------------------------------------------------------------------------
> Phil_V's Profile: http://www.excelforum.com/member.php...o&userid=31857
> View this thread: http://www.excelforum.com/showthread...hreadid=523144
>
>