How to open named Excel file & Worksheet

[dhiresh]

Hi VBA Experts…
I have the following code that opens a specific file. However the file Path & Name is hard coded.
Please could you help me amend such that –
a. User defines Path in cell “E14” of active worksheet called “RunCode_TEST”
b. User defines File name in “E16” of the same worksheet
c. User defines specific worksheet in cell “E18” of the same worksheet.

What I want is the code to look thru the Path (E14), opens Excel - namedfile (E16) and selects cell "A1" on the specified worksheet (E18).

The code I have is -

Please Login or Register  to view this content.

Thanking you in advance
Dhiresh

[ragulduy]

Please Login or Register  to view this content.

essentially,the path will need to end in "/" and the filename will need the extension.

[dhiresh]

Hi yudlugar,
Thx for your prompt reply.

I have very little knowledge of VBa and by replacing code with the above line you suggested is not working. Perhaps you could advise on how to amend my code.

rgds
Dhiresh

[ragulduy]

replace:

Please Login or Register  to view this content.

with the code I gave you, if that doesn't work try running this macro:

Please Login or Register  to view this content.

and see if it is giving you the path you expect...

[dhiresh]

Hi yudlugar,
Yes. it is not working.
many Thanks

Dhiresh

[ragulduy]

What is in cells E14 and E16

[dhiresh]

I have recheck & have managed to open the file but the actual worksheet is not selected. this is what I meant. sorry for being to vague.

[ragulduy]

Sorry, it should be
sheetname = Range("E18")
Workbooks.Open Filename:=Range("E14") & Range("E16")
Sheets(sheetname).Range("A1").select

[dhiresh]

I have tried the revised code and the file is opening as it should but the code is still not opening the named tab and Cell A1. Any advise!!!!

[ragulduy]

Please can you upload both workbooks (remove any sensitive data) with the code you are using and I'll take a look.

[dhiresh]

Hi.
Attaching 2 files as requested.

Rgds
D

[ragulduy]

You were close, you defined the sheetname as a worksheet and I used it as a string, you need to use:

Please Login or Register  to view this content.

[dhiresh]

HI.

Thx for update and I will try your code tomorrow morning & will update the thread appropraitely

Rgds
D

[dhiresh]

Hi,
I have copied your code on actual file and tested on a number of files and the file opens as expected but I can only see the first worksheet in all cases. Code to open the specified worksheet is not working.

Help!!!

D

[ragulduy]

One more go:

Please Login or Register  to view this content.

[dhiresh]

Excellent. it is now working.
Many thanks

Rgds
Dhiresh

[dhiresh]

Excellent. It is now working.
Many Thanks.

Rgds
D