Full Version: Transferspreedsheet Macro Problem
Another Day Another Problem!!
I 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
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 helps
Dan
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
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
Dan,
Thankyou 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
Add this to the end of the code if you don't want the excel file to stay open.
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.
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.
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
I get faults.
Sorry for all the questions!!!!! LOL
Thanks
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
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
I get faults.
Sorry for all the questions!!!!! LOL
Thanks
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.
Gerry, did that work for you?
QUOTE (Cosmichighway @ Jan 12 2012, 07:14 PM) 
Gerry, did that work for you?
Dan,
Only 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
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
I changed 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
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.