what i am trying to do is...... i have a website link which i kept open. For every after 5 mins my session logsout.
is there any alternate solution or a vba code which keep refreshing the page once in 4 mins.
I import a table from a website and its work fine.
My question is, Is there any macro or formula to highlight the cells after the update process (only if the value changed) ?
Also i need new entries to add automatically at the bottom(below the last row).
Sample table:
Data1
5
Data2
6
Data3
4
I have two different worksheet that if data is changed then the user needs to go to the company's website to update information there as well.
Can a macro look at a range of cells and if a change is made then a message box would pop up and if the user said yes then they would be directed to the company's website.
I am looking for a macro that can click on a link within a website. This link produces a print window. Below is some similar code that I have used before but this link does not have an ID so I am not sure what to do. I am also posting the HTML Code and a jpeg of the HTML code.
[Code] .....
HTML Code:
<TR>
<TD height=24 noWrap>
<IMG alt="" src="/SharedFilesCB/LeftMenuControl/Images/spacer.gif" width=2 height=1>
<A onclick="if (bIsFrameLoaded) PrintReportClick(); return false;" onmouseover="status='';return true;" class=leftMenu href="">print reports</A></TD></TR>
F12_2014-05-15_17-22-08.jpg
Where it is bolded I am trying to replace that with a cell reference such as A1 where I can input a website in that cell rather than edit the VBA code everytime
Here is the code:
Sub URL_Get_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://quote.money.cnn.com/quote/quote?symbols=msft", _
[Code]....
My macro appears to be refreshing the data from the queries on my supporting worksheets at the wrong time. It apparently doesn't go in the order it appears in my VBA code (and if anyone can explain that I'd love to know what's going on there).
Anyway, all I want to have happen is for all the queries to refresh, and THEN for the rest of my macro to run. So for example, if the user changes the job number in cell A1, or the Date in cell A2, I want the queries to refresh for this new information, and when that is done I want the rest of the macro to run.
I found the following code, but I don't understand enough about VBA to know where exactly it goes. Could someone please walk me through this? I tried putting in Sheet 1 under Microsoft Excel Objects, and I tried putting it in a Module, but neither worked. I'm sure it's some combo of both, but I can't figure it out.
I want to put a macro in that will refresh the workbook before this next part of code is run:
[Code] .....
I tried putting this in before:
[Code] ....
But it isn't refreshing the workbook before the next part is run
I am writing a routine to retrieve multiple stock quotes by looping through a bunch of sheets and refreshing a bunch of querytables that access the web. My data source (Livecharts) is often clogged up and queries will fail or get lost in space so after initiating the queries I wait for a while using a waitable and then check to see if each query has completed or is still refreshing. If they are still refreshing I cancel them and refresh again. Or at least that was the plan. What I have discovered is that as long as the vba code is running the results from the queries do not come back into excel. As soon as the code is exited or I go into break mode in the debugger the queries complete.
Is there some way to get the queries to refresh during code execution? Here is a code snippet that shows what I am trying. I have defined a Class Module defining an "EventfulQTable" and it's associated methods and properties so I can have a QueryTable with events. The sub Wait uses the windows API SetWaiTable command to pause for a specified period of time. It includes a DoEvents command which I thought would allow the returning queries back into Excel but no such luck.
Sub Refresh_Queries()
Dim SheetNum As Integer, ListSheet As Integer
Dim eqtQT As New EventfulQTable
Dim QueriesDone As Boolean
SheetNum = 1
ListSheet = 12
QueriesDone = False
On Error Goto QueryError
In cell B9 I have a simple Time formula which can be refreshed manually through F9.
Would like a code that automatically refreshes the time every 30 seconds, if that is possible.
I have written some simple VBA code so that on Sheet 4 I have a drop down list box to choose the month.
When the month is chosen by the user it moves to the active cell and changes the dates, as well as dates in some other columns (I used activecell.offset ....).
This code runs perfectly fine when I haven't refreshed the pivot tables in the workbook. But straight after a refresh, if I try and change the month it gives a run time error and gets stuck at the first instruction:
Sub cboMonth_Change()
Sheet4.Range("C2").Activate
I have 2 drop downs that when changed, auto refresh all pivot tables. My problem is getting the columns to auto-adjust based on the refresh or change of the data in the pivot table. Listed below the code I have thus far for the auto-refresh on pivot tables. How to auto expand all columns simultaneously.
The pivot tables are based off of tables on a different sheet(TOS Tables). So the code listed below is in the table sheet, not the pivot table sheet (TOS Customer Level). So followup question will be, which sheet to put the auto-expand columns code?
VB:
Private Sub Worksheet_Calculate()
'If data on this worksheet changes, refresh the pivot table
Sheets("TOS Customer Level").PivotTables("PivotTable2").RefreshTable
Sheets("TOS Customer Level").PivotTables("PivotTable5").RefreshTable
End Sub
I have some VBA code which hides columns based on a cell value. The cell value changes according to which option button is selected. The code works but not when the button is selected and the cell value changes. It is necessary to click elsewhere in the sheet or press Enter to get the columns to hide. I want it to do it automatically as a user wouldn't know to click elsewhere.
The working code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N5").Value = 2 Then
Columns("O:R").EntireColumn.Hidden = True
Else
Columns("O:R").EntireColumn.Hidden = False
End If
End Sub
N5 is the linked cell for the option button.
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
How to create a vba code that would emulate the following:
=$F$8
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
I have a workbook with daily sales information for each week in a period (4,5 or 6 weeks) depending on the fiscal year calendar. I have a dashboard to select the weekending date in cell D7. I used mscal 7.exe to embed the calendar. I need the date to remain the same, but want the calendar to show the current date. Cell H5 shows the current period;i.e. 1,2,3,4 etc. Cell H7 shows the week number of the period (1,2,3,4,5 and 6 for December).
I need vba code to showthe appropriate week's daily sales worksheet based on the info in cell H7 on the dashboard. The week number on the dashboard page is obtained fromthe calendar worksheet with a vlookup formula. These sheets are named "week 1, week 2, week 3, week 4, week 5 and week 6". I need to assign this code to a shape with the caption Create New Week.
In the Excel file I am trying to create I have a code that is sending an email notification that a file is ready for review. In that notification I want to insert a link to the file the email is referring to. No files are attached to the email.
Anyway, the code I am using is not pasting the link properly. This is what is pasted into the email body:
file://C:Test4DarrenRRR09-0001 DARREN CORP.xls (this link does not work)
The link should be appearing something like:
file://C:Test4DarrenRRR09-0001 DARREN CORP.xls
I can't seem to alter the code to do what I want. When I hover over the filename while stepping through my code everything look correct so I think it must be the line of code below that requires altering....
I have built a userform and want the text boxes to access information from a worksheet when I click on the button that displays the userform.
View 3 Replies View RelatedI am trying to write a code in VBA to collect data from several weekly workbooks, and list them up in a monthly workbook.
These weekly workbooks change names depending on the week, for example, we have:
Report week 1.xls
Report week 2.xls
Report week 3.xls
Report week 4.xls
Each report has the same layout, so the data I want will be in the same cells. However, as the report names change as the weeks in the year go on, I can't write the code for weeks 1-4 as they will not work with week 5 onwards!
I have found out how to chose a file of my choice using:
Sub GetImportFileName()
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
FInfo = "All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 5 ......................
I've got a spreadsheet with a few queries to extract data into my sheet. Whenever I open the spreadsheet, I get a "query refresh" dialog box, asking if I want to "Enable automatic refresh". I keep clicking on the "Enable" button but I have to keep answering the question for each open. Isn't there a way to set "yean - ok - refresh the data" so that I don't have to keep saying "yes"?
View 2 Replies View RelatedI have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
Will someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies View RelatedI have a spread I use daily where I need to go to a series of links on a site and extract data- all that is programmed. But it's a site that requires I first be logged into my account.
Code:
Sub AcctLogin()
Dim a As String
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
[Code] ....
The above code works, but it opens up an IE window. I'd just like it to log in in the background so I don't have to deal with a new window..
I am desperately trying to get full postcode lists from two websites: [URL] .... and [URL] ........
The latter of the two is harder to get (I'm told) so it could also be available at: [URL] .... by selecting only the "Collect+" stores.
I am using VBA to open an IE page and try to get some info however i cant seem to grab it
This is the code i have..
[Code].....
I have inspected the code from the website and it is this
[Code] ........
The problem is i dont know how to get the info in the "Metaname, ICBM".
I am looking to pull information from certain websites and put them in excel. I'm not quite sure where to start. I have tried the search option but it is not returning anything.
View 3 Replies View RelatedI am wanting to be able to lauch www.fafsa.gov from within Excel. In other words I want to be able to put a button on screen and when the user clicks on the button it will record some statistical data and then lauch the website. I know how to do everything except lauch the website. Can you lauch a website using code from within Excel.
View 4 Replies View RelatedI have as list of company registration numbers and would like you use code to input them into the companies house website - Failure Page
Comany Reg No example - 03292899
In order to get the date of the last accounts.
The problem is then when you submit on the site i cant see how it passes the company reg number through to load the next page. If I can get to the page then i have code to get what i need from the page but i cant find a whay to get the to page that i want.
how to use the example reg number to access the companies house page for this company.
I am trying to bring in a web page into excel but when it brings it in it misses the little football .gif. My code is like this if I don't get this gif its another bunch of work to read through the data.
Sub Get_playbyplay()
Dim cur_year As Integer
Dim game_url As String
Dim cur_row As Integer
Dim site_url As String
Dim paste_row As Long
Dim Heading As String
Dim q1_row As Long
Dim find_last As Long
Dim space_count As Integer
Dim prev_year As Integer
'Main loop each loop one season
I am trying to extract data from a website:
[URL] .....
I looked at the source code of the website and realized that if you notice (above) that the variables listed in the link (i.e year, month, day) are exactly what i need to change in order to get the data for a specific date. how can I accomplish this using VBA. so say I have in on an excel sheet year in column A, month in column B, and days in column C (time interval is constant so we don't have to worry about stime and etime). and i run the macro and it loops through each row taking year,month,day for all rows and saving the data as .csv or xls files?
