Write data to a closed workbook

[Dave H9]

I am attempting to speed up a project I am working on, the first step has been solved here, many thanks for the help. I now want to copy data to a closed workbook.

I have found the following code which proves it is possible -

Please Login or Register  to view this content.

Rylo posted it on this site here.

I can get this code to work however I am unsure how to incorporate into my existing code (also greatfully gathered with the help of this forum).

This is an example of the code I am using -

Please Login or Register  to view this content.

So what I want to achieve is to replace the (very slow) process of copy, open, paste, save and close, as this occurs at numerous points in my complete progress.

Thanks in advance for your help

Dave H

[snb]

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Avoid copy/paste/pastespecial as much as possible

[Dave H9]

Thanks for the input snb,

I gave the code a try. For some reason my destination workbook has a problem now!!! I used the code as you have posted (No mods just replaced my original code with yours) and ran the macro. It ran through without error. Now when I open the destination workbook the sheet is not displayed although the associated VBA project does appear in the code window. I have double checked all the usual screen updating ect but to no avail.....Help!

[Dave H9]

Ok just double checked this. I used this code -

Please Login or Register  to view this content.

If the destination workbook is open it works as it should, however if it's closed it seems to stop the workbook displaying when opened?

Have I missed something?

[snb]

The only thing you missed is that you can show this 'hidden' workbook using Menubar/windows/display or show (I use a non-English version of Excel so I'm not 100% percent sure what the English option is).
It's the same as displaying the personal.xls.

[Dave H9]

Snb,

You were correct. Is there a way round this happening? The workbooks will be shared on a server and the users should not have to unhide each time they want to view the records?

Many thanks

[snb]

Please Login or Register  to view this content.

[Dave H9]

Excellent, and simple when you know how .

Hopefully my last question on this subject. I am struggling to implement the above on the following code -

Please Login or Register  to view this content.

I tried this, it works with the TRUE If function but bugs out on the ELSE with object doesnt support this property or method.

Please Login or Register  to view this content.

Your continued help with this is appreciated and hopefully this is my last hurdle.

Many thanks

Dave H

[snb]

It's no use using variables for values you only use once.
Please compare the code you had in the 'Not Applicable' case and in the opposite case....
It isn't clear to me what should be done if cell C10 doesn't contain "Not Applicable".
So I think this suffices.

Please Login or Register  to view this content.

[Dave H9]

Thanks snb,

Basically if cell J3 doesn't equal "Not Applicable" a match for the J3 value is located in the destination workbook column C. When the match is found the copied range is pasted into column K in the matching row. The full saga of how I got to this stage is HERE.

The second stage of the If function is important but I don't know how to integrate it with "GetObject" this bit inparticular -

Please Login or Register  to view this content.

Again thank you very much I wouldn't be at this stage without people like you.

Dave H

[snb]

Please Login or Register  to view this content.

[Dave H9]

Thanks snb,

Sorry to be a pest but just a small bug left I'm sure. I removed the last End If from your latest code due to "compile error". Unfortunatley it's now bugging out at

Please Login or Register  to view this content.

Error message is "Object variable or With variable not set"

I have tried using "sn" instead of ".value" with the same results.

Thank you again

Dave H

[snb]

Please Login or Register  to view this content.

Now we are looking in columns(3) : "C"

[Dave H9]

Thank you so much snb, this thread now solved.

Many thanks for your persistence in helping me with this.

Dave H