Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-18
12
Medium Priority
?
1,332 Views
Last Modified: 2013-11-27
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
 
0
Comment
Question by:giorgiorossanigo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 34933034
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
 

Author Comment

by:giorgiorossanigo
ID: 34934154
Thanks a  lot Scoot!!! now let me study your proposal.......
0
 

Author Comment

by:giorgiorossanigo
ID: 34934324
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 31

Expert Comment

by:Scott Helmers
ID: 34934394
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
 

Author Comment

by:giorgiorossanigo
ID: 34934430
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
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 34934457
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
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 34934488
I'm sorry -- I may have misled you. It doesn't look like the Visio application object takes the SendKeys method. Stay tuned...
0
 

Author Comment

by:giorgiorossanigo
ID: 34934523
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
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 34934573
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
 

Author Comment

by:giorgiorossanigo
ID: 34934651
Scott, thanks a lot for your help!!!!!!!!!!
0
 

Accepted Solution

by:
giorgiorossanigo earned 0 total points
ID: 34958595
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
 

Author Closing Comment

by:giorgiorossanigo
ID: 34990915
It's working!!!!
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question