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

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........

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
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?


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...

Data Refresh Problem In Visual Basic Data Report

The problem I am having is that my data report always displays the old set of data. Meaning, when I run the data report for the first time it correctly displays the data from the database table. However, if I change the data in the database table and run the same report again (same debug session), its still shows the data from the first run and not the modified data.

Can someone please help me.

Thank you.

Refresh Of Data On Data Report From Access
Hello everybody
   i am a new person in VB and i have made one report by using access as back end. All the connectivity of data report is at design time. now i open the data report then it is not showing any data or output.

if i place a checkpoint on the particular showreport statement then it shows data. what will be the particular method so that at runtime the report will show the correct output

Waiting for reply

Thank you

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.

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

How To Refresh Data In VB
How to refresh data in Visual Basic

Help in this regard is appreciated

Refresh Data
Hi Everyone,

I have two forms. The first form called frmEmployees is used to display data for an employee. The second form called frmUpdateEmployees is used to update any and all information for a specific employee. A SQL UPDATE statement is used to update all of the records in the MS Access database. My question is this, once I have updated information for an employee how can I get the frmEmployees to reflect the changes without having to stop the program and then reload it again. I would appreciate any help. Thank you.

Refresh Data
OK I'm very stumped on this one. I'll post the code and then tell you whats going on.

'this section right here is supposed to delete everything that has a certain AccountID and LastName
'which it does this is not my problem. Im just trying to lay out some info.

reformatAccountID = Right(textPI(0), Len(textPI(0)) - 2)
SQL_Q_PERSON_DELETE = "DELETE * FROM tblPerson WHERE tblPerson.AccountID = " & reformatAccountID & _
                                        " and tblPerson.LastName = '" & textPI(2) & "';"
rstCommand.CommandText = SQL_Q_PERSON_DELETE
Call requeryRecordsets("all") 'same as rstPerson.requery. (rstPerson is the same as "Select * From tblPerson")

'Here's where it gets tricky. I'm trying to refresh the treeview to show that the person has been deleted. But when I press
'my button to execute the delete, you still see the person there as the last node. But if I press it again it will delete.

Private Sub refreshTreeView1()
  Dim bankroot As String
  Dim accountRoot As String
  Dim nodX As node
  Do While rstBank.EOF = False
    bankroot = rstBank(0)
    Set nodX = TreeView1.Nodes.Add(, , bankroot, rstBank(0))
    nodX.Expanded = True
  Do While rstAccount.EOF = False
    bankroot = rstAccount(0)
    accountRoot = "#" & str(rstAccount(1))
    Set nodX = TreeView1.Nodes.Add(bankroot, tvwChild, accountRoot, accountRoot)
    nodX.Expanded = True
  Do While rstPerson.EOF = False
      accountRoot = "#" & str(rstPerson(1))
      Set nodX = TreeView1.Nodes.Add(accountRoot, tvwChild, , rstPerson(3))

End Sub

'This is driving me banannas if anyone has any tips please help.

Stay out of trouble - Robocop

Refresh Data Through ADO
I am connected to an SQL database through ADO.
I would like to refresh the data on a datagrid as this happens. Updates are done by other users Only. The interface I am building is read only.

Please Help

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!!!

Data Environment Refresh
Is there any way to refresh a data environment recordset right before you run a data report?

Refresh Data Per ComboBox
I am using a Access 97 Database, and I want the data that appears on my form to refresh as per my selection on my combobox. I know that I need to do the Adodc1.Refresh, but how do I specif that I want it to refresh according to my combobox value? Please Help...Thanks

Can Refresh The Data Grid!
SConn declare as global. The problem here is after I select the data from the table and set it to the adodc then set the datagrid datasource=adodc.

Lastly I wan to display the selected data into the datagrid. Thus I refresh the datagrid, but no data is show in the data grid.

Can anyone out there help me please!

Dim rs As New ADODB.Recordset, sql As String

sql = "SELECT * " _
& " FROM invoice " _
& " WHERE iv_num BETWEEN '" _
& myIdFrom & "' AND '" & myIdTo & "'"
'Here we open the recordset.
rs.Open sql, sConn, adOpenKeyset, adLockOptimistic, adCmdText
'Here we populate the ADO Datacontrol with our filtered recordset
'Note the "Set .Recordset = rs" this is where we actually set the recordset
'for the datacontrol
With adoInvoice
.ConnectionString = sConn
Set .Recordset = rs
End With

If rs.RecordCount = 0 Then
MsgBox "No record found!"
Set datgridInv.DataSource = adoInvoice
End If

Set rs = Nothing

How Do I Refresh Data Grid
hi all
i have a form with datagrid on it
also update cmd
how do i get the updates to show on grid
without having to close form and reopen it
some thing about grid refresh
if thats what i need how do i do it and where does it go

Data Report Won't Refresh
I'm using a data report (at the moment without a DE) but however much I try I can't get the on-screen report to update. It still shows the original data rather than the most recent one. I have a list box of choices which is passed to a SELECT CASE, which produces an SQL query that populates both a MSHF and a report. The flexgrid updates no problem, so what am I doing wrong with the report? Does something need to be (re)closed, (re)opened, refreshed, requeried, set to nothing, or what?

Private Sub selcontype(choice As Integer)
Dim conn As ADODB.Connection, rs As ADODB.Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:cricketdb.mdb;"
Set rs = New ADODB.Recordset

Select Case choice
(example case - all identical apart from the query string)
Case 2
rs.Open "select * from contacts where [Position]='Secretary' ", conn, adOpenStatic, adLockOptimistic
Set grdSearches.DataSource = rs
Set rptConrep.DataSource = rs
End Select
End Sub

How To Refresh Data In TextBox?
How do i refresh data that's being read from .txt file in the textbox when i click a command button more than once?


Data Refresh Problem
Hi all

I have one MSHFlexGrid1 control, that display some fields from one mssql 2000 server. The MSHFlexGrid1 retrieve the data with datasource and datamember set in design mode. The connection with the server is made with DataEnvironment tool. I create a new command, I retrieve my fields and all shims ok. The problem starts when I insert a record in my database. I do it with a sql insert command but when I use MSHFlexGrid1.refresh the new record it doesn’t displayed.
Why that happens? I mast do something else?
I try and with DataGrid1 control but it happens the same.

Any help needed

Thank you.

Refresh Form With Data
I've got a form with several textboxes on it.
Each textbox displays a certain field from a database.

When I update the database (successfully I might add), I then run the following code. No matter what I do the data doesn't refresh in the textboxes so it appears to the user that the data is still there when it actually isn't.

I use the code below, but I can't get it to refresh.

So what are your thoughts on how to better refresh my form?

"RefreshData" contains code (GetTodaysData) that runs a new SQL query to hit the updated data. The record does get updated in the database, but won't disappear on the form.

VB Code:
Private Sub Form_Unload(Cancel As Integer)  frmMain.Enabled = True  frmMain.LVLevel1Cancellations.Refresh  frmMain.LVLevel2Cancellations.Refresh  frmMain.LVLevel4Cancellations.Refresh  frmMain.LVLevel5Cancellations.Refresh  frmMain.ShowCoverFrame  RefreshData    Unload frmDeleteClinicEnd Sub Private Sub RefreshData() GetCancellations GetTodaysData frmMain.MonthViewClinic.ValueEnd Sub

I can confirm that this is a refresh issue because I put a break point in my VB code and the database is updating correctly. I just need to refresh the textboxes on the form somehow.

How To Refresh Data In A Datagrid?
i have a datagrid connected to a datadesigner.
this datagrid is updatable but not insertable for reasons which are irrelevant.
how can i refresh the datagrids data when a manual insert into the database has been done ?
(manual insert means: connection.execute ("insert into .....")
i know i could use the requery method of the whole recordset but this would mean it would set the bookmark of the recordset to the first record and this is what i want to avoid....
anyone any ideas?

Data Report Refresh

I can't seem to get this report to refresh after adding a new record. Any ideas?

Private Sub MenuReport_Click()

If DataEnvironmentMyBSD.rsCommandBSD.State = adStateOpen Then
End If


End Sub



How Do I Refresh A Data Report?
I have a datareport that's connected to a certain table through a data environment. when I run the report the first time, everything is ok, but when I run it again, it shows the same info as from the first time I called it. Is there some way to refresh the connection of the dataenvironment, or can I just refresh the report?

Data Report Refresh...
The data report that I have works fine, but when I change the data to be displayed, it shows the exact same data that it did the first time...What can I do about this?

Data To DBgrid Refresh
can anybody assist me in how I can re-populate a DBgrid that is tied to a data control when I give the data control a new SELECT FROM statement. When the form loads it loads ALL RECORDS INTO THE DBgrid, what I want to do is change some parameters (dates) with combo boxes and re-populate the DBgrid. I have tried lots of different ways of achieving this but none of them will work. My code is available if required.

Data Report Does Not Refresh
Hi All,
I have designed a report using DataEnvironment and command. I built a query using various tables.
When a user selects from a list on the form, any value in a particular table is deleted and the selected value is stored, i.e. there is a maximum of one value in that table which links another table in a 1 to many relationship.
If for example X1 is selected, assuming the table is empty, nothing shows on the report, although X1 would be stored in the table.
When the application is closed and re-opened and X2 selected, information relating to X1 shows. Info on X1 will show so long as the application is running. Assume X9 is selected just before closing the application. When it is re-opened and X10 is selected, X9 shows on the report.
I have coded me.refresh in the Activate event but still . . .
I'm using sql server.
Any help would be appreciated.

How To Refresh Data Report?

I am a new user for Data report.
I'm giving 2 dates to report and show records between that period.
'--------------------code working for only 1st time
Legal1.Commands("AllDiary").CommandText = "SELECT * FROM TRANDIARY Where DIARYDATE >= #" & dtpFromDate.Value & "# And DIARYDATE <= #" & dtpToDate.Value & "# ORDER BY DIARYDATE,CASEID"
What goes wrong ?????

Data Report Does Not Refresh.
HI Experts,
i am using a data report - which gets data from a temporary table that gets populated with required data through code.i have different criterias to run the report.Problem is whichever criteria i select first - report runs fine and then fo rthe second criteria- it does not refresh the data unless i exit the application and run again, though the table shows latest updated data.So i don't understand where is the problem,i am using "Datareport.Refresh" methos too, i even tried unloading and loading it again before refreshing.please help

Need To Refresh Data Using XL 5.0 Dialog
I am creating a worksheet which contains data queried from our sequel database.  This data will be viewed by users who need everything spelled out very clearly so I would like to create a Dialog Caption Box where they can simply enter a customer ID which is my variable criteria in my query. Then in the same Dialog Caption below the editable field I would like to place a link for both "Print Preview" and "Print" to print the worksheet containing the Data.  Is this possible?  Note: I am new to using Dialog Caption and VBA so please be gentle.

Data Environment Refresh
I know this has been posted before but I don't really understand the answers!

I have created a Data Environment and linked Report. These work with an Access Database.
Neither DE nor Report have any code.

The report works fine when the program is first started but after more data is added to the
database file, it is not shown in the report.

Would somebody please give me a simple clue to the code that I need to add to make the data become refreshed.


How To Refresh Data Report
I am using inbuild data report in VB6.0

but once i see tht report it is visible as per first records r stored

but if i add new records the datareport doesn't shows the records

the same goes with when i modify any records

so plz help me


Data Refresh Problem
Hello All,
I am connecting to an access database, then using the recordsource to set up the record set.
I have done this lots of times and have never had problems till now.

The time it takes to refresh is taking a long time (10 seconds).

Here is my Code:
Code:Data1.DatabaseName = App.Path & "HATSdatabase.mdb"
Data1.RecordSource = "SELECT CompanyID FROM RegistrationFeatures"

Debug.Print Data1.Recordset.RecordCount

While Data1.Recordset.RecordCount < 2

Debug.Print Data1.Recordset.RecordCount

The first debug.print returns a 1. The last debug.print returns 10 which is correct from my DB after
about 10 seconds.

2 questions. 1) Why would this simple db call take so long? and 2) Shouldn't the .refresh command pause the program until it is complete?

Other info:
This is the first call to the DB in the program.
Other calls to the DB seem to be working normally.

Edited by - dvmc on 12/28/2004 10:50:05 AM

How TO Refresh Data Report
when i run the data report for first time it shows the right data in the data base but when i add some records to the data base and then show the report i don't get the updated data base , the breviouse data will be shown ,,,, how to do that ,,,,

HOW Can We Refresh The DATA ENVIRONMENT ??? (plz Help Anyone)
How can I refresh the DataEnvironment Recordsets ??? thats my only question !!! How can I refresh my recorsets to include new fields that may have been add ??? Seems like no one knows ... Is it because it's impossible ??? Plz answer ... thanks ...

Data Grid Refresh
I have a data grid which uses a query in the data designer as a data source I can't
get the data grid to refresh after a delete or insert using grd.refresh or grd.clearfields grd.rebind.
The updates are reflected in the grid but rows are not insert or deleted in the grid only when a new connection is made?? Help! What am I missing here?

How To Refresh Data Report
Hi all,

I had created a report by using VB Data report with data environment. And the report show correctly every first time when I preview the report. However, when I update the data by inserted some new rows, the data report still display the old data. Can anyone kindly tell me how to refresh the data report so that it display the correct data.

Many thanks,

How To Refresh A Data Connection
I am using a Data Connection using "Microsoft OLE DB Provider for ODBC Drivers" and Data Source is "Text Files".

I am getting the report first time. When I try to generate the report again, I get Runtime Error 8577 - Data Provider failed while executing a Provider Command. If I refresh the Data Connection from the Project Window and run the report again, I get the report with old data.

My code is as follows :

Your help will be highly appreciated.

Refresh Form With New Data
I have a form that prints infomration on label captions from intormation stored in the registry. I have a second options form that changes the information in the registry. How can I change the information on the original form with out bringing up a second instance of that form?

How Refresh Data In .rpt Files
I created .rpt files. Next I call report in VB.
But new records (after, when I created file) are invisible.
How can I refresh data in this report (use control Crystal Report)?


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

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

Copyright © 2005-08, All rights reserved