Problem insert table to Autocad from Excel by VBA

[malmal02122023]

Hello,

Could you help me please?

I try to insert the table to AutoCad from Excel but I have a problem with function definition  as shown below
AutoCad2019

Sub GetExcel()
'Excel
Dim wb As excel.workbook
Dim ws As excel.worksheet
Dim text As String
Set wb = GetExcelObject()
Set ws = wb.sheets("CAD")
'CAD
Dim vinsertionpoint As Variant
Dim lnumberofrows As Long
Dim lnumberofcolumns As Long
Dim otable As AcadTable
With ThisDrawing.Utility
vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
End With
lnumberofrows = 6
lnumberofcolumns = 5
Set otable = ThisDrawing.ModelSpace.AddTable(vinsertionpoint, _
lnumberofrows, lnumberofcolumns, 5, 15)
otable.SetText 0, 0, "Emacro"
'excel -> CAD
text = ws.cells(1, 1)
ThisDrawing.Utility.Prompt (text) + vbNewLine
otable.SetText 1, 0, text

For irow = 1 To lnumberofrow - 1
For icolumn = 0 To lnumberofcolumn - 1
'text = cstr(irow) + "," + cstr(icolumn)
text = ws.cells(irow, icolumn + 1)
otable.SetText irow, icolumn, text
Next
Next
End Sub

Function GetExcelObject() As excel.workbook

Dim wb As excel.workbook
Dim path As String
path = "C:\Users\amali\Downloads\TEST TABLE.xlsx"
Dim ret
ret = isworkbookopen(path)
If ret = True Then
Set wb = VBA.GetObject(path)
ThisDrawing.Utility.Prompt ("excel open") + vbNewLine
Else
Set wb = excel.workboks.Open(path)
ThisDrawing.Utility.Prompt ("excel closed") + vbNewLine
End If
Set getexcelobjet = wb
End Function
Function isworkbookopen(filename As String)
Dim ff As slong, errno As Long
On Error Resume Next
ff = FreeFile()
Open filename For Input Lock Read As #ff
Close ff
errno = Err
On Error GoTo 0
Select Case errno
Case 0: isworkbookopen = False
Case 70: isworkbookopen = True
Case Else: Error errno
End Select
End Function

Sub GE()
'excel
Dim wb As excel.workbook
Dim ws As excel.worksheet
Dim rg As excel.range
Dim text As String
Set wb = GetExcelObject()
Set ws = wb.sheets("Presupuesto")
'cad
Dim vinsertionpoint As Variant
Dim lnumberofrows As Long
Dim lnumberofcolumns As Long
Dim otable As AcadTable
On Error Resume Next
With ThisDrawing.Utility
vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
End With
Lnumberorows = 5
lnumberofcolumns = 5
If Err Then Exit Sub
Set otable = ThisDrawing.ModelSpace.adtable(vinsertionpoint, _
Lnumberogrows, lnumberofcolumns, 5, 50)
text = ws.cells(1, 1)
This drawing.Utility.Prompt(text) + vbNewLine
otable.SetText 0, 0, "Presupuesto"
For irow = 1 To lnumberofrows
For icolumn = 1 To 5
Set rg = ws.cells(irow, icolumn)
text = rg.text
otable.SetText irow, icolumn - 1, text
Next
Next
End Sub

Thank you for advice

Best regards
Olek

[Ed__Jobe]

You need to add a reference to excel. Tools>References.

[malmal02122023]

Thank you, I have added the library, it works but
error 91 appears
Is it missing extra library or I need to rewrite the code?

Sub GetExcel()
'Excel
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim text As String
Set wb = GetExcelObject()
Set ws = wb.Sheets("CAD")
'CAD
Dim vinsertionpoint As Variant
Dim lnumberofrows As Long
Dim lnumberofcolumns As Long
Dim otable As AcadTable
With ThisDrawing.Utility
vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
End With
lnumberofrows = 6
lnumberofcolumns = 5
Set otable = ThisDrawing.ModelSpace.AddTable(vinsertionpoint, _
lnumberofrows, lnumberofcolumns, 5, 15)
otable.SetText 0, 0, "Emacro"
'excel -> CAD
text = ws.Cells(1, 1)
ThisDrawing.Utility.Prompt (text) + vbNewLine
otable.SetText 1, 0, text

For irow = 1 To lnumberofrow - 1
For icolumn = 0 To lnumberofcolumn - 1
'text = cstr(irow) + "," + cstr(icolumn)
text = ws.Cells(irow, icolumn + 1)
otable.SetText irow, icolumn, text
Next
Next
End Sub
Function GetExcelObject() As Excel.Workbook
Dim wb As Excel.Workbook
Dim path As String
path = "C:\Users\amali\Downloads\TEST TABLE.xlsx"
Dim ret
ret = isworkbookopen(path)
If ret = True Then
Set wb = VBA.GetObject(path)
ThisDrawing.Utility.Prompt ("excel open") + vbNewLine
Else
Set wb = Excel.Workbooks.Open(path)
ThisDrawing.Utility.Prompt ("excel closed") + vbNewLine
End If
Set getexcelobjet = wb
End Function
Function isworkbookopen(filename As String)
Dim ff As Long, errno As Long
On Error Resume Next
ff = FreeFile()
Open filename For Input Lock Read As #ff
Close ff
errno = Err
On Error GoTo 0
Select Case errno
Case 0: isworkbookopen = False
Case 70: isworkbookopen = True
Case Else: Error errno
End Select
End Function

Sub GE()
'excel
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rg As Excel.Range
Dim text As String
Set wb = GetExcelObject()
Set ws = wb.Sheets("Presupuesto")
'cad
Dim vinsertionpoint As Variant
Dim lnumberofrows As Long
Dim lnumberofcolumns As Long
Dim otable As AcadTable
On Error Resume Next
With ThisDrawing.Utility
vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
End With
Lnumberorows = 5
lnumberofcolumns = 5
If Err Then Exit Sub
Set otable = ThisDrawing.ModelSpace.adtable(vinsertionpoint, _
Lnumberogrows, lnumberofcolumns, 5, 50)
text = ws.Cells(1, 1)
ThisDrawing.Utility.Prompt (text) + vbNewLine
otable.SetText 0, 0, "Presupuesto"
For irow = 1 To lnumberofrows
For icolumn = 1 To 5
Set rg = ws.Cells(irow, icolumn)
text = rg.text
otable.SetText irow, icolumn - 1, text
Next
Next
End Sub

[norman.yuan]

You need to step through function GetExcelObject() in debugging mode to see why it returns Nothing. Since you did not show the code of the function VBA.GetObject(path as String), no one, but you, can tell what goes wrong.

Also, please post your code by using "</>" button, so the code is easily readable.

[Ed__Jobe]

Your code is poorly organized to begin with, and when you don't post it in a code window, it makes it even harder to read. Please click on the 3 vertical dots and select Edit Post and then use this to post your code. Then I will be better able to comment on your code.

[malmal02122023]

Sub GetExcel()
  'Excel
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  Dim text As String
  Set wb = GetExcelObject()
  Set ws = wb.Sheets("CAD")
  'CAD
  Dim vinsertionpoint As Variant
  Dim lnumberofrows As Long
  Dim lnumberofcolumns As Long
  Dim otable As AcadTable
  With ThisDrawing.Utility
    vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
  End With
  lnumberofrows = 6
  lnumberofcolumns = 5
  Set otable = ThisDrawing.ModelSpace.AddTable(vinsertionpoint, _
  lnumberofrows, lnumberofcolumns, 5, 15)
  otable.SetText 0, 0, "Emacro"
  'excel -> CAD
  text = ws.Cells(1, 1)
  ThisDrawing.Utility.Prompt (text) + vbNewLine
  otable.SetText 1, 0, text

  For irow = 1 To lnumberofrow - 1
    For icolumn = 0 To lnumberofcolumn - 1
      'text = cstr(irow) + "," + cstr(icolumn)
      text = ws.Cells(irow, icolumn + 1)
      otable.SetText irow, icolumn, text
    Next
  Next
End Sub


Function GetExcelObject() As Excel.Workbook
  Dim wb As Excel.Workbook
  Dim path As String
  path = "C:\Users\amali\Downloads\TEST TABLE.xlsx"
  Dim ret
  ret = isworkbookopen(path)
  If ret = True Then
    Set wb = VBA.GetObject(path)
    ThisDrawing.Utility.Prompt ("excel open") + vbNewLine
  Else
    Set wb = Excel.Workbooks.Open(path)
    ThisDrawing.Utility.Prompt ("excel closed") + vbNewLine
  End If
  Set getexcelobjet = wb
End Function


Function isworkbookopen(filename As String)
  Dim ff As Long, errno As Long
  On Error Resume Next
  ff = FreeFile()
  Open filename For Input Lock Read As #ff
  Close ff
  errno = Err
  On Error GoTo 0
  Select Case errno
    Case 0: isworkbookopen = False
    Case 70: isworkbookopen = True
    Case Else: Error errno
  End Select
End Function

Sub GE()
  'excel
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  Dim rg As Excel.Range
  Dim text As String
  Set wb = GetExcelObject()
  Set ws = wb.Sheets("Presupuesto")
  'cad
  Dim vinsertionpoint As Variant
  Dim lnumberofrows As Long
  Dim lnumberofcolumns As Long
  Dim otable As AcadTable
  On Error Resume Next
  With ThisDrawing.Utility
    vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
  End With
  Lnumberorows = 5
  lnumberofcolumns = 5
  If Err Then Exit Sub
  Set otable = ThisDrawing.ModelSpace.adtable(vinsertionpoint, _
  Lnumberogrows, lnumberofcolumns, 5, 50)
  text = ws.Cells(1, 1)
  ThisDrawing.Utility.Prompt (text) + vbNewLine
  otable.SetText 0, 0, "Presupuesto"
  For irow = 1 To lnumberofrows
    For icolumn = 1 To 5
      Set rg = ws.Cells(irow, icolumn)
      text = rg.text
      otable.SetText irow, icolumn - 1, text
    Next
  Next
End Sub

Please take a look, 

Thank you

[Ed__Jobe]

I edited your code to add indenting. It makes it even easier to read by indicating the hierarchical structure. I don't know that you need all those functions. There is some redundancy. Also, you've taken my GetExcel function and added stuff to it that doesn't match the purpose of the function which is to get an excel application object, not a workbook. A function should always only do one thing.

If you need to add to that function's capability, then create a new function that calls the first one. The reasoning behind this is to be able to test small blocks of code. Once you test a block of code, then you don't have to keep testing it. But if you put everything together all tangled like spaghetti, then its really hard to test your code. It also makes your code reusable, by making it available to other subs.

Another tip, xl has a problem that requires you to dispose of xl objects in the reverse order in which they were created. Otherwise they will always stay in memory. For example:

new xl app

  new workbook

    new worksheet

     do your work

    worksheet = Nothing

  workbook = Nothing

app = Nothing

[Ed__Jobe]

I couldn't test it because I don't have your drawing or spreadsheet but the below code should work.

Public Function GetXL() As Excel.Application
    On Error Resume Next
    Dim xlApp As Excel.Application
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    Set GetXL = xlApp
End Function

Sub AddTableFromXL()
  'excel
  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  Dim rg As Excel.Range
  Dim text As String
  
  Set xl = GetXL()
  'open readonly avoids checking for open file. You're only copying info anyway.
  Set wb = xl.Workbooks.Open("C:\Users\amali\Downloads\TEST TABLE.xlsx", , True)
  Set ws = wb.Sheets("Presupuesto")
  
  'cad
  Dim vinsertionpoint As Variant
  Dim lnumberofrows As Long
  Dim lnumberofcolumns As Long
  Dim otable As AcadTable
  On Error Resume Next
  With ThisDrawing.Utility
    vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
  End With
  Lnumberorows = 5
  lnumberofcolumns = 5
  If Err Then Exit Sub
    Set otable = ThisDrawing.ModelSpace.adtable(vinsertionpoint, _
    Lnumberogrows, lnumberofcolumns, 5, 50)
    text = ws.Cells(1, 1)
    ThisDrawing.Utility.Prompt (text) + vbNewLine
    otable.SetText 0, 0, "Presupuesto"
    For irow = 1 To lnumberofrows
      For icolumn = 1 To 5
        Set rg = ws.Cells(irow, icolumn)
        text = rg.text
        otable.SetText irow, icolumn - 1, text
      Next
    Next
    
    'dispose
    Set rg = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xl = Nothing
End Sub

[malmal02122023]

Thank you very much for help. I have corrected my code, it works. I tried to run your code but it didn't insert the table and didn't give any errors.

Sub GetExcel()
  'Excel
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  Dim text As String
  Set wb = GetExcelObject()
  Set ws = wb.Sheets("CAD")
  
  'CAD
  Dim vinsertionpoint As Variant
  Dim lnumberofrows As Long
  Dim lnumberofcolumns As Long
  Dim otable As AcadTable
  
  With ThisDrawing.Utility
    vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
  End With
  
  lnumberofrows = 6
  lnumberofcolumns = 5
  
  Set otable = ThisDrawing.ModelSpace.AddTable(vinsertionpoint, _
  lnumberofrows, lnumberofcolumns, 5, 15)
  
  otable.SetText 0, 0, "Emacro"
  'excel -> CAD
  text = ws.Cells(1, 1)
  ThisDrawing.Utility.Prompt (text) + vbNewLine
  otable.SetText 1, 0, text
  
  
  For irow = 1 To lnumberofrows - 1
  
    For icolumn = 0 To lnumberofcolumns - 1
      'text = cstr(irow) + "," + cstr(icolumn)
      text = ws.Cells(irow, icolumn + 1)
      otable.SetText irow, icolumn, text
    Next
  Next
End Sub


Function GetExcelObject() As Excel.Workbook

      Dim wb As Excel.Workbook
      
      Dim path As String
      path = "C:\Users\amali\Downloads\TEST TABLE.xlsx"
      
      Dim ret
      
      ret = isworkbookopen(path)
      
      If ret = True Then
      Set wb = VBA.GetObject(path)
      ThisDrawing.Utility.Prompt ("excel open") + vbNewLine
       Else
        Set wb = Excel.Workbooks.Open(path)
        ThisDrawing.Utility.Prompt ("excel closed") + vbNewLine
      End If
      
      Set GetExcelObject = wb
    End Function

    Function isworkbookopen(filename As String)
      Dim ff As Long, errno As Long
      
      On Error Resume Next
      ff = FreeFile()
      Open filename For Input Lock Read As #ff
      Close ff
      errno = Err
      On Error GoTo 0
      
       Select Case errno
        Case 0: isworkbookopen = False
        Case 70: isworkbookopen = True
        Case Else: Error errno
      End Select
    End Function
    
    Sub GE()
      'excel
       Dim wb As Excel.Workbook
       Dim ws As Excel.Worksheet
       Dim rg As Excel.Range
       Dim text As String
       Set wb = GetExcelObject()
      Set ws = wb.Sheets("Presupuesto")
      
      'cad
      Dim vinsertionpoint As Variant
      Dim lnumberofrows As Long
      Dim lnumberofcolumns As Long
      Dim otable As AcadTable
      
       On Error Resume Next
       
      With ThisDrawing.Utility
        vinsertionpoint = .GetPoint(, vbCr & "pick the insertion point: ")
      End With
      
      lnumberofrows = 5
      lnumberofcolumns = 5
      
      If Err Then Exit Sub
      
       Set otable = ThisDrawing.ModelSpace.addtable(vinsertionpoint, _
       lnumberofrows, lnumberofcolumns, 5, 50)
       
       text = ws.Cells(1, 1)
       ThisDrawing.Utility.Prompt (text) + vbNewLine
       
       otable.SetText 0, 0, "Presupuesto"
       
       
       For irow = 1 To lnumberofrows
     
        For icolumn = 1 To 5
           Set rg = ws.Cells(irow, icolumn)
           text = rg.text
           otable.SetText irow, icolumn - 1, text
         Next
       Next
     End Sub

[Ed__Jobe]

---

@malmal02122023 wrote:

Thank you very much for help. I have corrected my code, it works. I tried to run your code but it didn't insert the table and didn't give any errors.

---

I told you, I wasn't able to test the table part because I don't have your spreadsheet.If you post a sample of your spreadsheet, I might be able to test it. But it's probably because of your use of On Error and checking for the Err object.

Your code is very messy. It creates xl objects many times, unnecessarily. and you don't dispose of the objects when you are done. To see what I mean, start TaskManager and I bet you will see that there are many instances of Excel still running. You now have to use Task Manager to end all those tasks.

[malmal02122023]

Please see the example table in attachment.

[malmal02122023]

I have a question. Is it possible to delete the row - title in the  table so that only dates  and header remain

or better to create table from a  data link by vba but I just don't know where to start.

[Ed__Jobe]

What I would recommend is to create a table style that doesn't have a Tile row, and then after you do ModelSpace.AddTable, assign the TableStyle to the table. The easiest way to do this is add a table. Then delete the Title row. Now select the table, right-click>Table Style>Save as new table style.

[norman.yuan]

Addition to what @Ed__Jobe said (since you mentioned DataLink):

Using DataLink to connect data between AutoCAD's table and Excel sheet (which can update data in both direction) you still need to create a AutoCAD table, manually, or by code.

Unfortunately, VBA does not have API to manipulate DataLink. You can use AutoCAD .NET API to connect/update table with DataLink, if you do .NET API programming (time to leave VBA behind?).

[Ed__Jobe · Accepted solution]

@malmal02122023 I didn't catch that you wanted a datalink. Anyway, here is some working code and a modified spreadsheet that has tables defined to create named ranges that you can refer to from acad. Here are some other problems that you had in your code.

1. many misspelled variables. Always use Option Explicit in the top of your modules to have the compiler catch these errors. Similarly, you misspelled some object methods.

2. Don't test for Err, it always exists. Test for Err.Number>0 or some other value.

3. Don't use On Error to get user input. Test for a condition.

4. AddTable was misspelled as adTable

Option Explicit


Sub AddTableFromXL()
    On Error GoTo Err_Control
    'excel
    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim rg As Excel.Range
    Dim path As String
    Dim text As String
    
    Set xl = GetXL()
    path = "C:\Users\username\Downloads\TEST TABLE.xlsx"
    'open readonly.
    Set wb = xl.Workbooks.Open(path, , True)
WbSet:
    Set ws = wb.Sheets("CAD")
    Set rg = ws.Range("Table1")
    
    'cad
    Dim vInsertionPoint1 As Variant
    Dim vInsertionPoint2(0 To 2) As Double
    Dim oTable As AcadTable
    Dim iRow As Integer
    Dim iColumn As Integer
    Dim oText As String
    
GetIP:
    With ThisDrawing.Utility
        vInsertionPoint1 = .GetPoint(, vbCr & "Pick the table insertion point: ")
    End With
    
    ThisDrawing.SetVariable "CTABLESTYLE", "NoTitle"
    'Add an extra row to account for table title row.
    Set oTable = ThisDrawing.ModelSpace.AddTable(vInsertionPoint1, rg.Rows.Count + 2, rg.Columns.Count, 0.25, 2)
    'Delete table title row
    oTable.DeleteRows 0, 1
    For iRow = 0 To rg.Rows.Count
        For iColumn = 1 To rg.Columns.Count
            oText = rg.Cells(iRow, iColumn)
            oTable.SetText iRow, iColumn - 1, oText
        Next
    Next
    
    Set ws = wb.Sheets("PRESUPUESTO")
    Set rg = ws.Range("Table2")
    ThisDrawing.SetVariable "CTABLESTYLE", "STANDARD"
    'new table 12 units to the right
    vInsertionPoint2(0) = vInsertionPoint1(0) + 12: vInsertionPoint2(1) = vInsertionPoint1(1): vInsertionPoint2(2) = vInsertionPoint1(2)
    'Add an extra row to account for table title row.
    Set oTable = ThisDrawing.ModelSpace.AddTable(vInsertionPoint2, rg.Rows.Count + 2, rg.Columns.Count, 0.25, 2)
    'Delete table title row
    oTable.DeleteRows 0, 1
    For iRow = 0 To rg.Rows.Count
        For iColumn = 1 To rg.Columns.Count
            oText = rg.Cells(iRow, iColumn)
            oTable.SetText iRow, iColumn - 1, oText
        Next
    Next
      
Exit_Here:
    'Cleanup xl objects
    Set rg = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xl = Nothing
    Exit Sub
Err_Control:
    Select Case Err.Number
    'Add your Case selections here
    Case Is = 1000
        'User didn't select a valid point
        Err.Clear
        Resume GetIP
    Case Is = 1004
        'File is already open, just get it.
        Err.Clear
        Set wb = xl.Workbooks("TEST TABLE.xlsx")
        Resume WbSet
    Case Else
        MsgBox Err.Number & ", " & Err.Description, , "AddTableFromXL"
        Err.Clear
        Resume Exit_Here
    End Select
End Sub

Public Function GetXL() As Excel.Application
    On Error Resume Next
    Dim xlApp As Excel.Application
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    Set GetXL = xlApp
End Function

[malmal02122023]

Thank you for advice to use AutoCad NET API. 
For data link ecxel spreadsheet I used AutoCad NET API C# code.

I'm facing a problem defining variables i and j - row and columns number as shown below:
tb.SetAlignment(i, j, CellAlignment.MiddleCenter);
How can I determine variables if the number of rows and columns is taken from Excel by data link?

[norman.yuan]

If you want to use DataLink to connect data in Excelsheet to a table entity in AutoCAD with .NET API, you need to either have a existing DataLink in drawing already, or create one with code, then, you need to have a AutoCAD table entity created, then you can call Table.SetDataLink(rowIndex, columnIndex, dataLinkObject, true/false); that is specify a range of cells in the table to connect data in the DataLink, which represents the data in a range of cell of the spreadsheet. I am not sure Table.SetAlignment() has anything to do with DataLink here.

When you call SetDataLink with starting row index and starting column index, you do not have to worry/know how many row/columns of data exist in the spreadsheet. the AutoCAD table would automatically expand its rows/columns to hold the data. However, you should get the source data's row/column information from DataLink.ConnectionoString property, or you can define the source's rows/columns to be connected via DataLink.ConnectionString property.

Tip: you can manually define a DataLink when create a table in AutoCAD and save the drawing. Then you can write some test code agist this drawing to find the existing DataLink and examine its ConnectionString property.

Since this is VBA/COM API forum, DataLink is a topic off this forum. If you go to .NET discussion forum and search for "DataLink", you may find a few past discussion with code samples.

[malmal02122023]

After adding a table to a drawing, I try to change the table format. In the Excel table there is a table with 9 rows that is exported to AutoCAD by Datalink. So i want to know how to determine the number of rows. Or is it possible to assign a "header" (without title) only to the first line and a "data" to the rest in a different way also for cells margin and text? From what I was able to find, I can format a table by defining the number of columns and rows [i, j].

Thank you. 

 for (int i = 0; i < 9; i++)
 {
     for (int j = 0; j < 12; j++)
     {

         tb.Cells[i, j].Style = "Data";
         tb.Cells[0, j].Style = "Header";

         
         tb.Cells[i, j].Borders.Top.Margin = 0.2;
         tb.Cells[i, j].Borders.Bottom.Margin = 0.2;

         tb.SetRowHeight(i, 6.8);

         tb.Cells[i, j].TextHeight = 3.5;
         tb.Cells[i, j].Alignment =
         CellAlignment.MiddleCenter;

         
     }
 }

[Ed__Jobe]

You're trying to overthink it. You don't understand how datalink and tables work. Do it manually first or read up on it in Help. Use the DATALINK command and select a named range or the print area to link to. Then insert a table and select the DataLink option. The whole table is filled out automatically and updates if the spreadsheet is edited.

[malmal02122023]

That's right. Only I select not a range of data from a sheet, but a whole sheet from the list of sheets for example sheet No.1, 2, 3 etc. no range.