How to write "Export" routine to a excel file from Visio using VBA in Access

Hi! I'm trying using VBA in Access to :
1) export employee table to excel and open Visio (done!!!);
after the updating  organization structure  chart in Visio (as usual!);
3) in Access save the organization chart uptdated in Visio and  export the organization chart to excel file
4) import the excel file in access table (done!!!).

Someone has some experience about point 3)? (i'm able in Visio VBA to save some fields of the
organization  chart in a excel file but i need to have the list of the main fields name too!)
thanks a lot
 
giorgiorossanigoAsked:
Who is Participating?
 
giorgiorossanigoConnect With a Mentor Author Commented:
I solved the problem!!!  definitely! I can design and redesign organization chart using Access database and update the Access database based on updated organization chart automatically
If someone wants to know how, please contact me!
0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
You may have already found this article: http://office.microsoft.com/en-us/visio/HA010774641033.aspx
It doesn't help with exporting the org chart but may provide some assistance. If you want to see VBA code that uses the info from that article, see this EE article.

I haven't found a comparable way to export the Visio org chart from VBA. You could use 'SendKeys' -- it's not elegant but it would work. The key sequence is
  Alt+C
  X
  <filename>
  Enter

Alt+C opens the Org Chart menu; X is for Export, which opens a Save As dialog; then provide the filename and press enter.

Important note: if you have Visio 2010, use Alt+O instead of Alt+C
0
 
giorgiorossanigoAuthor Commented:
Thanks a  lot Scoot!!! now let me study your proposal.......
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
giorgiorossanigoAuthor Commented:
Scott, your solution could be powerfull...but how i can tell to the code (in my VBA code in Access) that i want to use these keys in Visio application?
0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
I was referring to the VBA SendKeys method. This should help:
   http://msdn.microsoft.com/en-us/library/aa202943%28office.10%29.aspx

Be sure to look a the bottom of the article to see how to include the Alt key.

Let me know if you need further assistance.
0
 
giorgiorossanigoAuthor Commented:
Hi Scott!
no problem in order to write the right code for Alt key...but...the SenKey "Sends keystrokes to the active application"...but i'm in Acess VBA routine...so i think that actual application is Access e not Visio! how i can tell to my VBA cose that i want to use the Keys in Visio environmnet?
Thanks!!!
0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
I presume you've already created an object to refer to the Visio application? You must have done that because you said you open Visio from Access in step 1. Use SendKeys on that object, e.g., if your Visio app is vsoApp, then
   vsoApp.SendKeys("%fC")
0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
I'm sorry -- I may have misled you. It doesn't look like the Visio application object takes the SendKeys method. Stay tuned...
0
 
giorgiorossanigoAuthor Commented:
Scott......help me!
Sorry...but my code isn't working!! ....."no property supported........"
This is my code in Access:  the error occurs in " objVisio.SendKeys ("%4") " line

Private Sub Comando14_Click()
DoCmd.SetWarnings False
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    'Kill the prevoius file if it exist
    If Dir("C:\Users\Giorgio rossanigo\Documents\SocietàVisio.xlsx") <> "" Then
        Kill ("C:\Users\Giorgio rossanigo\Documents\SocietàVisio.xlsx")
    End If

DoCmd.OpenQuery "SocietàperVisio"          'run the query that create data for excel
DoCmd.TransferSpreadsheet acExport, , "SocietàVisio", "C:\Users\Giorgio rossanigo\Documents\SocietàVisio.xlsx", True       ' create the file excel for visio


Dim objVisio As Object

Dim strCommandPart As String

'START THE ORG CHART WIZ
Set objVisio = CreateObject("Visio.Application")
Set objAddOn = objVisio.Addons.ItemU("OrgCWiz")
objAddOn.Run ("/S-INIT")
strCommandPart = "/FILENAME = C:\Users\Giorgio rossanigo\Documents\SocietàVisio.xlsx / ID_Univoco - Field = ID_Univoco / Nome - Field = Nome /Subordinato_a - Field = Subordinato_a / Shape - Field = Master_Shape / Update - Shapes - OnOpen"
objAddOn.Run ("/S-ARGSTR " + strCommandPart)

objAddOn.Run ("/S-RUN")
objVisio.Application.ActiveWindow.ShowGrid = False


Msg = "confermi le modifiche effettuate nel disegno della struttura organizzativa?"    ' Definisce il messaggio. "do you confirm the updats?"
                                                                    Style = vbYesNo + vbQuestion + vbDefaultButton2
                                                                    Title = "Conferma"
                                                                    Ctxt = 1000
                                                                    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
                                                                     
                                                                                     If Response = vbNo Then                  'Close Visio
                                                                                       
                                                                                        objVisio.ActiveDocument.Saved = True
                                                                                        objVisio.Quit
                                                                                       
                                                                                     Else
                                                                                        '-----------------------------------------------------------
                                                                                        'DoCmd.Hourglass
                                                                                           
                                                                                            objVisio.SendKeys ("%4") ' because i have my custom menu!!!
                                                                                       
                                                                                            objVisio.SendKeys ("~RitornoSocietàVisio.xlsx")
                                                                                        '------------------------------------------------------------
                                                                                        objVisio.ActiveDocument.Saved = False
                                                                                        objVisio.Quit
                                                                                     End If






End Sub

0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
See my previous posting. I thought Visio supported sendkeys but apparently not. I must remember using with some other app. I won't be able to get back to this until sometime tomorrow. I'll see what else I can come up with that will work.
0
 
giorgiorossanigoAuthor Commented:
Scott, thanks a lot for your help!!!!!!!!!!
0
 
giorgiorossanigoAuthor Commented:
It's working!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.