Community

Trying to Reference Multiple Excel sheets into idw's

Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye
Enthusiast Enthusiast
2,159 Views
26 Replies
Message 1 of 27

Trying to Reference Multiple Excel sheets into idw's

emilda.chiuye
Enthusiast
Enthusiast
‎10-31-2021 08:24 AM

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.

emildachiuye_1-1635693404060.png

 

emildachiuye_0-1635692694112.png

Please help. 

Thank you

0 Likes
Like
Report
Accepted solutions (4)
Solution 1
Solution 2
Solution 3
Solution 4
2,160 Views
26 Replies
Replies (26)
Message 2 of 27

theo.bot
Collaborator
Collaborator
‎11-01-2021 12:58 AM
Accepted solution

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

theobot_0-1635753261504.png

 

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

 

Preview file
11 KB
Report
Message 3 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-01-2021 07:40 AM

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.

 

emildachiuye_0-1635776733358.png

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

 

0 Likes
Like
Report
Message 4 of 27

theo.bot
Collaborator
Collaborator
in reply to emilda.chiuye
‎11-01-2021 08:11 AM
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.
Report
Message 5 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-01-2021 10:02 AM

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?

 

 

emildachiuye_0-1635784618091.png

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

 

0 Likes
Like
Report
Message 6 of 27

theo.bot
Collaborator
Collaborator
in reply to emilda.chiuye
‎11-01-2021 11:28 PM

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).

Report
Message 7 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-01-2021 11:52 PM

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

0 Likes
Like
Report
Message 8 of 27

theo.bot
Collaborator
Collaborator
in reply to emilda.chiuye
‎11-02-2021 01:17 AM

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"

 

Report
Message 9 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-02-2021 03:35 AM

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.

emildachiuye_1-1635848078281.png

 

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

 

emildachiuye_0-1635848024135.png

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

0 Likes
Like
Report
Message 10 of 27

theo.bot
Collaborator
Collaborator
in reply to emilda.chiuye
‎11-02-2021 03:38 AM
Can you share your drawing and your excel file? It's easier to troubleshoot 😉
0 Likes
Like
Report
Message 11 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-02-2021 04:39 AM

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.

Preview file
178 KB
CHURCH WINDOWS.idw
0 Likes
Like
Report
Message 12 of 27

theo.bot
Collaborator
Collaborator
in reply to emilda.chiuye
‎11-02-2021 05:35 AM
Accepted solution

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

 

theobot_0-1635856503913.png

 

Report
Message 13 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-02-2021 06:28 AM

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👍

Report
Message 14 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to emilda.chiuye
‎11-03-2021 04:00 AM

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

emildachiuye_0-1635936334243.png

 

 

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

 

0 Likes
Like
Report
Message 15 of 27

theo.bot
Collaborator
Collaborator
in reply to emilda.chiuye
‎11-03-2021 05:45 AM

@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

 

0 Likes
Like
Report
Message 16 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-03-2021 06:54 AM

@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)

emildachiuye_1-1635946631046.png

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

Please and Thank you!

0 Likes
Like
Report
Message 17 of 27

theo.bot
Collaborator
Collaborator
in reply to emilda.chiuye
‎11-03-2021 07:20 AM
Accepted solution

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)

theobot_0-1635949193379.png

 

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 

 

Report
Message 18 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-04-2021 12:54 AM

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

emildachiuye_0-1636011962890.png

 

0 Likes
Like
Report
Message 19 of 27

theo.bot
Collaborator
Collaborator
in reply to emilda.chiuye
‎11-04-2021 01:48 AM

No issue at my side:

 

theobot_0-1636015381225.png

 

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?

Report
Message 20 of 27

emilda.chiuye
Enthusiast
Enthusiast
in reply to theo.bot
‎11-04-2021 02:31 AM

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👌

emildachiuye_0-1636018182384.png

 

 

Report
Report a website issue