Macro Excel export - available Excel methods?

Hi, I've adapted a standard Data Architect macro to export relationships into Excel. I would like to format the exported range as a Table in Excel. So I created a working macro in Excel and tried adding the call to my code.. but I can't get it to work. I get a run time error. [img]//community.embarcadero.com/uploads/12676/Capture.PNG[/img] As it is a very simple line of code that works in Excel but not in the Data Architect Macro I'm wondering what Excel methods are available to call and if this is one of them? The method call is of the form: [i]ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$10"), , xlYes).Name = "Table1"[/i] And I added it to Export Relationship Info to Excel v2.bas: (in bold below):
[color=#0000ff]Sub[/color] [color=#000000]Main[/color]
								
								[color=#0000ff]Dim[/color] theDiagram [color=#0000ff]As[/color] [color=#000080]Diagram[/color]
								[color=#0000ff]Dim[/color] theModel [color=#0000ff]As[/color] [color=#000080]Model[/color]
								
								
								[color=#0000ff]If[/color] ([color=#000080]DiagramManager.DiagramCount[/color] = 0) [color=#0000ff]Then[/color]
								[color=#008080]MsgBox[/color]([color=#800000]"Please open a model before running macro.."[/color], [color=#008080]vbExclamation[/color])
								[color=#0000ff]Exit[/color] [color=#0000ff]Sub[/color]
								[color=#0000ff]End[/color] [color=#0000ff]If[/color]
								
								[color=#0000ff]Set[/color] theDiagram = [color=#000080]DiagramManager.ActiveDiagram[/color]
								[color=#0000ff]Set[/color] theModel = theDiagram[color=#000080].ActiveModel[/color]
								[color=#0000ff]If[/color] (theModel[color=#000080].Relationships.Count[/color] = 0) [color=#0000ff]Then[/color]
								[color=#008080]MsgBox[/color]([color=#800000]"There are no relationships in this model."[/color], [color=#008080]vbExclamation[/color])
								[color=#0000ff]Exit[/color] [color=#0000ff]Sub[/color]
								[color=#0000ff]End[/color] [color=#0000ff]If[/color]
								
								
								[color=#008000]' Create Excel workbook.[/color]
								[color=#0000ff]Set[/color] [color=#000000]Excel[/color] = [color=#008080]CreateObject[/color]([color=#800000]"Excel.Application"[/color])
								[color=#000000]Excel[/color].Workbooks[color=#000080].Add[/color]
								
								[color=#008000]' Init the ER/Studio variables.[/color]
								[color=#000000]curRow[/color] = 1
								[color=#000000]curCol[/color] = 1
								
								[color=#000000]clrBack[/color] = [color=#000000]CLR_WHITE[/color]
								[color=#000000]clrFore[/color] = [color=#000000]CLR_BLACK[/color]
								[color=#000000]clrTitleBack[/color] = [color=#000000]CLR_GREY[/color]
								[color=#000000]clrTitleFore[/color] = [color=#000000]CLR_BLACK[/color]
								
								[color=#008000]' Export[/color]
								[color=#000000]PrintColumnHeader[/color]
								[color=#000000]PrintData[/color](theModel)
								[color=#000000]AutofitAllUsed[/color]()
								
								[b][color=#000000]Excel[/color].ActiveSheet.ListObjects[color=#000080].Add[/color](xlSrcRange, [size= 1.2em; line-height: 1.3em; color: #000000]Excel[/size][size= 1.2em; line-height: 1.3em].ActiveSheet.UsedRange,[/size] , xlYes)[color=#000080].Name[/color] = [color=#800000]"Table1"[/color]
								[/b]
								[color=#008080]MsgBox[/color]([color=#800000]"Export Complete!"[/color], [color=#008080]vbInformation[/color],[color=#800000]"ER/Studio")[/color]
								
								[color=#008000]'make Excel visible[/color]
								[color=#000000]Excel[/color].Visible = [color=#0000ff]True[/color]
								
								[color=#0000ff]End[/color] [color=#0000ff]Sub[/color]
  • Hi, I've adapted a standard Data Architect macro to export relationships into Excel. I would like to format the exported range as a Table in Excel. So I created a working macro in Excel and tried adding the call to my code.. but I can't get it to work. I get a run time error. [img]//community.embarcadero.com/uploads/12676/Capture.PNG[/img] As it is a very simple line of code that works in Excel but not in the Data Architect Macro I'm wondering what Excel methods are available to call and if this is one of them? The method call is of the form: [i]ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$10"), , xlYes).Name = "Table1"[/i] And I added it to Export Relationship Info to Excel v2.bas: (in bold below): <pre style="color: #080000;">[color=#0000ff]Sub[/color] [color=#000000]Main[/color] [color=#0000ff]Dim[/color] theDiagram [color=#0000ff]As[/color] [color=#000080]Diagram[/color] [color=#0000ff]Dim[/color] theModel [color=#0000ff]As[/color] [color=#000080]Model[/color] [color=#0000ff]If[/color] ([color=#000080]DiagramManager.DiagramCount[/color] = 0) [color=#0000ff]Then[/color] [color=#008080]MsgBox[/color]([color=#800000]"Please open a model before running macro.."[/color], [color=#008080]vbExclamation[/color]) [color=#0000ff]Exit[/color] [color=#0000ff]Sub[/color] [color=#0000ff]End[/color] [color=#0000ff]If[/color] [color=#0000ff]Set[/color] theDiagram = [color=#000080]DiagramManager.ActiveDiagram[/color] [color=#0000ff]Set[/color] theModel = theDiagram[color=#000080].ActiveModel[/color] [color=#0000ff]If[/color] (theModel[color=#000080].Relationships.Count[/color] = 0) [color=#0000ff]Then[/color] [color=#008080]MsgBox[/color]([color=#800000]"There are no relationships in this model."[/color], [color=#008080]vbExclamation[/color]) [color=#0000ff]Exit[/color] [color=#0000ff]Sub[/color] [color=#0000ff]End[/color] [color=#0000ff]If[/color] [color=#008000]' Create Excel workbook.[/color] [color=#0000ff]Set[/color] [color=#000000]Excel[/color] = [color=#008080]CreateObject[/color]([color=#800000]"Excel.Application"[/color]) [color=#000000]Excel[/color].Workbooks[color=#000080].Add[/color] [color=#008000]' Init the ER/Studio variables.[/color] [color=#000000]curRow[/color] = 1 [color=#000000]curCol[/color] = 1 [color=#000000]clrBack[/color] = [color=#000000]CLR_WHITE[/color] [color=#000000]clrFore[/color] = [color=#000000]CLR_BLACK[/color] [color=#000000]clrTitleBack[/color] = [color=#000000]CLR_GREY[/color] [color=#000000]clrTitleFore[/color] = [color=#000000]CLR_BLACK[/color] [color=#008000]' Export[/color] [color=#000000]PrintColumnHeader[/color] [color=#000000]PrintData[/color](theModel) [color=#000000]AutofitAllUsed[/color]() [b][color=#000000]Excel[/color].ActiveSheet.ListObjects[color=#000080].Add[/color](xlSrcRange, [size= 1.2em; line-height: 1.3em; color: #000000]Excel[/size][size= 1.2em; line-height: 1.3em].ActiveSheet.UsedRange,[/size] , xlYes)[color=#000080].Name[/color] = [color=#800000]"Table1"[/color] [/b] [color=#008080]MsgBox[/color]([color=#800000]"Export Complete!"[/color], [color=#008080]vbInformation[/color],[color=#800000]"ER/Studio")[/color] [color=#008000]'make Excel visible[/color] [color=#000000]Excel[/color].Visible = [color=#0000ff]True[/color] [color=#0000ff]End[/color] [color=#0000ff]Sub[/color]</pre>
  • Hi,
    Have you defined the excel constants xlSrcRange and xlYes? They are only recognized within excel. If not you need to define them first.