Another Day Another Problem!! hope some one can help me with this.I have a Transferspreedsheet Marco which transfers the contents of a coupl of different queries into a Microsoft Excel 2010 spreedsheet. I am doing this so I can manipulate the data into various charts for display purposes. Charts in excel are very easy to manipulate.I can successfully get the Macro running and updating into a spreedsheet. I then have a coupd of diffent Excel documents linked to this excel spreedsheet. The problem is that when the Macro updates the spreedsheet it does not seem to save the data. When I open the spreedsheet the data is updated. When I attempt to exit the spreedsheet it asked me do I want to save. If I save the spreedsheet the externally linked spreedsheets update their data. Howver, If I dont save the external data links are not updated.Any thoughts??This worked for me before on Excel 2003. Company forced an upgrade to Office 2010 and kept Access 2003 until a later date.Any help appreciated.Regards,Gerry

Cosmichighway

Jan 12 2012, 10:17 AM

Hi Gerry,One thing you can do is at the end of your macro that exports the spreadsheet to excel is run some vbcode. I just did this the other day and it has been working well for me. You can open up the spreadsheet from within access and then save the file after the export occurs. Try something like this:

I have other code that uses the worksheet so you may not need to even include that if all you want to do is open the excel file and save it after export.Hope this helpsDan

gerrymouse1

Jan 12 2012, 10:43 AM

Dan,hankyou for your quick response. Is there anyway I can do this without opening the spreedsheet up?Going to give this a go now. Not to familiar where all this goes but going to dig through it. I think I create a module and put this in as a function????Again thank you for yor help!!!Gerry

Cosmichighway

Jan 12 2012, 10:50 AM

Add this to the end of the code if you don't want the excel file to stay open.!--c1-->

CODE

xlWBk.Close

You have to open the workbook before you can save it but this will also close it directly after saving.To add the code open up the VB Editor and create a function. Then at the end of your macro add an action as RunCode with the argument as the name of the function you created.

gerrymouse1

Jan 12 2012, 10:50 AM

Also,The excel document is contained on a different shared drive.S:\????\019 - ???\Focused Improvement\Charts\Focused Improvement Chart Data 2.xlsWhen Iinsert this into

You need to enclose the name of the file in quotation marks when you try to open the workbook. You also need to change Set xlWsh = xlWBk.Worksheets(nameOfWorksheet) to contain the actual name of the worksheet - again enclosed in quotes.

Cosmichighway

Jan 12 2012, 02:14 PM

Gerry, did that work for you?

gerrymouse1

Jan 13 2012, 03:26 AM

Dan,nly back at the sheet this morning. Ientereed the following code as my function:

Ochanged the visibiity to "False" so that the user will not see the excel spreedsheet opening. Other than that it worked perfectly. Thank you for you help.This site is an absolute cracker for anyone learning access. This help is excellent.Cheers,Gerry

Cosmichighway

Jan 13 2012, 10:37 AM

Your welcome Gerry. Glad I could help.

This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.