Connecting to External Data

[Nikolay]

Thank you, I thought about this but it wasn't the only reason. Your code worked. It was something with the too many Extended Properties (IMEX=1;MaxScanRows=0 ?!? ...).

You can google on these extra parameters to see if you really need them.
This can depend on the data you have in your excel sheet.

With the single parenthesis around the Sheet1$ it worked

Code Select Expand

commandString = "select * from `Sheet1$`" but with normal single ones

Code Select Expand

commandString2 = "select * from 'Sheet1$'"
it didnt ?!?

As far as I know, the correct ones are the `back-quotes` (or `back-ticks`, or how do you call those ). These are needed, because the "table name" (identifier Sheet1$) contains a special symbol ($). Visual Basic macro recorder seems to generate these back-quotes correctly, so probably the copy operation just erroneously changed those into "regular" quotes. I believe you could also use square brackets - [Sheet1$] instead of `Sheet1$`, - but 'Sheet1$' is illegal, because it is not an sql identifier, but a text literal.

Kind regards, Nikolay.

[VisioBut]

Can anybody tell what is wrong here?

A dropped shape is linked, and through the linking additional shape data (new properties) is defined.

Code Select Expand

m1 = visio.Documents.OpenEx(stencil,flags).Masters("ShapeName")
shape = page.Drop( m1, 0, 0 )
shape.LinkToData(Recordset.ID,rowNr[0])
if shape.CellExists("Prop.NewKind",0)

After the linking the new properties are not found.(at least checking with the if statement above doesnt find them) When I right-click on the shape, the properties are there though ... ?!?

[aledlund]

A couple of questions on the shape you are linking.
Is the property you are testing for predefined in the shape?
Does python accept an inferred response from an if statement (if shape.cellexists("prop.newkind",false) = true)?
al

[VisioBut]

Is the property you are testing for predefined in the shape?

No, its additional, i.e. it is not predefined.

Does python accept an inferred response from an if statement (if shape.cellexists("prop.newkind",false) = true)?

I dont think I understand your question. But if a cell property exists it will evaluate the statement as true.

[aledlund]

I've seen case where
if shape.cellexists("prop.newkind",false) then...
did not work, where
if shape.cellexists("prop.newkind",false) = true then...
does work. The first case infers that the response to the test is enough while the second case does not.
al

[mst700]

I have worked out a way of confirming the code is correct by using the macro record method.  This may or may not be a correct way to confirm these processes and would like some advise:

    Sub linkExcel()

'Enable diagram services
    Dim DiagramServices As Integer
    DiagramServices = ActiveDocument.DiagramServicesEnabled
    ActiveDocument.DiagramServicesEnabled = visServiceVersion140 + visServiceVersion150

    Application.ActiveDocument.DataRecordsets.Add "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Octal-Core\Desktop\NetpbcNodeOamIpAddresses.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False", "select * from `NetpbcNodeOamIpAddresses$`", 0, "NetpbcNodeOamIpAddresses"

    Dim vsoPrimaryKeys1(1 To 2) As String
    vsoPrimaryKeys1(1) = "#dev-name-primary"
    vsoPrimaryKeys1(2) = "#ip-address"
    Application.ActiveDocument.DataRecordsets.ItemFromID(2).SetPrimaryKey VisPrimaryKeySettings.visKeyComposite, vsoPrimaryKeys1
   
Application.ActiveWindow.Windows.ItemFromID(visWinIDExternalData).Visible = True

End Sub



I would like some help loading these 2 columns in a index type search function, maybe an array, not sure.  My data excel sheet is over 10,000 records long.  The data looks like the following:

vnnycag01   10.72.9.72
vnnycag02   10.72.9.73
vnnycag03   10.72.9.74
vnnycag04   10.72.9.75
vnnycag05   10.72.9.76
vnnycag06   10.72.9.77
vnnycag07   10.72.9.78
vnnycag08   10.72.9.79
vnnycag09   10.72.9.84
vnnycag10   10.72.9.85
vnnycag11   10.72.9.86
vnnycag12   10.72.9.87


I am trying to match the first column (Hostname) and copy the second column data in a specific text box with these names:

strSDG01
strSDG02
strASR01
strASR02
strIPS01
strIPS02
strANU01
strANU02
strANU03

once I have matched the host names and moved the IP's over to the box, the rest of my program works great.


Can anyone help?