Welcome Guest
Log In | Register )
You last visited December 5, 2016, 9:28 am
All times shown are
Eastern Time (GMT-5:00)

Excel Question

Topic closed. 32 replies. Last post 7 years ago by LottoChica23.

Page 3 of 3
PrintE-mailLink
LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
Tx
United States
Member #4570
May 4, 2004
5180 Posts
Offline
Posted: November 29, 2009, 12:05 pm - IP Logged

If you download the draws to a text file to your Desktop you can create a Macro that will import the text files into Excel

You will need to change the "YourName" in the macro to whatever your user name is for your computer.

I named my macro ImportDraws

 

Sub ImportDraws()

    ChDir "C:\Users\"YourName"\Desktop"
    Workbooks.OpenText Filename:="C:\Users\"YourName"\Desktop\Pick3_ASCII.doc-1.txt", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, _
        Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True


End Sub

 

Then in Excel select Tools > Macros > select ImportDraws macro the select > Run

It didn't work for me, some kind of compile error, I, of course don't need it anyway, but I tried it.

I used this:

Compaq_Owner

Sub ImportDraws()

    ChDir C:\Documents and Settings\Compaq_Owner\Desktop\
    Workbooks.OpenText Filename:= C:\Documents and Settings\Compaq_Owner\Desktop\Pick3_ASCII.doc-1.txt, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, _
        Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True


End Sub

BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

"Ten measures of beauty descended to the world, nine were taken by Jerusalem."

    LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
    Tx
    United States
    Member #4570
    May 4, 2004
    5180 Posts
    Offline
    Posted: November 29, 2009, 12:16 pm - IP Logged

    Maybe this will also help.

    Instructions for Importing Virginia Pick 3 Draws to Excel

    http://www.box.net/shared/y1f01c48xc

    I tried that and it is the best way to go, it is perfect fast and very easy!

    Thanks!

    -----------

    It is much better than the Macro and much less troublesome, no trouble at all.

    Trying the macro is prone to mistakes.

    --------------

    Of course, the search and replace way is very easy to understand and use, fast and produces a mistake free output also.

    BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

    "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

      LottoChica23's avatar - Aquarius
      Fughedaboutit (NY)
      United States
      Member #8160
      October 26, 2004
      6777 Posts
      Offline
      Posted: November 29, 2009, 12:40 pm - IP Logged

      Is there a way that I can download the pick 3, pick 4, cash 5, or mega millions in excel format. I am trying to learn how to make my own excel spreadsheets, but, when I download the numbers from the Virginia Lottery website they come in Word.

      I tried to change the download file type to .xls, but, then it is just a jumbled up mess. All of the data is compressed into a couple of cells.

      What I wanted to do was to try to get the numbers into excel and then 'make' a pivot table. Something I saw on Mr. Excel in YouTube.

      Any help would be greatly appreciated.

       

      Thanks,

      Sandra

      Hi Sandra,

      I just saw your question. The way I would do what you want to do is:

      1. Open the downloaded doc file in Word and save it as a txt file

      2. Open the newly saved txt file in Excel. Whe opening the txt file in Excel be sure to click drop-down arrow nex to "Files of type" and select "Text Files" you will then be able to see the text file that you saved.

      3. Select the desired txt file and click Open. You will get the Text Import Wizard.

      4. Choose "Delimited" --> Next

      5. Check Semicolon, Comma, Other (Put : in the other box)

      6. Click "Finish"


      You will then have raw data to work with any way you choose. When you save that data in the Excel sheet don't forget to save it as an xls format.


      HTH

      Matrix Chart Instructions--> http://www.lotterypost.com/thread/191818 

      check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

      You can judge the integrity of a man by the way he treats those that can do nothing for him...Thumbs Up