Posts by inbead

  • Pivot Table Formatting Lost

    The other threads with same titles are already expired and I can't post to them.


    Whenever I select Update, the formatting of the Pivot table is lost even when I select "Preserve formatting".


    In addition, the in the pivot table "field settings" options, the option "number" does not appear for many of the fields. This happens for lots of data columns where data is only numbers.


    Thanks

  • Macro still runs after spreadsheet is closed!

    Re: Macro still runs after spreadsheet is closed!


    Thanks. This is great


    Worked with a few changes:


    Code
    StartTime = Now + TimeValue("00:05:00")
    
Application.OnTime earliesttime:=StartTime, Procedure:="ButtonCode", schedule:=True



    I have also put the following code in "ThisWorkbook" sheet to have the macro load automatically when spreadsheet is opened:

    Code
    Private Sub Workbook_Open()
    Call ButtonCode
End Sub


    Not sure if it is needed or not!

  • Macro still runs after spreadsheet is closed!

    Re: Macro still runs after spreadsheet is closed!


    Quote from guygamer

    Hi,


    To stop the ontime method enter this code in a Auto_Close procedure

    Code
    Sub Auto_Close
Application.OnTime EarliestTime:=Now + TimeValue  ("00:05:00"), Procedure:="ButtonCode", Schedule:=False
End sub


    Guy


    I put the code as a separate macro. However, when I try to close the file, I get this error:


    Run-time error '1004':
    Method 'OnTime' of object'_Application' failed

  • Macro still runs after spreadsheet is closed!

    Hi, I have the following macro running after every 10 minutes in the background to update stock prices. However, after I close the spreadsheet the file open dialog box comes up and tries to open the file.


    How can I stop the macro execution after the file (spreadsheet) is closed?


    Thanks

  • Automated Stock Qoutes using Macro

    Re: Automated Stock Qoutes using Macro


    Quote from Robin1981

    Use the following connstring instead:


    Code
    connstring = "http://uk.old.finance.yahoo.com/d/quotes.csv?s=" & RIC & ".L" & "&f=a


    Where RIC is the instrument code, .L is the stock exchane (London) and 'a' is the ask price.


    How about using the US stock exchanges, NASDAQ, NYSE and Amex?

  • Automated Stock Qoutes using Macro

    I wanted to get the automated quotes in excel without using the webquery function. Basically the idea is to have only the price updated every few minutes.


    1. Is there any way to accomplish this using webquery. Webquery pulls in a lot of other junk that I don't really care about. I tried webquery with yahoo and it pulls in a lot of stuff (see attched spreadsheet). I only need price.


    2. I ended up developing / copying the following macro to update prices. However I need to make it automated so it looks for stock symbols anywhere in the spreadsheet and puts the price right next to the symbol in adjacent cell. (see attached.)


  • Hyperlink addresses keep changing to "my Documents"

    Hi, I have a spreadsheet in which I have hyperlinked individual cells to files on a network drive.


    When I click on the text in the cell it opens respective files on certain folders on the network drive.


    However, after I saved the file and go back to it after 2-3 days it looks like that all the hyperlinked cells have changed from pointing to the networked drive to "c:\documents and settings\username\my documents\....." The path after "my documents" is the same as before but the drive letter changed from "u:\" to "c:\documents and settings\username\my documents\....."


    This has happened about 4-5 times. Any ideas??


    Thanks in advance :yikes:

  • Automate searching messages in outlook

    I receive 100s of emails every day and would like to automate searching emails received within the last 7 days containing certain terms like "intel" received in a folder e.g. "bloomber".


    Can anyone help me out with a macro?


    Thanks

  • Issues using findb() function in a macro

    I am trying to use findb within a macro to search for some text. However, I am getting this error "Run time error '1004': Unable to get the FindB property of the Worksheet Function class"


    I am trying to find certain text "names" from a column of text "rawdata" and then extract the first 25 characters after the text.




    After I find the text using findb and mid I want to place the text next to the search term in names sheet and that is why I am using cells(i,2) and cells(i,3)

  • Macro to search text based on an array

    I am looking for a macro to search a column of text based on an array of names. Each cell in the column is basically a text paragraph.


    If the name is found in the paragraph, the name and the following 15 characters need to be copied from the cell into another worksheet.


    The array of names are fixed and are in a worksheet area. names are "adam, john, steve", etc. and are in range a1...a15 in worksheet called "names"


    data is in a table in worksheet called "rawdata" I am searching column f which has about 1170 rows for the text.


    Any help will be appreciated.

  • Scan Outlook emails for text and copy into xl

    Re: Scan Outlook emails for text and copy into xl


    This worked fine. I was also able to use outlook's filter feature to direct only emails with the companies I am interested in.


    I am still getting "out of memory" error when I import the subject of the email.

  • Scan Outlook emails for text and copy into xl

    Re: Scan Outlook emails for text and copy into xl


    This worked fine. I have added code for the copying of the text, but don't remember the format to search an array for the company. The logic is that based on the data read in for each email, the macro will try to match the name of the company to an array of 50 companies. If it finds a match it will copy the data to an existing database.


    However, whenever, i read the subject of the email in excel, i get the memory error!


    Here is my code as it looks now:


    [vba]Public Sub ReadEmails()
    ' remember to include a reference to Outlook library

    Dim olApp As Outlook.Application
    Dim olNamespace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem
    Dim lngRow As Long
    Dim mydate


    Sheets("quotes").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    mydate = ActiveCell.Value
    Sheets("rawdata").Select


    Set olApp = New Outlook.Application
    Set olNamespace = olApp.GetNamespace("MAPI")

    ' Set olFolder = olNamespace.GetDefaultFolder(olFolderInbox)
    Set olFolder = olNamespace.Folders("Personal Folders")
    Set olFolder = olNamespace.Folders("Bloomberg")
    Set olFolder = olFolder.Folders("Brokers")


    lngRow = 1
    For Each olMail In olFolder.Items
    If olMail.ReceivedTime < mydate Then


    With ActiveSheet
    .Cells(lngRow, 2) = olMail.SenderName
    .Cells(lngRow, 3) = olMail.Subject
    .Cells(lngRow, 1) = olMail.ReceivedTime
    'MsgBox "Microsoft Excel is currently using " & Application.MemoryUsed & " bytes"
    ' .Cells(lngRow, 4) = olMail.Body



    Range("A1:c1").Select
    Selection.Copy
    Sheets("quotes").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=-2
    ActiveCell.Offset(1, 0).Range("A1").Select

    ActiveSheet.Paste
    End With
    Else
    End If

    Next

    Set olMail = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
    End Sub[/vba]

  • Scan Outlook emails for text and copy into xl

    Re: Scan Outlook emails for text and copy into xl


    These emails are routed to one folder in my outlook based on sender's address. So all the emails with quotes stay in one sub folder among my personal folders. Here is an example (with names and prices changed of course) :nono: :


    Some are like this (Showing only the subject):
    "Microsoft trades


    101.50 - 103.50"


    Others:
    > Pets.com - 101 5/8 : 102 $2 x $2
    > ebay - 103 : 104 1/4 $2 x $2
    > yahoo - 99 7/8 : 100 1/4 $2 x $2
    > Insignia - 102 1/2 : 103 7/8 $2 x $2



    The ">" may or may not be there and some times ":" to separate bid /ask is replaced by "-". I know the company names for example "Pets.com" to search on.


    The space between the company name and the bid might be different.


    I was able to pull the entire text of the emails into excel using this code, but got memory error. What needs to be done is to read these emails one by one parse and copy the data into excel and then the next email. Having parameters for date received will also be helpful:


    [vba]Public Sub ReadEmails()
    ' remember to include a reference to Outlook library

    Dim olApp As Outlook.Application
    Dim olNamespace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem
    Dim lngRow As Long

    Set olApp = New Outlook.Application
    Set olNamespace = olApp.GetNamespace("MAPI")

    ' Set olFolder = olNamespace.GetDefaultFolder(olFolderInbox)

    Set olFolder = olNamespace.Folders("Bloomberg")
    Set olFolder = olFolder.Folders("Brokers")
    'Set olFolder = olFolder.Folders("Brokers")

    lngRow = 1
    For Each olMail In olFolder.Items
    With ActiveSheet
    .Cells(lngRow, 1) = olMail.SenderName
    .Cells(lngRow, 2) = olMail.Subject
    .Cells(lngRow, 3) = olMail.Body
    lngRow = lngRow + 1
    End With
    Next

    Set olMail = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
    End Sub[/vba]


    Thanks in advance

  • Scan Outlook emails for text and copy into xl

    Hi,


    I get more than 150 emails everyday with quotes for certain securities (these are not the same quotes as available to wall street).


    I would like to import these quotes from emails into excel and then graph these quotes by day.


    The quotes are in the emails usually with a set format name of the company followed by a bid "-" and then an offer.


    Usually the one email has several companies listed, one on each line of email:


    The data I need are:
    Sender name:
    Date:
    Company name:
    bid:
    offer:


    I know the names of the companies (about 50 names) I need to track so I guess that can be the search term.


    How can I accomplish this? I searched the board and saw some postings on reading emails into text, but not sure if I want to do this as I get out of memory error.


    Thanks