Full Version: Transferspreedsheet Macro Problem
UtterAccess Discussion Forums > Microsoft® Access > Access Macros
gerrymouse1
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
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:
CODE
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWsh As Object
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(nameOfWorkbook)
ApXL.Visible = True
Set xlWsh = xlWBk.Worksheets(nameOfWorksheet)
xlWBk.Save

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 helps
Dan
gerrymouse1
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
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
Also,
The excel document is contained on a different shared drive.
S:\????\019 - ???\Focused Improvement\Charts\Focused Improvement Chart Data 2.xls
When Iinsert this into
CODE
Function SaveSpreedsheet()
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWsh As Object
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(S:\Kevlar\019 - DPS\Focused Improvement\Charts\Focused Improvement Chart Data 2.xls)
ApXL.Visible = False
Set xlWsh = xlWBk.Worksheets(nameOfWorksheet)
xlWBk.Save
End Function

Oget faults.
Sorry for all the questions!!!!! LOL
Thanks
Cosmichighway
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
Gerry, did that work for you?
gerrymouse1
Dan,
nly back at the sheet this morning. Ientereed the following code as my function:
CODE
Function SaveSpreedsheet()
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWsh As Object
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open("S:\?????\019 - ???\Focused Improvement\Charts\Focused Improvement Chart Data 2.xls")
ApXL.Visible = False
Set xlWsh = xlWBk.Worksheets("Chart_Data")
xlWBk.Save
xlWBk.Close
End 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
Your welcome Gerry. Glad I could help.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.