Using Controls in Excel?

[terrypin]

Anyone used the Control commands in Excel?

 

It seems here (Excel 365 with Win 10) that whatever cell I try to capture with Get Control followed by Get Control Text, the result is always the data in row 1 of that column.

 

--

Terry, East Grinstead, UK

 

[acantor]

Try saving the control using its "coordinates" (instead of its "z-order.")

[terrypin]

Thanks Alan, but I'd tried all three options.

 

The result I described previously was wrong. By coincidence the first cell in the column of the worksheet I was working on held the name of the worksheet itself. I've tested again with a fresh worksheet just containing 'Cell A1' and 'Cell A2' in A1 and A2 respectively. The result (for all three save types) is in fact not the contents of the top cell, but the name of the worksheet.

 

Very easy to reproduce (or not) if anyone has a minute.

 

<GET CONTROL Flags="0" Program_Name="EXCEL.EXE" Class="XLMAIN" Title="TestingCopy-UsingMX-Controls.xlsm - Excel" Control="\"\",\"XLDESK\",\"TestingCopy-UsingMX-Controls.xlsm\",\"EXCEL7\"" Variable="%cSource%" Caption="TestingCopy-UsingMX-Controls.xlsm - Excel" Partial="FALSE" Wildcards="FALSE"/>
<GET CONTROL TEXT TextVar="%tResult%" ControlVar="%cSource%"/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil Tahoma;}{\\f1\\fnil\\fcharset0 Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 cSource\\f1  = \\f0 %cSource%\r\n\\par \r\n\\par tResult\\f1  = \\f0 %tResult%\r\n\\par \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

It would be interesting to see if this behaviour varies between Excel versions.

 

-- Terry, East Grinstead, UK

[Cory]

I don't have any ideas relation to MEP. I use a .NET framework class called Interop that is an API for Excel. It actually runs Excel and does everything a user can do but it's controlled grammatically. If there's a specific need you have I could write a simple little program you could run from MEP to do what you need in Excel. A common one I use with MEP simply exports the sheet's contents to a tab delimited file. Then I would iterate though the text file with ASCII File Process. It made dealing with Excel a lot simpler in MEP.

[terrypin]

Thanks Cory, good timing! This morning I was reading your article here

http://bluepointdesign.com/Macros/TabularData/Default.aspx

and plan to explore that clever approach, thanks.

 

I took a look at Interop but it looks pretty heavy without modern programminmg skills. I'm too impatient for results to spend a month learning it!

 

Meanwhile, if you know VBA, I'd much appreciate a few lines of code to copy from say A1 on Source.xlsb to B2 on Target.xlsb. I have about 40 such operations, and my current grunt method takes 2 minutes per worksheet! So for a hundred or so worksheets....

--

Terry, East Grinstead, UK

[Cory]

I wasn't suggesting you learn to use Interop. I was suggesting you describe what you need and I make a simple program to do it for you in Interop. It might be good to do what I was suggesting. Making a program that will simply dump a selected sheet to a tab file. Then it would be useful for all.

 

Click here for what looks like a good tutorial for moving cell values in VBA. 3 methods.

[terrypin]

Thanks. That tutorial's example was just what I was looking for. I reckon I can incorporate its simple VBA code into my more complex MX Pro maco.

One important aspect I'll consider next is the smartest way to use my 40-line 'From here, to here table', which looks like this

FROM cell in VARYING source worksheet	TO column of specified row in FIXED target worksheet
-------------------------------------	----------------------------------------------------
B5										C
B10										J
.										.
.										.
F17										AF
F18										AG

So any thoughts you or anyone have on this would be appreciated please.

 

--
Terry, East Grinstead, UK

[terrypin]

With a lot of help in an Excel VBA forum I now have a VBA macro working to do this. My own kludgy MX Pro macro was taking nearly 3 minutes (not 2). The VBA macro takes a fraction of a second!

So the 'tabulated data' approach is on my back burner for the time being. But, with curiosity and learning as motivators, I do hope to try working out how to adapt it to this copying task at some time.

In case it helps anyone ending up here with a similar requirement, I've posted my VBA macro here

Terry, East Grinstead, UK

[Cory]

I'm not familiar with how to do that in VBA. I only know how to do those kind of things in Interop. Sorry. One nice thing about Interop is it's intuitive as you're essentially using the UI like the user. So you do actions just like the user does. If you run into something in VBA you want help with please feel free to contact me here or by email. I'm competent in VB.NET but VBA is more like the old VB which takes me some time to adjust to. Little thinks like you must declare variables and then assign a value where in VB.NET you can do it in one step. Another reason I don't like VBA is it must be run in macros. And these days there is so much security that disables macros by default. What's more managing exactly where these macros are and getting them into all your spreadsheets is a pain.