Thread Rating:
  • 1 Vote(s) - 1 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel - Convert used range to table?
#1
Hello,

I would like to create a macro that uses COM for MS Excel to convert a used range to a table. Basically, I'd like to do what I can easily do through the Excel gui (Insert > Table) without going through the user interface.

Here's my first attempt:
Macro Excel Testing
Code:
Copy      Help
;typelib Excel {00020813-0000-0000-C000-000000000046} 1.9

ExcelSheet es.Init("" 1|4|16)

es.SetCell("Header_1" "A1")
es.SetCell("Header_2" "B1")
es.SetCell("Item_1" "A2")
es.SetCell("Item_2" "B2")

int c r cc rc
es.GetRangeInfo("<used>" c r cc rc)
VARIANT xRg=ExcelRange(c r cc rc)
VARIANT tStyle="TableStyleLight1"
VARIANT tName="Table1"
es.ws.ListObjects.Add(Excel.xlSourceRange xRg @ Excel.xlYes @ tStyle).Name=tName

I feel like I'm on the right track, but whenever I run the program, QM gives this error message on the last line: 0x80020009, Exception occurred

Advice? 
-Phil

P.S. - I am a long-time user (since around 2003) and first-time poster. I just have to say that QM is one of the most valuable tools I have ever purchased. It literally changed the course of my career for the better. I can't thank you, Gintaras, enough.
#2
Oh - I figured it out!

I made two mistakes.
1. The xRg variable needs to be an Excel.Range object. (Should have read the MSDN documentation for Excel ListObjects.Add more closely.)
2. The first parameter should be Excel.xlSrcRange, not Excel.xlSourceRange. (I'm surprised that both are valid, but only one can be used in this case. I read right over that when I read the MSDN documentation.)

So, this code works:
Macro Excel Testing
Code:
Copy      Help
;Prep sample Excel File
ExcelSheet es.Init("" 4|16)
es.SetCell("Header_1" "A1")
es.SetCell("Header_2" "B1")
es.SetCell("Item_1" "A2")
es.SetCell("Item_2" "B2")

;Convert used range to table
Excel.Range xRg=es.ws.UsedRange
VARIANT tStyle="TableStyleLight1"
es.ws.ListObjects.Add(Excel.xlSrcRange xRg @ Excel.xlYes @ tStyle).Name="MyTable"

Thank you anyway! Please keep up the great work. 

-Phil


Forum Jump:


Users browsing this thread: 2 Guest(s)