View Full Version : Used Opentext command, - How do i select the pop-up Workbook?

07-09-2014, 04:47 AM
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!


07-09-2014, 05:00 AM
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

07-09-2014, 05:17 AM
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"

07-09-2014, 05:33 AM
You need to qualify all the Range calls:

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

07-09-2014, 05:35 AM
Ouch :P Thx for the help, hope my questions will be on a higher level next time :P

07-09-2014, 05:48 AM
Glad to help - and don't worry, we all started at the same level! :)