Close file via cell reference

Hi

I have this in my macro which works fine..

Please Login or Register  to view this content.

does any one know something similar to close the file again?

Right now i use this:

Please Login or Register  to view this content.

But i want it to close the file by the contains of Q20 (just like when it opens)

Presumably in cell Q10 you have something like C:\Temp\Spreadsheet1.xlsm am I correct? And using Windows(Range("Q10").Value).Close False is failing?

yes with this: Windows(Range("Q20").Value).Close False i get a bug..
in Q20 i have C:\Users\DKJUHMET\Desktop\test.xlsm

I found a solution In Q21 i just write test.xlsm and then i works

Please Login or Register  to view this content.

That works, or to be consistent (i.e. avoid errors that the name in Q10 and Q11 are different), you can either formulate Q11 to extract Test.xlsm out. Or in your code, do

Please Login or Register  to view this content.

Thanks

do you know if i can use this:
Workbooks.Open Range("Q20").Value, ReadOnly:=True, UpdateLinks:=True

To open a specific sheet by cell value also ?
Sheet name GRAF in cell Q22

To open a specific cell, I believe you'll need to do something like this.

Please Login or Register  to view this content.

it works, but i dont what any names in the macro, only references, so can it be done without the "GRAF" ?

Yes, you can, but you will need to record your current inputs first as you will be changing your active workbook and sheets
For example if in Q12 you have "GRAF", and Q13 you have "Q22" then

Please Login or Register  to view this content.

i cant get i to work :/

Hmm.. it worked on mine. Although, I did notice a few things
1) There's a stray quotation mark on

Please Login or Register  to view this content.

Remove it.

2) When I saw cell Q12 should read "GRAF", I actually intended for you to type in GRAF into cell Q12, without the quotation marks.
3) As per point 2, but for Q13 (type in Q22 instead of "Q22")

I still cant figure it out.

I have in Q20 : C:\Users\DKJUHMET\Desktop\Test.xlsm
In Q24 : GRAF

The Q20 works perfectly to open and close with:

Please Login or Register  to view this content.

But i cant figure out what to do to make it open Sheet GRAF in the file C:\Users\DKJUHMET\Desktop\Test.xlsm

The file i currently work in is called: C:\Users\DKJUHMET\Desktop\Data.xlsm

Without the spreadsheet in question I can only guess what the issue is. Are you able to attach a sanitised version of the two spreadsheets (i.e. no confidential data)?

Can you check that the sheet name GRAF is correct? That is, the sheet name in Test.xlsm is actually GRAF without any spaces at the start or end of the sheet name.

If you're certain its correct. Try this code, it's basically a combination of the above - but just to ensure that the recording of inputs are done before the workbook Test.xlsm is opened.

Please Login or Register  to view this content.

i get quit confussed when you write Q10, Q12 and Q13, i dont have anything in those.. only in Q20 and Q22 ??

And yes it is GRAF without spaces
i really appreciate your help

I got i to work Thanks a lot for your time and help

Please Login or Register  to view this content.

Ah, you managed to figure it out before I got the time to respond. That's great.

Enjoy your new VBA tool.

I found another problem maybe yu know the answer?

In this macro, how do i use this togehter with it?
ReadOnly:=True, UpdateLinks:=True

Please Login or Register  to view this content.

To do that, you can either do...

Please Login or Register  to view this content.

OR...

Please Login or Register  to view this content.

I find the second option clearer.

Hej MetteGaga, glad to be of help.

The answer to the first problem. Yes. Again, depending on which cell you want to input the cell references.
So for example, if you type A1:K48 in cell Q1 then resulting code is...

Please Login or Register  to view this content.

As for the second question, I'm not entirely sure where in the code to be adjusted? Can you clarify?

Please Login or Register  to view this content.

if i write

Please Login or Register  to view this content.

then the macro take Q1 in the file that is open wich is the on in q20, wich will be empty.. because i have the cell value ind the file that is soposed to take data from q20..

does it make any sense?



il just show you the macro i have, (wich works perfect) i just want to be able to change as much as posible via cell value and not in the macro it self (i have some kolleages that dont know how to work with macros. But if it is in cells they know how to do..

Please Login or Register  to view this content.

I think I know what you mean. Perhaps something like this? Similar to what I have done for strSheetName and strRangeAddress.

Please Login or Register  to view this content.

OMG, it works just like i want it to thanks a lot !!

Hi again

the macro works so fine but how do i make it close without saving?

Windows(Right(str, Len(str) - InStrRev(str, "\", -1))).Close

When you say, "without saving" did you mean without Excel asking you whether to save or not? Try this.

Please Login or Register  to view this content.

yes that is what i was talking about thanks