Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Excel Refresh

VB6 - The first time I shell execute EXCEL from VB its fine, however, the next time, EXCEL won't refresh properly, it seems to replace the Rows/Cols grid with a screen shot of VB.

Any ideas.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Refresh Excel From VB
i have an excel file with some PivotTables in it. what i'm tring to do is to open it from stand alone vb6 application and refresh the data in it.
your suggestions are well come, please.

Smile, Shmulik. (-;

Incomplete Excel Refresh
Hi all,

Has anyone come across the problem of Excel not refreshing properly.

Here's the problem Iam working in VBA and this occurs on xl 2000 2002 2003

I have hidden the toolbars on start and they reset on close as below

Private Sub ShowCommandBarList(ByVal blnShowList As Boolean)
Dim cb As CommandBar
Application.ScreenUpdating = False

For Each cb In Application.CommandBars

cb.Enabled = blnShowList
Next cb
End Sub

I am using macros to move between sheets and zooming the window in each sheet to suit its contents

When I use ActiveWindow.Zoom and Application.ScreenUpdating = False / True during the switching of sheets or even on the same sheet Blank Gray bars / menus appear! on the left hand side and on the top (not on right or bottom)

Repainting does not work nor xlmin Excel and then xlmax

The only way I have to clearing them is to use a false blank menu as follows

Sub Setabrs()
Set clearmenubar = CommandBars.Add(Name:="clearmenu", _
Position:=msoBarLeft, _
clearmenubar.Protection = msoBarNoCustomize + msoBarNoMove
End Sub

Sub Clearbars()
Application.CommandBars("clearmenu").Enabled = True
Application.CommandBars("clearmenu").Visible = True
Application.CommandBars("clearmenu").Visible = False

This removes them but causes a flicker of grey bars

Has any one come across this? or any other work around?

As always any help much appreciated.


Vba Excel - Need To Refresh Explorer
I've got some vb/vba code in excel and it does a to a document created in a particular folder. However when debugging i think the file is not read as an error message says at this line in code that either the file is read only or open or not exist. When i refresh windows explorer i can see the new file has been created and then recontinue (f8) the debugged code the works.
Therefore i would like to code in VBA excel a refresh to a particular folder say G:/pc_matdb - how do i do this?how do i do this :cool:

Excel: How To Refresh A Cell
i have several cells that use a custom vba function. is there anyway that i can update/refresh these cells automatically on workbook change. the current code i have is as follows:


Private Sub Worksheet_Change(ByVal Target As Range)
If GetXLCol(Target.Column) = "G" Then
End If
End Sub

thanks for any replies

Refresh The Data In Excel
I need to refresh the data in sheet2,when i made the changes in sheet1. This data is not coming from recordset. The data in sheet1 is user data entry. I need to get that data evertime when users edits. How do i do that? Please........

Excel VBA Refresh Querytable Question
Hi all,

I realise that this sort of thing has been discussed in several threads, but I'm still a little confused about it so I'm seeking clarification.

When refreshing a querytable (which has the focus), the SQL database is very large and busy so the query can take a long time to complete. Will excel "time out" the query if it takes a certain amount of time, or is there anyway I can introduce some sort of argument so that it will? For example, if the querytable hasn't refreshed after 30 seconds then I would like it to stop.

Here's something close to what I'm using at the moment:

Dim wSt As Worksheet, odbc_conn As String, sql_query_string As String, BackgroundQuery as boolean
Application.DisplayAlerts = True

Set wSt = Workbooks("Opdata.xls").Worksheets("table")

odbc_conn = "ODBC;DSN=some_SQL_access;database=some_database"
sql_query_string = "select * from sometable where somecriteria = '1'"

Set oQt = wSt.QueryTables.Add(Connection:=odbc_conn, _
Destination:=wSt.Range("a1"), _

oQt.Refresh BackgroundQuery:=False

Application.OnTime Now + TimeSerial(0, 0, 1), "NextProcedure"


Automatic Refresh Of Excel Chart

I am scraping a terminal screen to produce a chart, and have managed to sucessfully produce my chart. My user requests the ability to have the chart dynamically refresh from the automatically updated terminal screen. (This would be similar to the real time stock ticker charts.)

Would you give me some guidance about how to either refresh the data (and the supporting chart), or how to determine that Excel is active, delete the data from Sheet1 and replace it with the new?

The terminal refresh is 2 seconds, but I expect that 3 or 4 seconds is acceptable.



Excel - Pivot Table Refresh?
I know how to refresh a Pivot Table in a macro but is there a way to script it so that it does them all at once or one at a time without having to create the pivot table refresh command for each one?

Refresh Links In An Excel Workbook Using VB

I have an Excel workbook with some worksheets, each of them contains data that comes from a query (which was written using MSQuery). The worksheets are linked to the query!

I need to write a VB program that opens the workbook and refreshes the links. I tried to do that in so many ways, but all of them failed. Even when the program finished to execute without run-time errors, when I opened the workbook with Excel, I found that the workbook was not upgraded at all!

So far I have tried to use "Workbook1.RefreshLinks" and "Workbook1.Worksheets(1).Selection.QueryTable.Refresh" with many more variations on them.

Does anyone know another way to do that?


Refresh Excel Form Controls Value
THE CASE: I have developed 3 entry forms using EXCEL; Form1 show the different possibily; filling Form2 controls I enter Form 3; Filling Form3 controls I've got a command button to save the whole data entry... it works right but...

THE PROBLEM: when I save the data set I come back to show Form1; If I want to show Form 2 again, the controls are already filled with the data I entered before.. How can i make an automatic refresh of the form (better, of the controls that are inserted in the form) without make lines for each control? Why Excel doesn't make you Close a form as it happens in Access?

Thank You for your help!!!

Refresh Label Caption In Form (Excel)
I have a form with a Text Label and a Button. By pressing the button a process starts running. I have a Variable into the process that changes continuously, lets say from 1 to "n".

I want to refresh the Text Label in the form with the value of the variable each time it changes.

I tried TextLabel.Refresh but this function seams to be not included in Excel Visual Basic.

My Question is, Is there any .refresh method for VB in Excel I can use to refresh the label in the form while the process is running?

I tried Form.Repaint and it works but it refreshes everything in the form and it looks wierd.

Create A MS Query From Within Excel And Refresh The Same Programmatically!
Hi Everybody

This is the requirement :-

Using the Data -> Import External Data -> New Database Query functionality in Excel, I have written a query (using MS Query) to extract some information from our our SQL Server based datawarehouse. The data is returned to the Excel spreadsheet and this spreadsheet is saved somewhere on the network.

Rather than extracting data directly from the cubes (I am not familiar with MDX at this stage to the extent to be able to easily extract the same data directly from the cubes), I am using SQL statements to extract data from the tables that sit behind these datawarehouse cubes.

I now have a report template document, which the user uses to generate the monthly report. However, before he/she is able to do update the report every month, this data needs to be refreshed with the latest data. I have a button on my report template document and what I want is that when the user clicks this button, the program should open spreadsheet containing the MS Query, update the data from the data source in the background (based on the user input of the latest period which the data needs to be updated for) and return the data to this spreadsheet. The program then would recreate the associated named ranges to work with the enlarged/altered data range so the formulas continue to work without having to alter them.

Any ideas/suggestions/pointers how do I get to refresh the query and alter the SQL within using VBA?

Best regards

Deepak Agarwal

Open Excel And Refresh Data From Vb Executable
i have a excel spreadsheet that i want to open and refresh data every day. I want to code this in VB so i can run a executable to do that for me. Please help? I am new to VB and have no ideas how to start this?


VB Macro For Excel Pivot Table Refresh
Has anyone written a macro in excel that will retain any changes you make in a pivot chart (legend key colors, etc)? I have a line graph that uses a pivot table as it's source. I want to change some of the colors for the lines on the graph, but it reverts back to the default colors when I refresh the pivot table.

The help menu brings up the following message pertaining to this:

Note When you refresh a PivotChart report or its associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), some chart formatting may be lost.

In another area it said you will have to write a macro to override this. How do I do that?

Very frustrated.

great_llama: Double-post. Thread locked and should be continued here.

Edited by - great_llama on 10/7/2005 10:16:49 AM

VB Macro For Excel Pivot Chart Refresh
Has anyone written a macro in excel that will retain any changes you make in a pivot chart (legend key colors, etc)? I have a line graph that uses a pivot table as it's source. I want to change some of the colors for the lines on the graph, but it reverts back to the default colors when I refresh the pivot table.

The help menu brings up the following message pertaining to this:

Note When you refresh a PivotChart report or its associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), some chart formatting may be lost.

In another area it said you will have to write a macro to override this. How do I do that?

Very frustrated.


Edited by - vickicarol on 10/7/2005 10:04:22 AM

VBA To Enter Oracle Database Password To Refresh Query In Excel
I'm working on automatically getting the results of an Access query to load into an Excel file.

I have set up the query in Access already, and loaded it into Micosoft Query in Excel.

So I have a macro which refreshes the queries when Excel is opened. The problem is that I am prompted for the password each time.

I went into the "Data Range Properties..." and selected "Remeber Password". This did not work...??? No idea why...

Is there some way I could use VBA to enter the password?

Here's the code I run:

HTML Code:
Private Sub Workbook_Open()
Call Refresh
End Sub

Sub Refresh()

Selection.QueryTable.Refresh BackgroundQuery:=True

Selection.QueryTable.Refresh BackgroundQuery:=True

Selection.QueryTable.Refresh BackgroundQuery:=True

'sort lookup table:
Selection.Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

'now refresh all pivot tables in workbook
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In Worksheets
For Each pt In ws.PivotTables
Next pt
Next ws

End Sub
Thanks for your help!

Automatic Refresh Of Excel 2000 Workbook With Out Pivot Tables.
automatic refresh of excel 2000 workbook with out pivot tables. ...?

Cannot Refresh Data Using Crystal Viewer Refresh Method
I am using crystal report 8.5 and Visual Basic 6.0

I get an error for this code.


error :

Method 'Refresh' of object 'IcrystalReportViewer' Failed

thank you ....

regards pragash...

VBA Excel: Runtime 1004 Error With "Refresh BackgroundQuery" In Web Query
Anyone seen this one before? I get the notirious runtime 1004 error at the following point in an Excel macro that retrieves stock data from Yahoo:

strurl = "" & Symbol & "&d=v2"
With Worksheets("Temp").QueryTables.Add( _
       Connection:="URL;" & strurl, Destination:=Worksheets("Temp").Cells(1, 1))
           .BackgroundQuery = True
           .TablesOnlyFromHTML = False
           .Refresh BackgroundQuery:=False ' <- Bombs at this line with
           .SaveData = True ' Run-time Error '1004':
   End With ' Application-defined or object-defined error

The weird part is, it only seems to happen on my machine (XP Home, Office 2000 SR-1 SP3 as noted above), and not on a variety of others running various
flavors of Excel, from 97 to XP, on environments ranging from Win2K to Mac SoftWindows!

I put the bombing code in a blank ss with nothing else. It still bombs on the Yahoo URL (I substituted the "symbol" var with hard-coded symbol e.g. QQQ for a complete URL string). It does NOT bomb on a URL where I know the data is small and static (the Yahoo page updates dynamically before being gotten, obviously).

When I try a similar web query hand entered in a blank Excel ss using the Yahoo URL (Data -> Get External Data -> New Web Query), 4 times out of 5 I get a dialog saying:

"Unable to open <URL>. The Internet site reports that a connection was established but the data is not available."

(where <URL> is the actual Yahoo URL in the VBA at top of this thread)

I wonder if this 1004 error in the VBA macro is being caused by the query returning empty when the connection returns no data? If so, why am I getting it and no one else?? Is this a known issue?

MSFlexGrid Refresh Causes My Comboboxes To Refresh???
What in the heck is happening?? On a form I have 2 combo boxes, the first for suppliers and the second for contacts. The contacts is filled from the database after the supplier is chosen. On my Flexgrid, I have several pop-up forms/controls. When I open them (For instance the DateTimePicker) and select the date from my pop-up control, my screen flickers and my contact is blank again. The supplier stays like it was. I can't exactly paste the code for this, since I am at about 5,000 lines and counting on this Application.

Com Refresh
Hi all,

If the com1 is blocked, instead of manually going thru the device manager to disable and enable the port ( inorder to free up the port). Is there a way where i can key in a code to tell , if in case of port opening error , do refresh the port and connect?


Refresh Help
Hi I'm new to this site so I would like to say hi to everyone. I'm having a little trouble I have a vb6 app that connects to my website and runs at startup but when it runs and the internet is not connected it can't load my site within the form. So I was wondering if anyone knows how to get it to autorefresh every 15mins. Thanks in advance Jack

p.s cool site

How do i get a another form to refresh, when i hit a button an another form, what i am trying to do is refresh the data in a combo box. when i add a new name to a listbox (database), the combo box takes the names from the listbox and puts it in the combo box, but the combo box doesn't have any of the new names in it until i close and re-open the program. Can i simple refresh the combo boxes by refrehing the form they are on or do i have to refresh each combo box ANY ANSWERS GREATLY APPRECIATED

After Refresh
I have an excel sheet im trying to change the code on. First a description of the sheet....

*Cell a1 has a drop down menu with product codes.
*When this cell changes 5 queries within the sheet automatically refresh. This is a setting in Excel.
*Finally I have code in Visual Basic that runs when cell A1 changes. This code looks at the cells the queries are in and hides the rows where these cells are empty.

However a problem I have, and ive seen a lot of people with this, is that the Hide rows code runs before the queries refresh with the result that rows with real data are still hidden!

I found the article: which describes how to use the AfterRefresh code but this has not worked for me. I have a theory this may be because there are 5 queries and the after refresh code runs after the first one refreshes but before the other 4! Is this theory sound?

Either way is there a change to the code at or other code I could use to acheive the result I want? Right now the solution i have settled on is to use the code in a function of its own and use Application.Ontime so that the code runs after a couple of seconds, giving the query time to run. However any delay, although rare, in accessing the Database would mean this code will run too soon.

Bad Refresh
I have the following problem:

In an application when you click "nieuw" (new)
you get the following screen:
when you pick something out the little window for example "voortijdige hervergunning"
and here my problem starts
but when I change to my desktop or another app and then return it shows correctly:
More screenshots when adding multiple:

there is something wrong with the refresh but I have no idea
anyone has suggestions?

background: The application allows users to log in to a database.The database update the ip address of the users log in.I am thinking of using the ip address to allow the users to communicate with one another. There is a field that indicate whether the user is log in to the database.

The problem: i have a listbox that shows the users log in the database.what i need is the listbox to refresh itself automatically so the the users knows who is currently online.What is the event i should use?


How Do You Refresh...
just a quicknote, i'm new to vb, but not to several other programming languages, so i should be able to catch on... anyway, i need to refresh a window every couple seconds, i'd appreciate some quick code for it

i'll try to be more specific (just incase). i'm putting together a chat bot, and i inserted text boxes in the window that hold some information that the bot needs to use once it connects. i've decided that by simply having it refresh every once in a while is the best solution, so that it finds the new data in that time. anyway, thx to anyone who helps me out.

Is there option to simply refresh data within mshflexgrid every time i update the recordset ?

My App Won't Refresh
hey, i've got a strange problem on my app, i'm moving and renaming files from a folder to another and it works fine but... it's supossed to close the progress form but it doesn't and it doesn't even change the values for the controls on that form. It just finishes the duty and stays there.... what is wrong???

by the way, here's my code:

Dim bar As Double
Form2.Show 1
Form2.ProgressBar1.Value = 0
Dim nFiles As Integer
For Each f1 In fc
nFiles = nFiles + 1
If nFiles = 0 Then nFiles = 1
bar = 100 / nFiles

Dim ext As String
Set f1 = Nothing
If Not fs.FolderExists(Dir1.Path & " emp") Then Call fs.CreateFolder(Dir1.Path & " emp")
For Each f1 In fc
Form2.Label2.Caption = f1.Name
ext = "." & Right$(f1.Name, 3)
f1.Move Dir1.Path & " emp"
If Not f1.Name = "file" & maxnumber & ext Then
f1.Name = "file" & maxnumber & ext
End If
f1.Move Dir2.Path & ""
maxnumber = maxnumber + 1
Form2.ProgressBar1.Value = Form2.ProgressBar1.Value + bar
Call fs.DeleteFolder(Dir1.Path & " emp")

Unload Form2

can u tell me what's wrong???

How To Refresh?
How do I refresh my text-boxes, ie: blank them?

Private Sub cmdRefresh_Click()


End Sub

..doesn't seem to work.

Sql Refresh?
I am using SQL to create a record set that populates a datagrid.

I have an Add button that Addnew to one of the table that the SQL statement uses. I did a refresh on my data control that my SQL statement is connected to and a refresh on my datagrid, but it doesn't reflect the add that I just made. If I leave the form and reload the form, the data grid is updated.

Is there another updat or refresh I need to do?

Dale S

Refresh Db
When I execute this statement:
cnnDB.Execute "CREATE TABLE tmpTable " & _
"(DateEff varchar not null)"

The table is created but I cannot see it until I press the F5 Key in Access. Is there a way to code this so I don't have to manually press F5 to refresh database?

Refresh The
I have a datadrid control in my application, but when I manually enter a record it does not appear at runtime. Please note that I do not allow to insert a record or delete from the datagrid so I can control the information that are entered in the database.
Can you please tell me a way to refresh the datagrid?
I have already used clearrecords and stuff but it didn't really work. Also note that I open my database adOpenStatic.
Any advise would be extremely helpful!!!

Hi, I have this form in access, it's linked to a table and it's working quite fine. Only thing is that when I enter a record in one of the textbox and I click on the navigation button to add another one, the value stays there. It's like everything is being refreshed but that one textbox. I should mention that the textbox has a vb procedure to give a result in another textbox.
Anyone knows why?

The file is included. The form in question is called "Time Billed"

Ado Refresh
Hi Folks.

I bound a table on a ADO Control. A Datagrid-Control is linked (DataSource property) with this Control. If i click on the left side of the Grid (the small row buttons), the Ado-Recordset-Datasetpointer points to the same dataset as selected in the Grid. But if i only select a field in the grid, it doesn't points to the correct Data. Is there a way to correct this? Or a way to prevent the user to click on fields?

thank you.

Refresh ?
What am i missing here, i cant get my mshflexgrid1= grid to refresh when i either add or delete item from DB form. It deletes them i just cant tell until i end and run again. Sorry for such a simple question.

Private Sub CmdADD_Click()
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsOwnerDesktopcdcollection.mdb;"
MyConn.CursorLocation = adUseServer
MyConn.Execute ("INSERT INTO Cd(artist,title,album,genre,length ) VALUES('Dylan Smith', '123 Fake Street', '(204) 888-8888','jeff','123')")

End Sub

Refresh Api?
Is there any api function to refresh desktop?
i'm working on some special effect desktop utility and when i change desktop i get stuff like this in attachment.

Refresh Help
Hi I'm new to this site so I would like to say hi to everyone. I'm having a little trouble I have a vb6 app that connects to my website and runs at startup but when it runs and the internet is not connected it can't load my site within the form. So I was wondering if anyone knows how to get it to autorefresh every 15mins. Thanks in advance Jack

p.s cool site

I'm writing a program and the program goes out and retrieves data from a web site. I want the data to be refreshed every 5 minutes automatically. i can;t seem to find any information on this.. can anyone help?

i have written a program that draws a pattern using a class. when the pattern is finished i'd like to be able to press a comand button that clears it so it can be drawn again.

RDO Refresh
Im trying to save to an access database using MSRDC controls. Im getting an error "Cannot refresh controls" and it stops at MSRDC1.Refresh. I've got a couple of combo boxes - Do combo boxes not work with MSRDC refreshing? (text boxes appear to be ok)

Private Sub CmdSave_Click()
Status = "Saving job"

If awb <> "" Then

strSQL = "select * from ACC where accNO = '" & ACC & "'"
MSRDC1.sql = strSQL
MSRDC1.LockType = rdConcurLock

A ReFresh
Is There A Statement that refresh your progoms

DIR Refresh
I am using the Dir command to create an array of all the files in a directory.

After reviewing all each file I remove it from the directory.

After the entire array is processed, I want to check for additional files that have been written to this directory by another application.

Occassionaly the Dir command is not "refreshing" and picking up the new files.

Any on have any suggestions to "refresh" so that the Dir command will find the new files.

I have a user control which has combo box. when i select in combo box , the control down doesnt get refreshed after i complte selecting from combo box.

Refresh Tab
I have a microsoft tab in my program and i would like to know how i would be able to have a button that would be able to refresh the tab. This is because i will have a button wich will change a variable that is used to render the information in the tab. The variable will be changed and how should i then "refresh" the tab with the new contents.

Another Way To Refresh
Need to refresh form. Another way besides Refresh.form1 pls. I know there is a simple way... redraw....? i dont know. HELP!!!

Refresh IN Asp
sorry for putting this in the wrong forum, but things dont get answered very fast in the asp forum.

does anyone know an asp code to refesh the page every second

i am new to asp

Refresh ??
Hello all,
I'm using crystal reports in my project. But when I navigate among the records and then click on print button, I'm not getting the report contents refreshed. Even the .Refresh property is also not working in this case.
Please tell me how can i solve this problem without clicking on the default refresh button everytime..
thanks in advance,

What This Refresh Mean?
Sometimes i got this error
Method REFRESH of Object 'Iadodc failed

what is this?

Copyright 2005-08, All rights reserved