Viso diagram creation from excel data sheet

[stonyny]

Hi,

I am working on a task to gather data from excel datasheet and create visio diagram, may be create a button that will do the drawing the wne the button is clicked.

I tried the following but having some trouble and seeking some help.

1) I tried to create connection but not sure if it is required.

strOfficePath = Visio.Application.Path
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "User ID=Admin;" _
                       & "Data Source=" + strOfficePath + "ExcelFile.XLS;" _
                       & "Mode=Read;" _
                       & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
                       & "Jet OLEDB:Engine Type=34;"

    strCommand = "SELECT * FROM [Sheet1$]"

  ' Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Test Data") -- I get compiling error here

2) I think I need a loop to go through the each excel column and store the values in an array which I can later use in visio diagrams

Can I use the following code?

Sub ExcelOpen()

Dim objExcel As Visio.Object
Dim objWorkBook As Object
Dim Rng As Range
Dim c As Range

Set objExcel = CreateObject("EXCEL.APPLICATION")
objExcel.Visible = True
Set objWorkBook = objExcel.Workbooks.Open("ExcelFile.xls") -- As my connection was not successful and the program crashes I was                                                                                                  not able to access the excel file from my local deskstop
Set objWorkSheet = objWorkBook.WorkSheets("Sheet1")

With objWorkBook.Sheets("Sheet1")
    Set Rng = .Range("A6:B6")
   
    For Each c In Rng
        'code to populate visio   -- I am not sure about this section and need help most in this part
Next c

End With

End Sub

3) I think I also have to use Visio.applications, Visio.documents, Visio,pages etc. to create visio instances and before I do step 2 and step1

Thanks in advance and would appreciate if any of you advise.

[Jumpy]

You have to reference the Excel-Object-Library, when you use early bbinding.
Therefore in the VBA-Editor go to Options->Referencs and check the Excel-Library.
Sth. Like "Microsoft Excel 12.0 Object Library" (12.0 may be different due to your excel version).

Then you can do sth. like this:

Code Select Expand


Dim ea as Excel.Application
Dim wb as Excel.Workbook 'From here on the Excel. is not strictly neccessary, just Workbook will work, too.
Dim ws as Excel.Worksheet
Dim rng as Excel.Range

Set ea = New Excel.Application
Set wb = ea.Workbooks.Open "myfile"
Set ws = wb.Worksheets(1)

'and so ao


[stonyny]

Thanks for mentioning about adding Excel Library.  I have checked the selection of the libraries and so far I have following ones:

1) Visual Basic for Applications
2) Microsoft Visio 12.0 Type Library
3) OLE Automation
4) Microsoft Office 12.0 Object Library
5) Microsoft Excel 14.0 Object Library
6) Microsoft Visio 12.0 Diagram Launch Control
7) Microsoft Visio 12.0 Drawing Control Type Library

I was not able to locate Excel 12.0 Object Library from the Tools>Reference

FYI...I am using Visio 2007 Pro but Excel version is 2010.  I was able to locate Office 12 and Office 14 folders under Microsoft Office folders under Program Files.

I tried to search this forum if there are similar issues experienced by others or any other old threads but I didn't have luck yet.  I found following one:

1) http://msdn.microsoft.com/en-us/library/aa701255(v=office.12).aspx#Y4397 - but this is to get information from visio to excel.  Can I have the similar code other way around?  Like Excel to Visio Shapes?

[Jumpy]

Microsoft Excel 14.0 Object Library is the one you need, as you have Excel 2010 (= Excel 14).
(Because I have Excel 2007, my version number is 12).

As for the way to go. You can use Visio to open Excel (in sth. like remote control) fetch the data and apply it to the shapes. That's what you tried so far.
(You can do the opposite, too (Code in Excel, to control Visio per VBA from there), but why?)

What you should do. In Visio:
- Check the Excel Library
- Code like the one below:

Code Select Expand


Sub GetMyData()
  Dim ea as Excel.Application
  Dim wb as Excel.Workbook 'From here on the Excel. is not strictly neccessary, just Workbook will work, too.
  Dim ws as Excel.Worksheet
  Dim c as Collection
  Dim i as Integer

  Set c = New Collection
  Set ea = New Excel.Application
  Set wb = ea.Workbooks.Open "C:\Test\MyExcelFile.xls"
  Set ws = wb.Worksheets("MyWorksheetName")

  'Now get the Data from Excel and save it in collection
  'In this example I fetch the values in Column "A", Rows 1..100
  For i = 1 To ws.UsedRange.Rows.Count
    c.Add  ws.Cells(i,1).Value
  Next i

  'Now do sth. with that Data in Visio:
 
 
End Sub