Taking workbook names from cells and putting them in VBA

Be something like:

Please Login or Register  to view this content.

Regards, TMS

Hi MichaelMcF,

there is a function to get the file name if you implement it set a variable with it and input it in your code you´ll be fine whatever happens. Gimme a sec I´ll look where I have it dont remember the syntax by book....;-)

Best regards

Soul

PS -- so you dont need to keep the cell reference for the book name

EDIT:

Please Login or Register  to view this content.

there you go

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Generally speaking, if you drop a formula into cell with a reference to a workbook (or, I think, a worksheet), an the reference cannot be resolved, you will get a dialogue box displayed so that you can locate the file.

So, first question: is the workbook open?

Regards, TMS

If you're only accessing it to retrieve data, you could always open it in the background, Read-Only, using code before you drop the formula.


Regards, TMS

Hi,

I´m skipping the first part about the prompt, because I´m not exactly sure the conditions under which it occurs (and I think it shouldnt be happening with the solution I´ve proposed). But regarding the second part - getting data from another file; that is absolutely no problem, you can create a little userform with textbox where you fill in the path to the file and than all the magic can start...;-).

Best regards

Soul

EDIT: and btw. some prompts you can bypass with this piece of coding:

Please Login or Register  to view this content.

but as i said above, I dont have a clear idea what issue you´re facing so it might be useless what I´ve just gave you. This helps for example against the dialog box which occurs when you´re openin xls (that tells you it old format or whatever it tells i know i just click OK and dont even read it)