Can't autofix Excel cols after Refresh

[Ed White]

I'm using VSTO (Visual Studio 2008) with Excel 2007. In a spreadsheet in
Excel, I have a couple Data Tables with data imported from SQL Server
(Data->From Other Sources-->From SQL Server). I've written a sub that
Refreshes the data tables, and at the sub, it tries to AutoFit the columns.

Globals.ThisWorkbook.RefreshAll()
Me.Columns("A:BZ").EntireColumn.AutoFit()
My.Computer.Audio.Play(MediaPath & "notify.wav",AudioPlayMode.WaitToComplete)

It needs to resize the columns because one data table is below the other
(i.e. in the same Excel columns), and when the second one refreshes and
resizes its columns, it sometimes makes the columns too narrow for the first
table.

The AutoFit in the code above is ineffective. Actually, it is executed (and
does temporarily change the column widths), but then after the sub finishes,
Excel reformats the spreadsheet, and then the columns are not the correct
size. You can see and hear this when you run the above sub: the data is
Refreshed, the screen columns are adjusted, you hear the notify.wav sound,
and then Excel reformats the spreadsheet.

How can I get the columns to autofit in my VSTO sub?

 

[\Ji Zhou [MSFT]\]

Hello Ed,

Based on my understanding, the issue is when we call the AutoFit() function
after RefreshAll(), the column width is firstly adjusted, but the Excel
will reformat the spreadsheet immediately. Is my understanding right? If
not, please feel free to correct me!

My best guess for this problem is that it results from the "Enable
background refresh" option. To avoid this issue, I would like to suggest
you disable the "Enable background refresh" option for each connection
property. To disable the option for a connection,

1.We can navigate to the ribbon's Data tab, click the Connections button in
the Connections group, and then the Workbook connections dialog pops up.

2.We select a connection in the dialog and click the Properties button.

3.In the Connection Properties dialog, we can see the check box "Enable
background refresh" and uncheck it.

When this option is checked, the external data refreshing task will be
performed in another thread. While this option is unchecked, the external
data refreshing will run in the Excel main UI thread.

Actually, from my test, another important point in this issue is that the
Workbook.RefreshAll() function will not only update the data, but also
format the column width by its logical. Consequently, when the option
"Enable background refresh" is checked, things may happen in the following
sequence when we call our above code snippet.

1.The main UI thread(we call thread 1) runs our code to
Globals.ThisWorkbook.RefreshAll().

2.Since the option "Enable background refresh" is checked, the RefreshAll()
function will cause a new thread(we call thread 2) created to perform the
updating data and UI task.

3.Thread 2 gets to executed and update some of the data and UI.

4.Thread 1 gets to executed and run to the line
Me.Columns("A:BZ").EntireColumn.AutoFit(). It set the column width, but at
this time, the thread 2 may still not finish.

5.Thread 2 gets to executed, the table data is updated and Excel
spreadsheet UI is reformatted again.

Would you like to disable the "Enable background refresh" option and test
again on your side. Hope this helps to resolve the issue. If you have any
future questions or concerns, please feel free to let me know and I will
try my best to provide future help! Look forward to your reply.

Have a nice day, ED!

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ed White]

Hi Ji,

Yes, you understood my question correctly, and your suggestion appears to
work. Thanks for discussing the different threads--that is useful to know.

Another approach to fixing this problem that I discovered is: select
Data-->Properties, and unclick 'Adjust column width' for each table.

Thanks for your help.
--
Ed

Hello Ed,

Based on my understanding, the issue is when we call the AutoFit() function
after RefreshAll(), the column width is firstly adjusted, but the Excel
will reformat the spreadsheet immediately. Is my understanding right? If
not, please feel free to correct me!

My best guess for this problem is that it results from the "Enable
background refresh" option. To avoid this issue, I would like to suggest
you disable the "Enable background refresh" option for each connection
property. To disable the option for a connection,

1.We can navigate to the ribbon's Data tab, click the Connections button in
the Connections group, and then the Workbook connections dialog pops up.

2.We select a connection in the dialog and click the Properties button.

3.In the Connection Properties dialog, we can see the check box "Enable
background refresh" and uncheck it.

When this option is checked, the external data refreshing task will be
performed in another thread. While this option is unchecked, the external
data refreshing will run in the Excel main UI thread.

Actually, from my test, another important point in this issue is that the
Workbook.RefreshAll() function will not only update the data, but also
format the column width by its logical. Consequently, when the option
"Enable background refresh" is checked, things may happen in the following
sequence when we call our above code snippet.

1.The main UI thread(we call thread 1) runs our code to
Globals.ThisWorkbook.RefreshAll().

2.Since the option "Enable background refresh" is checked, the RefreshAll()
function will cause a new thread(we call thread 2) created to perform the
updating data and UI task.

3.Thread 2 gets to executed and update some of the data and UI.

4.Thread 1 gets to executed and run to the line
Me.Columns("A:BZ").EntireColumn.AutoFit(). It set the column width, but at
this time, the thread 2 may still not finish.

5.Thread 2 gets to executed, the table data is updated and Excel
spreadsheet UI is reformatted again.

Would you like to disable the "Enable background refresh" option and test
again on your side. Hope this helps to resolve the issue. If you have any
future questions or concerns, please feel free to let me know and I will
try my best to provide future help! Look forward to your reply.

Have a nice day, ED!

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ed White]

If I have some tables and some pivot tables together on a spreadsheet, and my
VS code executes RefreshAll, will the pivot tables run on a different thread
similar to what you described above, and is there a way to stop it?
--
Ed

Hello Ed,

Based on my understanding, the issue is when we call the AutoFit() function
after RefreshAll(), the column width is firstly adjusted, but the Excel
will reformat the spreadsheet immediately. Is my understanding right? If
not, please feel free to correct me!

My best guess for this problem is that it results from the "Enable
background refresh" option. To avoid this issue, I would like to suggest
you disable the "Enable background refresh" option for each connection
property. To disable the option for a connection,

1.We can navigate to the ribbon's Data tab, click the Connections button in
the Connections group, and then the Workbook connections dialog pops up.

2.We select a connection in the dialog and click the Properties button.

3.In the Connection Properties dialog, we can see the check box "Enable
background refresh" and uncheck it.

When this option is checked, the external data refreshing task will be
performed in another thread. While this option is unchecked, the external
data refreshing will run in the Excel main UI thread.

Actually, from my test, another important point in this issue is that the
Workbook.RefreshAll() function will not only update the data, but also
format the column width by its logical. Consequently, when the option
"Enable background refresh" is checked, things may happen in the following
sequence when we call our above code snippet.

1.The main UI thread(we call thread 1) runs our code to
Globals.ThisWorkbook.RefreshAll().

2.Since the option "Enable background refresh" is checked, the RefreshAll()
function will cause a new thread(we call thread 2) created to perform the
updating data and UI task.

3.Thread 2 gets to executed and update some of the data and UI.

4.Thread 1 gets to executed and run to the line
Me.Columns("A:BZ").EntireColumn.AutoFit(). It set the column width, but at
this time, the thread 2 may still not finish.

5.Thread 2 gets to executed, the table data is updated and Excel
spreadsheet UI is reformatted again.

Would you like to disable the "Enable background refresh" option and test
again on your side. Hope this helps to resolve the issue. If you have any
future questions or concerns, please feel free to let me know and I will
try my best to provide future help! Look forward to your reply.

Have a nice day, ED!

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[\Ji Zhou [MSFT]\]

Hello Ed White,

Yes, the background refresh option is also available for the Excel pivot
table. So, our previous discussion is still true for the pivot table cases
if the pivot table use the external data as source. To stop it, we need to
uncheck the "Enable background refresh" option. We can do it manually or by
codes.

*Manually set the option,

1.Select the target pivot table,

2.Navigate to the Data ribbon and click the properties button in the
Connections group.

3.In the popped up Connection Properties Dialog, uncheck the "Enable
background refresh". Press OK to return


*Programmatically set the option,

We can get the pivot cache from the pivot table by PivotTable.PivotCache()
function. With the pivot cache, we can set its BackgroundQuery to false.
Code looks like,

Excel.PivotTable pt = (this.Application.ActiveSheet as
Excel.Worksheet).PivotTables(1) as Excel.PivotTable;
pt.PivotCache().BackgroundQuery = false;


If you have any future questions or concerns, please update this thread and
I will try my best to provide future assistance.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ed White]

Thanks...that certain things are running are different threads is important
to know. I've had bugs in my code that, when I step through the code, never
appear, but appear at random when I run the code without stepping through it.
I now believe that this is because certains operations were being done on
different threads, and the code was not running sequentially as I had
expected.