Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I reset a spreadsheet to it's original starting point to make it reusable

    Hello again to all:
    This is a continuation of my first post of a few days ago, although a new problem. I am experiencing a problem that occurs when I try to "reuse" a spreadsheet. I have .txt files that consist of hundreds to thousands of rows of data with ten items in each row. I paste this data into my spreadsheet (cell A1). The data occupies whatever number of rows and all ten items of each row are contained in a single column (A). I then press a button that starts macros that parse the data into ten separate columns, deletes seven of the unwanted columns and the process continues as other macros utilize the data as needed and everything works fine the first time. The problem occurs when I try to reuse the spreadsheet. When I delete the old data and paste in the new .txt data, it is automatically parsed into ten columns. When I press the button to start the analysis process, it hits an error. It says that there is no data to parse (obviously) and everything grinds to a halt. I am hoping not to have to open a new (template) file every time I go on to the next set of data. It would be so much easier to rename, save, delete data and reuse the spreadsheet already open on the screen over and over. I have been unable to find any posts similar to mine that might be helpful.

  2. #2
    Star Lounger Wiley's Avatar
    Join Date
    Mar 2013
    Location
    St. Louis, MO
    Posts
    72
    Thanks
    1
    Thanked 8 Times in 8 Posts
    I am hoping not to have to open a new (template) file every time I go on to the next set of data. It would be so much easier to rename, save, delete data and reuse the spreadsheet already open on the screen over and over.
    i think that when you try to "reuse" the spreadsheet your problem is that you 'deleted' the data that you also deleted your formulas.. you analysis process can't find the data it is looking for to finish the process.
    i think your solution is the thing that you don't want to do... open a new spreadsheet each time.
    or paste your data, save as a new spreadsheet, work with it and when done...
    go back to your first spreadsheet (where you pasted the data and hit the undo button) that will remove the data that you pasted and you'll go back to before you put the data in and then you can paste the new data and do it again...
    i think that might work for you.
    Last edited by RetiredGeek; 2013-10-24 at 17:37.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You may want to right after opening the file, and before it is used, save as with a new name and then work with the copy. This preserves the original.

    What is your code for opening the text file? Once you set the parsing options, they are persistent for that session of excel, so you might want to explicitly set the options you want when you import, for example with the Workbooks.OpenText method with the desired type and delimiters set (see: http://msdn.microsoft.com/en-us/libr.../ff837097.aspx )

    Steve

  4. #4
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,407
    Thanks
    447
    Thanked 405 Times in 377 Posts
    Kenzer: you're doing this the hard way. There is a much easier way -- get Excel to split the data into columns before it goes into the spreadsheet.

    I'll explain how this works in Excel 2007, because that's the version of Excel that I have. I'm sure that other versions of Excel are about the same.

    I assume that there is a pattern here, such as space only between the columns, and not in a column. For example, something like this:
    1.25 2.25 3.00 4.66 2.77 etc

    In this example, we know for sure that anytime there is a space, a new column starts right after the space.
    We also observe that each column is exactly 4 characters wide (including the decimal point).

    You could use either of those facts to get Excel to split the data into multiple columns, IF every single row of data was that way.

    Here's another possibility:
    1.25,27.25,3.00,4.66,2.77 etc

    In this example, there is a comma between each column; however, we no longer have a guarantee of each column being four characters wide.
    If we KNOW that there is a comma between each column on EVERY ROW, then we can get Excel to split the columns based on where the commas are.

    I will use the example of a comma ALWAYS being between each column. (In Excel-speak, the comma is the "delimiter".)

    Run Excel. Click the "globe", then "Open" (to open your .txt file).
    In the bottom right of the files window, you will need to choose "All Files", so that it will show you the .txt files in the list.

    Find the file, highlight it, and then click Open.
    The file will show up, but in a "text import" window.
    Here you will need to select Delimited or Fixed Width. Since our example has a comma between each column, we will select Delimited. Then click Next.

    Now you will select the type of delimiter -- make sure that only Comma is checked.
    Click Next if you want to look everything over before proceeding; click Finish if you know that all is ready to go.

    You should now have an Excel spreadsheet open with all of your data split into separate columns.

  5. #5
    New Lounger
    Join Date
    Oct 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Wiley, sdckapr, mrjimphelphs for responding. Steve (sdckapr) you hit it on the head "they are persistent for that session of excel" since the excel session is not closed. I would like to be able to use the same already open excel file again. What I am trying to do is: open the file the first time, process and analyze the data, save it with a new name, delete/clear out the just analyzed data, paste in the new data, process and analyze that data, save it and continue this process over and over. What I have been trying to do is find some code that I could call after saving the just completed analysis that would reset everything, as if the program were starting for the first time. I have codes that parse the .txt data and do the anlysis. I have, in fact, a number of macros that are called out one after another. This automates the analysis. As for the .txt file, that data is simply copied, pasted into excel and then gets parsed as part of the analysis process. Steve, you provided me with code that scales all the graphs with the same scale based on the data and I thanked you. Unfortunately, I did not realize that I should have hit the thank you button to register it. Sorry about that. Anyway, any help by anyone is always appreciated.
    Gary

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Gary,

    I do something similar with my cell phone analysis workbook. Here's how I clear out the raw data.
    Code:
    Sub ClearRaw()
    
        Sheets("JanetRaw").Cells.ClearContents
        Sheets("BruceRaw").Cells.ClearContents
        Sheets("JanetRawData").Cells.ClearContents
        Sheets("BruceRawData").Cells.ClearContents
        Sheets("Analysis").Select
        
    End Sub           'ClearRaw()
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Gary

    Perhaps you just need a small update to your code?

    Let's assume your process starts by pasting your text data to cell [A1] on sheet named [Import].
    You need to make sure there is no previous data on the [Import] sheet, so step 1 is to clear the [Import] sheet before pasting in the Text:
    Sheets("Import").Cells.Clear

    Now, if this is the first time of importing the text data in the current Excel session, the data will be in column [A] only.
    So cell [B1] on sheet [Import] will be empty.
    You can easily test for this in the vba code.
    If the cell [B1] is empty, then you continue with the process that parses the data into ten columns.
    If the cell [B1] is not empty, then this not the first time the process has run, and the data has already been parsed, so you skip the parsing process..

    Now continue your vba at the place where you delete seven of the unwanted columns
    etc etc.

    zeddy
    zeddy

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You need to be specific about what you want to reset, based on what you are changing. You are changing the text wizard import options, which excel remembers for that session. The only ways I know to reset them is to start a new excel session or open up a new text file using the default options (could be as simple as creating a temporary workbook and running the data / test-to-columns with the desired default parameters).

    As mentioned before, once the parameters are changed (in opentext routine or data/text-to-column), pasting new text uses the new settings, so your routines must either explicilty reset them, work with the new settings, or as I have recommended, explicitly define the paramters you want to use in the routine and not worry about whether the settings are the default or have been changed.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •