open file specified in a Cell location

[Aeneren]

Hello All,

Anyone tell me how to have the coding below open a specified file using the file location specified in a cell.

Please Login or Register  to view this content.

Thanks
Aeneren

[swoop99]

You'll need the entire path in the cell unless you can get the folder information from another source. So, if C6 = C:\Users\Brian\Desktop\Excell test folder\1903\4-29-2012-1903-Brian.xlsm....

Please Login or Register  to view this content.

[Aeneren]

Hello,

--swoop99 thanks for your reply,
place your code in and now am getting:

Run-time error '9'
Subscript out of range

Also the code is now copying the Formula from the sheet's instead of the results any idea.

Please Login or Register  to view this content.

Thanks
Aeneren

[swoop99]

See this page: http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm for advice on using something other than copy and paste (besides having more control, it's faster to avoid use of the clipboard).

Also, the reason it's bombing out is cos you have rem'd out the line that switches back to the workbook you just opened, therefore when you try to select sheet "Portable Suction" it can't find it. Is the name of the worksheet you open from C6 always the same length? If so, you can use something like:

Please Login or Register  to view this content.

If the filename varies in length then you'll have to parse it for slashes to extrapolate the workbook name to switch to.

[Aeneren]

Hello all,

--swoop99
Sorry for the confusion, ive been busy at work, and its taken this long to get back to you.
The below was how i first targeted the file before your Cell Target code.

Please Login or Register  to view this content.

And this bit was how i activated it between sections, with the copy and paste

Please Login or Register  to view this content.

This is the code as it is now that im working with.

Please Login or Register  to view this content.

And it still Debugs to the begining of the Second section, looked at the link you gave and gonna try some it.
If your up to it please feel free to give me any pointers.

Thanks
Aeneren

[nilem]

Hi Aeneren.Try it

Please Login or Register  to view this content.

[Aeneren]

Hello Nilem

Thank You Works great in the test books.
One more tweak please, the code is pasting the Formulas instead of the data

Aeneren

[nilem]

You only need the values? Try

Please Login or Register  to view this content.

[Aeneren]

Hello nilem,

Bow, Bow, Bow, Bow, Bow, Bow, Bow

Thank You My Friend now to place this in the full workbook of over 30+ sheets



Aeneren

[Aeneren]

Hello again,

--Nilem (or anyone who can please)
I've been trying to attach your code to the entire WB and have been having trouble trying to understand some of the code, could you possibly go line by line and Define what each line is doing for me.

Its probally something simple but this Novice is still learning

So i could posiblly track down why i have been getting a error of

Not Defined Page

Now i did have to move pages around and have been adding several more pages and fields to copy paste.

Please Login or Register  to view this content.

Above is the code i am using and have started to modify.


Thanks
Aeneren