topic Re: Trying to Reference Multiple Excel sheets into idw's in Inventor Programming Forum

Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Sun, 31 Oct 2021 15:24:41 GMT

Hello.

I am able to simply reference an excel table into my idw's with no problem. HOWEVER....inventor seems to read the first sheet of the excel spreadsheet only, unless ofcourse I am wrong.

I have multiple idw's that must read from the same excel spreadsheet which has multiple sheets eg .idw 1 should read from Sheet 1 of the Superheater Template.

.idw 2 should read from Sheet2 of the Superheater Template spreadsheet etc.

Is there a rule that permits this?

I know that I can reference different cells in the first sheet of the spreadsheet but for this exercise it will prove to very confusing as each sheet is quite involving as it is.

Currently I have no rules, just making reference to an excel table, with limited functionality it seems.

Please help.

Thank you

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Mon, 01 Nov 2021 07:58:30 GMT

Even the API don't allow you to specify the sheet when you use "AddExcelTable".

But you can create a custom table based on the data in excel. So you first need to get the data from excel and then use them to fill your table. But this is not a table then that is linked. you could made a code checking the content in excel if needed. Here is a little sample rule to create a table that gets data based on the sheet number from excel. I included the excel as well (save this in the root of your workspace and the code should work directly).

Sub main
	
Dim oDrawDoc As DrawingDocument
 oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet

'ophalen template path definitie
oPathWP = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath

oExcel = oPathWP & "\Sample.xlsx"
i=1
For Each oSheet In oDrawDoc.Sheets
	
	CreateTable(oExcel, oSheet, i)
	i=i+1
Next
	
End Sub
Sub CreateTable(ByVal oExcel As String, oSheet As Sheet,i As Integer)

	Dim oSheetName As String
	oSheetName = "Sheet" & i

    Dim oTitles(1) As String
    oTitles(0) = GoExcel.CellValue(oExcel, oSheetName, "A1")
    oTitles(1) = GoExcel.CellValue(oExcel, oSheetName, "B1")
		
	Dim oContents(7) As String
	oContents(0) = GoExcel.CellValue(oExcel, oSheetName, "A2")
	oContents(1) =GoExcel.CellValue(oExcel, oSheetName, "B2")
	oContents(2) = GoExcel.CellValue(oExcel, oSheetName, "A3")
	oContents(3) = GoExcel.CellValue(oExcel, oSheetName, "B3")
	oContents(4) = GoExcel.CellValue(oExcel, oSheetName, "A4")
	oContents(5) = GoExcel.CellValue(oExcel, oSheetName, "B4")
	oContents(6) = GoExcel.CellValue(oExcel, oSheetName, "A5")
	oContents(7) =GoExcel.CellValue(oExcel, oSheetName, "B5")
	
	Dim InsP As Point2d
    InsP = ThisApplication.TransientGeometry.CreatePoint2d(15, 15)

    Dim oCustomTable As CustomTable
    oCustomTable = oSheet.CustomTables.Add("Machines", InsP, 2, 4, oTitles,oContents)


End Sub

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Mon, 01 Nov 2021 14:40:04 GMT

Hello theo.Bolt

Thank you so much for starting me off, I am definitely a bit further than I was yesterday.

Using your rule, I have referenced all the columns I require and I have included just a few rows, there are more however I just wanted to test the rule first.

Upon applying the rule I seem to come across this error.

Please see the manner in which I tweaked the rule below.

Sub main
	
Dim oDrawDoc As DrawingDocument
 oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet

'ophalen template path definition
oPathWP = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath

oExcel = "X:\SUPERHEATER_Template.xlsx"
i= 4
For Each oSheet In oDrawDoc.Sheets
	
	CreateTable(oExcel, oSheet, i)
	i=i+1
Next
	
End Sub
Sub CreateTable(ByVal oExcel As String, oSheet As Sheet,i As Integer)

	Dim oSheetName As String
	oSheetName = "ELEMENT" & i

    Dim oTitles(1) As String
    oTitles(0) = GoExcel.CellValue(oExcel, oSheetName, "F144")
    oTitles(1) = GoExcel.CellValue(oExcel, oSheetName, "A145")
		
	Dim oContents(7) As String
	oContents(0) = GoExcel.CellValue(oExcel, oSheetName, "A147")
	oContents(1) =GoExcel.CellValue(oExcel, oSheetName, "B147")
	oContents(2) = GoExcel.CellValue(oExcel, oSheetName, "C147")
	oContents(3) = GoExcel.CellValue(oExcel, oSheetName, "D147")
	oContents(4) = GoExcel.CellValue(oExcel, oSheetName, "E147")
	oContents(5) = GoExcel.CellValue(oExcel, oSheetName, "F147")
	oContents(6) = GoExcel.CellValue(oExcel, oSheetName, "G147")
	oContents(7) = GoExcel.CellValue(oExcel, oSheetName, "H147")
	oContents(8) = GoExcel.CellValue(oExcel, oSheetName, "I147")
	oContents(9) = GoExcel.CellValue(oExcel, oSheetName, "J147")
	oContents(10) = GoExcel.CellValue(oExcel, oSheetName, "A148")
	oContents(11) = GoExcel.CellValue(oExcel, oSheetName, "A149")
	oContents(12) = GoExcel.CellValue(oExcel, oSheetName, "A150")
	oContents(13) = GoExcel.CellValue(oExcel, oSheetName, "A151")
	oContents(14) = GoExcel.CellValue(oExcel, oSheetName, "A152")
	oContents(15) =GoExcel.CellValue(oExcel, oSheetName, "A153")
	
	Dim InsP As Point2d
    InsP = ThisApplication.TransientGeometry.CreatePoint2d(15, 15)

    Dim oCustomTable As CustomTable
    oCustomTable = oSheet.CustomTables.Add("CHURCH WINDOWS EXISTING WELDS", InsP, 10, 6, oTitles,oContents)


End Sub

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Mon, 01 Nov 2021 15:11:02 GMT

I think you need to change the following:

oContents(7) Into oContents(15)

And

oSheet.CustomTables.Add("CHURCH WINDOWS EXISTING WELDS", InsP, 10, 6, oTitles,oContents)

Into

oSheet.CustomTables.Add("CHURCH WINDOWS EXISTING WELDS", InsP, 2, 8, oTitles,oContents)

If the specified number of rows does not match with the content, the addtable function will fail.

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Mon, 01 Nov 2021 17:02:46 GMT

Hello @theo.bot

Thank you for the response,

I know that when this message pops up, it is an indication that something with the reference somewhere is so incorrect.

Where would I have gone wrong this time?

What does the (15, 15) in this line mean "Insp=ThisApllication.TransientGeometry.CreatePoint2d(15, 15)

Sub main
	
Dim oDrawDoc As DrawingDocument
 oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet

'ophalen template path definition
oPathWP = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath

oExcel = "X:\SUPERHEATER_Template.xlsx"
i= 4
For Each oSheet In oDrawDoc.Sheets
	
	CreateTable(oExcel, oSheet, i)
	i=i+1
Next
	
End Sub
Sub CreateTable(ByVal oExcel As String, oSheet As Sheet,i As Integer)

	Dim oSheetName As String
	oSheetName = "ELEMENT" & i

    Dim oTitles(1) As String
    oTitles(0) = GoExcel.CellValue(oExcel, oSheetName, "A147")
    oTitles(1) = GoExcel.CellValue(oExcel, oSheetName, "B147")
		
	Dim oContents(11) As String
	oContents(0) = GoExcel.CellValue(oExcel, oSheetName, "A148")
	oContents(1) =GoExcel.CellValue(oExcel, oSheetName, "B148")
	oContents(2) = GoExcel.CellValue(oExcel, oSheetName, "A149")
	oContents(3) = GoExcel.CellValue(oExcel, oSheetName, "B149")
	oContents(4) = GoExcel.CellValue(oExcel, oSheetName, "A150")
	oContents(5) = GoExcel.CellValue(oExcel, oSheetName, "B150")
	oContents(6) = GoExcel.CellValue(oExcel, oSheetName, "A151")
	oContents(7) = GoExcel.CellValue(oExcel, oSheetName, "B151")
	oContents(8) = GoExcel.CellValue(oExcel, oSheetName, "A152")
	oContents(9) = GoExcel.CellValue(oExcel, oSheetName, "B152")
	oContents(10) = GoExcel.CellValue(oExcel, oSheetName, "A153")
    oContents(11) = GoExcel.CellValue(oExcel, oSheetName, "B153")
	
	'Dim InsP As Point2d
    InsP = ThisApplication.TransientGeometry.CreatePoint2d(15, 15)

    Dim oCustomTable As CustomTable
    oCustomTable = oSheet.CustomTables.Add("CHURCH WINDOWS EXISTING WELDS",InsP,2,6,oTitles,oContents)


End Sub

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Tue, 02 Nov 2021 06:28:38 GMT

You changed the value of "i" into 4. Is that correct it's ment as sheet number. So in your excel file your sheet are named "Element4" "element5" etc. If you needed to start with "Element1" then you should keep i=1

What does the (15, 15) in this line mean "Insp=ThisApllication.TransientGeometry.CreatePoint2d(15, 15)

The line above creates a point on your sheet as your insert point for your table. The values are X and Y coordinates in application default units (cm).

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Tue, 02 Nov 2021 06:52:19 GMT

Hello Theo.bolt

Yes I deliberately changed the "i=1"into 4 because I would like to reference the 4th Sheet which is called Element 4.

I have 28 Elements (sheets) in total so I need the idw's to make reference to each Element.

Each Element has differing information within the tables

Please don't tell me it only works for the 1st Sheet because I need this rule to be flexible enough to refer to the different Element numbers in the same spreadsheet.

Just a matter of interest is there another manner of referring to the excel path without using this "oPathWP" option

oPathWP = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath

oExcel = oPathWP & "\SUPERHEATER_Template.xlsx"

Thank you

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Tue, 02 Nov 2021 08:17:09 GMT

If you are aware why you changed it, it's fine ;-).

I use the Workspace path a lot to avoid hardcoded file paths, but ofcourse you can use it without. Just set oExcel to a full path string like this:

oExcel = "D:\01 Data\Workingfolder Vault\Designs\sample.xlsx"

oExcel = oPathWP & "\SUPERHEATER_Template.xlsx"

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Tue, 02 Nov 2021 10:35:28 GMT

Hello Theo.bolt

Thank you for your help so far, however this "simple code" is surely testing my wits.

when I remove oPathWP and just refer to the string as you mentioned...this terrible error pops up.

When I include the oPathWP option as initially provided by you, then this error pops up.

Any ideas as to why these varying errors occur? I mean, simply linking the excel path is not hard to do yet

it returns with this " Exception from HRESULT.....FAIL" error.

Thank You

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Tue, 02 Nov 2021 10:38:01 GMT

Can you share your drawing and your excel file? It's easier to troubleshoot 😉

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Tue, 02 Nov 2021 11:39:56 GMT

Thanks theo.bolt

I have attached the spreadsheet in question and the idw. (which is just one of many)

May I request we opt for the excel link without a work path as this works out better in the long run.

Thank you once again.

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Tue, 02 Nov 2021 13:43:13 GMT

I see that the cells you are referencing are sometimes empty. this results in an error. you can do add checks like this to create a blank cell for your table.

You need to do this for each oContents definition.

	 If GoExcel.CellValue(oExcel, oSheetName, "D65") Is Nothing Then
	oContents(8) = ""
	Else
		oContents(8) = GoExcel.CellValue(oExcel, oSheetName, "D65")
	End If

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Tue, 02 Nov 2021 13:28:03 GMT

Hello @theo.bot

Thank you for your patience and perseverance in assisting me with this matter.

It took me a while but EVENTUALLY I understood where I went wrong, it all came together 😁

Thanks once again👍

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Wed, 03 Nov 2021 11:00:00 GMT

Hello @theo.bot

Sorry, I have returned, I have done some troubleshooting before sending this message.

Unless I am missing something, but I am unable to expand my columns to the desired numbers.

Currently it limits me to five columns only regardless of the information I add.

using the same spreadsheet and .idw I have sent on this forum, would you be able to assist in finding out

what I am missing? I have inserted the code below the picture.

Thank you

Sub main
	
Dim oDrawDoc As DrawingDocument
 oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet

'ophalen template path definitie

oExcel = "D:\SUPERHEATERS\SUPERHEATER_Template1.xlsx"
i=1
For Each oSheet In oDrawDoc.Sheets
	
	CreateTable(oExcel, oSheet, i)
	i=i+1
Next
	
End Sub
Sub CreateTable(ByVal oExcel As String, oSheet As Sheet,i As Integer)

	Dim oSheetName As String
	oSheetName = "ELEMENT" & i

    Dim oTitles(5) As String
    oTitles(0) = GoExcel.CellValue(oExcel, oSheetName, "A2")
    oTitles(1) = GoExcel.CellValue(oExcel, oSheetName, "B2")
    oTitles(2) = GoExcel.CellValue(oExcel, oSheetName, "C2")
    oTitles(3) = GoExcel.CellValue(oExcel, oSheetName, "D2")
    oTitles(4) = GoExcel.CellValue(oExcel, oSheetName, "E2")
	oTitles(5) = GoExcel.CellValue(oExcel, oSheetName, "F2")
	'oTitles(6) = GoExcel.CellValue(oExcel, oSheetName, "G2")
	'oTitles(7) = GoExcel.CellValue(oExcel, oSheetName, "H2")
	'oTitles(8) = GoExcel.CellValue(oExcel, oSheetName, "I2")
	'oTitles(9) = GoExcel.CellValue(oExcel, oSheetName, "J2")
	
	
	Dim oContents(47) As String
	oContents(0) = GoExcel.CellValue(oExcel, oSheetName, "A3")
	oContents(1) = GoExcel.CellValue(oExcel, oSheetName, "B3")
	oContents(2) = GoExcel.CellValue(oExcel, oSheetName, "C3")
	oContents(3) = GoExcel.CellValue(oExcel, oSheetName, "D3")
	oContents(4) = GoExcel.CellValue(oExcel, oSheetName, "E3")
	oContents(5) = GoExcel.CellValue(oExcel, oSheetName, "F3")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "G3")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "H3")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "I3")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "J3")
	oContents(6) = GoExcel.CellValue(oExcel, oSheetName, "A4")
	oContents(7) = GoExcel.CellValue(oExcel, oSheetName, "B4")
	oContents(8) = GoExcel.CellValue(oExcel, oSheetName, "C4")
	oContents(9) = GoExcel.CellValue(oExcel, oSheetName, "D4")
	oContents(10) = GoExcel.CellValue(oExcel, oSheetName, "E4")
	oContents(11) = GoExcel.CellValue(oExcel, oSheetName, "F4")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "G4")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "H4")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "I4")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "J4")
	oContents(12) = GoExcel.CellValue(oExcel, oSheetName, "A5")
	oContents(13) = GoExcel.CellValue(oExcel, oSheetName, "B5")
	oContents(14) = GoExcel.CellValue(oExcel, oSheetName, "C5")
	oContents(15) = GoExcel.CellValue(oExcel, oSheetName, "D5")
	oContents(16) = GoExcel.CellValue(oExcel, oSheetName, "E5")
	oContents(17) = GoExcel.CellValue(oExcel, oSheetName, "F5")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "G5")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "H5")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "I5")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "J5")
	oContents(18) = GoExcel.CellValue(oExcel, oSheetName, "A6")
	oContents(19) = GoExcel.CellValue(oExcel, oSheetName, "B6")
	oContents(20) = GoExcel.CellValue(oExcel, oSheetName, "C6")
	oContents(21) = GoExcel.CellValue(oExcel, oSheetName, "D6")
	oContents(22) = GoExcel.CellValue(oExcel, oSheetName, "E6")
	oContents(23) = GoExcel.CellValue(oExcel, oSheetName, "F6")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "G6")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "H6")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "I6")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "J6")
	oContents(24) = GoExcel.CellValue(oExcel, oSheetName, "A7")
	oContents(25) = GoExcel.CellValue(oExcel, oSheetName, "B7")
	oContents(26) = GoExcel.CellValue(oExcel, oSheetName, "C7")
	oContents(27) = GoExcel.CellValue(oExcel, oSheetName, "D7")
	oContents(28) = GoExcel.CellValue(oExcel, oSheetName, "E7")
	oContents(29) = GoExcel.CellValue(oExcel, oSheetName, "F7")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "G7")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "H7")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "I7")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "J7")
	oContents(30) = GoExcel.CellValue(oExcel, oSheetName, "A8")
	oContents(31) = GoExcel.CellValue(oExcel, oSheetName, "B8")
	oContents(32) = GoExcel.CellValue(oExcel, oSheetName, "C8")
	oContents(33) = GoExcel.CellValue(oExcel, oSheetName, "D8")
	oContents(34) = GoExcel.CellValue(oExcel, oSheetName, "E8")
	oContents(35) = GoExcel.CellValue(oExcel, oSheetName, "F8")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "G8")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "H8")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "I8")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "J8")
	oContents(36) = GoExcel.CellValue(oExcel, oSheetName, "A9")
	oContents(37) = GoExcel.CellValue(oExcel, oSheetName, "B9")
	oContents(38) = GoExcel.CellValue(oExcel, oSheetName, "C9")
	oContents(39) = GoExcel.CellValue(oExcel, oSheetName, "D9")
	oContents(40) = GoExcel.CellValue(oExcel, oSheetName, "E9")
	oContents(41) = GoExcel.CellValue(oExcel, oSheetName, "F9")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "G9")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "H9")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "I9")
	'oContents() = GoExcel.CellValue(oExcel, oSheetName, "J9")
	oContents(42) = GoExcel.CellValue(oExcel, oSheetName, "A10")
	oContents(43) = GoExcel.CellValue(oExcel, oSheetName, "B10")
	oContents(44) = GoExcel.CellValue(oExcel, oSheetName, "C10")
	oContents(45) = GoExcel.CellValue(oExcel, oSheetName, "D10")
	oContents(46) = GoExcel.CellValue(oExcel, oSheetName, "E10")
	oContents(47) = GoExcel.CellValue(oExcel, oSheetName, "F10")
	
	Dim InsP As Point2d
    InsP = ThisApplication.TransientGeometry.CreatePoint2d(15, 15)

    Dim oCustomTable As CustomTable
    oCustomTable = oSheet.CustomTables.Add("CHURCH WINDOWS EXISTING WELDS", InsP, 6, 8, oTitles,oContents)


End Sub

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Wed, 03 Nov 2021 12:45:30 GMT

@emilda.chiuye

I updated the code a little bit to make it more flexible.

When you call the sub "CreateTable" you now need to provide the startcell for your titles, startcell for your Content, number of columns, number of rows.

CreateTable(oExcel, oSheet, i,"A2","A5",8,15)

I created some loops to fill the oTitles and oContent. I used a funtion to get the info for Excel.

Here is the full code:

Sub main
	
Dim oDrawDoc As DrawingDocument
 oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet

'ophalen template path definitie
oExcel = "D:\SUPERHEATERS\SUPERHEATER_Template1.xlsx"

i=1
For Each oSheet In oDrawDoc.Sheets
	
	CreateTable(oExcel, oSheet, i,"A2","A5",8,15)
	i=i+1
Next
	
End Sub
Sub CreateTable(ByVal oExcel As String, oSheet As Sheet,i As Integer, SC_Titles As String,SC_Content As String, oColumns As Integer,oRows As Integer)

	Dim oSheetName, oCell As String
	oSheetName = "Element" & i
	
	'calculate number of columns cells
	oCells = oColumns * oRows
	
	'get the column character and row number
	SC_Titles_Column = Left(SC_Titles,1)
	SC_Titles_row = Right(SC_Titles,Len(SC_Titles)-1)
	SC_Content_Column = Left(SC_Content,1)
	SC_Content_Row = Right(SC_Content, Len(SC_Content) -1)
	SC_Content_LastColumn = Chr(Asc(SC_Content_Column) + oColumns-1)
	oCell = ""

    Dim oTitles(oColumns - 1) As String
	Dim k As Integer
	k=0
	Do Until k = oColumns
		
		oCell = SC_Titles_Column & SC_Titles_row
		'Get value from Excel
		oTitles(k) = GetExcelValue(oExcel, oSheetName, oCell)
		'Set the next Column
	 	SC_Titles_Column = Chr(Asc(SC_Titles_Column) + 1)

		k=k+1
	Loop
	
	Dim oContents(oCells - 1) As String
	Dim j As Integer
	j=0
	Do Until j = oCells
		
		'check if last cell in row is reached	
		If SC_Content_Column = SC_Content_LastColumn Then
			SC_Content_Column = Left(SC_Content, 1)
			SC_Content_Row = SC_Content_Row+1
		End If
		
		oCell = SC_Content_Column & SC_Content_Row
		
		'Get value from Excel
		oContents(j) = GetExcelValue(oExcel, oSheetName, oCell)
		
		'Set the next Column
	 	SC_Content_Column = Chr(Asc(SC_Content_Column) + 1)

		j=j+1
	Loop
	
	Dim InsP As Point2d
    InsP = ThisApplication.TransientGeometry.CreatePoint2d(15, 15)

    Dim oCustomTable As CustomTable
    oCustomTable = oSheet.CustomTables.Add("CHURCH WINDOWS EXISTING WELDS", InsP, oColumns, oRows, oTitles,oContents)


End Sub
Function GetExcelValue(ByVal oExcel As String, oSheetName As String, oCell As String) As String

If GoExcel.CellValue(oExcel, oSheetName, oCell) Is Nothing Then
GetExcelValue = ""
Else
GetExcelValue = GoExcel.CellValue(oExcel, oSheetName, oCell)
End If

End Function

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Wed, 03 Nov 2021 13:54:58 GMT

@theo.bot

I have run the rule and the outcome is very different from what is on the spreadsheet.

unless I was meant to add values somewhere else along the code?

As you said, I just stipulated the start cell of the titles which is A2, start of content-A3 number of columns 8 (but I only get 5) and rows -15 which works out.

CreateTable(oExcel, oSheet, i,"A2","A3",8,15)

Does it work out in the format of the excel table when you try it out?

Please and Thank you!

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Wed, 03 Nov 2021 14:21:15 GMT

I made a small mistake 😉:

SC_Content_LastColumn = Chr(Asc(SC_Content_Column) + oColumns-1)

needed to be

SC_Content_LastColumn = Chr(Asc(SC_Content_Column) + oColumns)

here is the new full code:

Sub main
	
Dim oDrawDoc As DrawingDocument
 oDrawDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet

'ophalen template path definitie
oExcel = "D:\SUPERHEATERS\SUPERHEATER_Template1.xlsx"

i=1
For Each oSheet In oDrawDoc.Sheets
	
	CreateTable(oExcel, oSheet, i, "A2", "A3", 3, 10)
	i=i+1
Next
	
End Sub
Sub CreateTable(ByVal oExcel As String, oSheet As Sheet,i As Integer, SC_Titles As String,SC_Content As String, oColumns As Integer,oRows As Integer)

	Dim oSheetName, oCell As String
	oSheetName = "Element" & i
	
	'calculate number of columns cells
	oCells = oColumns * oRows
	
	'get the column character and row number
	SC_Titles_Column = Left(SC_Titles,1)
	SC_Titles_row = Right(SC_Titles,Len(SC_Titles)-1)
	SC_Content_Column = Left(SC_Content,1)
	SC_Content_Row = Right(SC_Content, Len(SC_Content) -1)
	SC_Content_LastColumn = Chr(Asc(SC_Content_Column) + oColumns)
	SC_Content_LastRow = SC_Content_Row+oRows
	oCell = ""

    Dim oTitles(oColumns - 1) As String
	Dim k As Integer
	k=0
	Do Until k = oColumns
		
		oCell = SC_Titles_Column & SC_Titles_row
		'Get value from Excel
		oTitles(k) = GetExcelValue(oExcel, oSheetName, oCell)
		'Set the next Column
	 	SC_Titles_Column = Chr(Asc(SC_Titles_Column) + 1)

		k=k+1
	Loop
	
	Dim oContents(oCells - 1) As String
	Logger.Info(oCells - 1)
	Dim j As Integer
	j=0
	Do Until j = oCells
		
		'check if last cell in row is reached	
		If SC_Content_Column = SC_Content_LastColumn Then
			SC_Content_Column = Left(SC_Content, 1)
			SC_Content_Row = SC_Content_Row+1
		End If
		
		oCell = SC_Content_Column & SC_Content_Row
		
		'Get value from Excel
		oContents(j) = GetExcelValue(oExcel, oSheetName, oCell)

		'Set the next Column
	 	SC_Content_Column = Chr(Asc(SC_Content_Column) + 1)

		j=j+1
	Loop
	
	Dim InsP As Point2d
    InsP = ThisApplication.TransientGeometry.CreatePoint2d(15, 15)

    Dim oCustomTable As CustomTable
    oCustomTable = oSheet.CustomTables.Add("CHURCH WINDOWS EXISTING WELDS", InsP, oColumns, oRows, oTitles,oContents)


End Sub
Function GetExcelValue(ByVal oExcel As String, oSheetName As String, oCell As String) As String

If GoExcel.CellValue(oExcel, oSheetName, oCell) Is Nothing Then
GetExcelValue = ""
Else
GetExcelValue = GoExcel.CellValue(oExcel, oSheetName, oCell)
End If

End Function

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Thu, 04 Nov 2021 07:54:57 GMT

Hello @theo.bot

I absolutely do appreciate the effort and thank you for rectifying a blunder I wouldn't have even picked out.

My question is why oh why are the columns restricted to just 5 columns on my side still?

I have included all the rows yet the columns are not entirely inclusive. I see in your first example you have atleast 6 columns.

	
	CreateTable(oExcel, oSheet, i, "A2", "A3", 10, 48)

Many thanks

Re: Trying to Reference Multiple Excel sheets into idw's

theo.bot — Thu, 04 Nov 2021 08:48:23 GMT

No issue at my side:

a strange thing at your side is the Column names that are retrieved blank from excel cells. They should appear as default column names like "column 5". In your screenshot it looks like a cellvalue is used, because there is "#" at column title.

Did you removed all old rules and replace the full code from my last reply? Or did you only replace parts from the code?

Re: Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye — Thu, 04 Nov 2021 09:31:19 GMT

Oh, you know @theo.bot , I went along and manually filled in the "#" value in the excel spreadsheet.

I have since removed them🙈

What a fool I was. Everything is one hundred percent now👌