KnowBrainer Speech Recognition
Decrease font size
Increase font size
Topic Title: Excel command to auto-fit columns not working - invalid flags
Topic Summary:
Created On: 02/11/2022 11:06 AM
Status: Post and Reply
Linear : Threading : Single : Branch
 Excel command to auto-fit columns not working - invalid flags   - wristofdoom - 02/11/2022 11:06 AM  
 Excel command to auto-fit columns not working - invalid flags   - Alan Cantor - 02/11/2022 11:45 AM  
Keyword
 02/11/2022 11:06 AM
User is offline View Users Profile Print this message

Author Icon
wristofdoom
Top-Tier Member

Posts: 322
Joined: 09/03/2020

I created a command for Microsoft Excel (most recent version, Microsoft 365) based on a macro recording.


The command is select all cells (clicking the triangle in the upper left corner), and then apply "auto fit column"  ( Home > Format > auto fit column width )


In Excel, the macro generated by performing these actions with the mouse is:


    Cells.Select

    Selection.Columns.AutoFit


Seems straightforward enough. I put this into a command called "auto fit column":


Sub Main

    Cells.Select

    Selection.Columns.AutoFit

End Sub


I added the Excel document object library by pressing ALT+ENTER.


But when I execute the command, I get the following error message from Dragon:


"The macro contains the error:


MyCommand: "auto fit column"

line: 3 

position: 0

Description: (1004) Application specific error. - Invalid flags."


Any ideas?


 



-------------------------

Dragon Professional Individual v15.6. Windows 10. Knowbrainer 2017.

 02/11/2022 11:45 AM
User is offline View Users Profile Print this message


Alan Cantor
Top-Tier Member

Posts: 4313
Joined: 12/08/2007

Try this:

Sub Main
Dim objExcel As Excel.Application
Set objExcel = GetObject(, "Excel.Application")

objExcel.Selection.Columns.AutoFit

Set objExcel = Nothing
End Sub

Its main limitation is that that the width of the column is determined by the width of the focused cell (or the the range of selected cells).

A quick way to deal with this limitation is to select the entire column that you want auto-fitted.

Alternatively, you could send keystrokes to auto-fit a column. No need to set a Reference:

SendKeys "{F10}", True
SendKeys "h", True
SendKeys "o", True
SendKeys "i", True


The following version selects the entire column, changes the width of the column, and then (tries) to restore the cursor to the starting location:

Sub Main
SendSystemKeys "{Ctrl+space}"
SendKeys "{F10}", True
SendKeys "h", True
SendKeys "o", True
SendKeys "i", True
SendSystemKeys "{Up}{Down}"
End Sub
Statistics
32306 users are registered to the KnowBrainer Speech Recognition forum.
There are currently 0 users logged in.
The most users ever online was 12124 on 09/09/2020 at 04:59 AM.
There are currently 396 guests browsing this forum, which makes a total of 396 users using this forum.

FuseTalk Standard Edition v4.0 - © 1999-2022 FuseTalk™ Inc. All rights reserved.