UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Transferspreedsheet Macro Problem, Office 2003    
 
   
gerrymouse1
post Jan 12 2012, 09:48 AM
Post #1

UtterAccess Enthusiast
Posts: 55

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
Go to the top of the page
 
+
Cosmichighway
post Jan 12 2012, 10:17 AM
Post #2

UtterAccess Addict
Posts: 170
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
Go to the top of the page
 
+
gerrymouse1
post Jan 12 2012, 10:43 AM
Post #3

UtterAccess Enthusiast
Posts: 55

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
Go to the top of the page
 
+
Cosmichighway
post Jan 12 2012, 10:50 AM
Post #4

UtterAccess Addict
Posts: 170
Add this to the end of the code if you don't want the excel file to stay open.

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.
Go to the top of the page
 
+
gerrymouse1
post Jan 12 2012, 10:50 AM
Post #5

UtterAccess Enthusiast
Posts: 55
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


I get faults.

Sorry for all the questions!!!!! LOL

Thanks

This post has been edited by gerrymouse1: Jan 12 2012, 10:51 AM
Go to the top of the page
 
+
Cosmichighway
post Jan 12 2012, 10:56 AM
Post #6

UtterAccess Addict
Posts: 170
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.
Go to the top of the page
 
+
Cosmichighway
post Jan 12 2012, 02:14 PM
Post #7

UtterAccess Addict
Posts: 170
Gerry, did that work for you?
Go to the top of the page
 
+
gerrymouse1
post Jan 13 2012, 03:26 AM
Post #8

UtterAccess Enthusiast
Posts: 55
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



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
Go to the top of the page
 
+
Cosmichighway
post Jan 13 2012, 10:37 AM
Post #9

UtterAccess Addict
Posts: 170
Your welcome Gerry. Glad I could help.
Go to the top of the page
 
+
« Next Oldest · Access Macros · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 02:35 AM