Used Opentext command, - How do i select the pop-up Workbook?

[JKB]

Hi everybody.

First of all ill warn you that im new in this VBA-world!

My problem is that i have opened a text file, using the code:
Workbooks.OpenText Environ("Userprofile") & "\Documents\VBA halløj\IBM_TAQ", comma:=True

This command opens a new workbook called "IBM_TAQ". This is problematic for me since when i use "range" it selects the cells in the worksheet WITH the button i.e not the in-loaded textfile.

Does anybody know either how i can: Make excel open the document in the same worksheet where i placed the button to activate the code? (I would prefer a solution where the "Opentext" is used.)

Or how i activate the sheet "IBM_TAQ" instead so that i can use the "range command" in the correct workbook?

Hope somebody can help me!

JKB

[Aflatoon]

Don't use unqualified Range calls - always specify the worksheet. For example:

Dim sht as Worksheet
Workbooks.OpenText Environ("Userprofile") & "\Documents\VBA halløj\IBM_TAQ", comma:=True
set sht = Activeworkbook.Sheets(1)
' then refer to sht.Range(...) wherever you use Range

[JKB]

Okay - that is a great idea! And it seems to work for me, but only when using range to select single cells. I get error when using this code:

Private Sub CommandButton1_Click()


Dim sht As Worksheet
Workbooks.OpenText Environ("Userprofile") & "\Documents\VBA halløj\IBM_TAQ", comma:=True
Set sht = ActiveWorkbook.Sheets(1)

sht.Range("D1", Range("D1").End(xlDown)).Cut

Any idea why? - It says: "Application defined - Or object defined error"

[Aflatoon]

You need to qualify all the Range calls:

sht.Range("D1", sht.Range("D1").End(xlDown)).Cut

[JKB]

Ouch :P Thx for the help, hope my questions will be on a higher level next time :P

[Aflatoon]

Glad to help - and don't worry, we all started at the same level!