Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Saving an Array (Excel 2000)

    Hans,
    Poking around in the references you offered, I've started to wonder - could you save an array? Without writing the contents of the array into a range?
    Once again, I'm trying to defy the size of a sheet.
    I can create an array with more columns than a sheet has.
    Or is there some way of taking multiple sheets, saving each sheet as a text file and "gluing" the info together? Not appending the information. It isn't more rows I need - but more columns.

    Just rambling....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving an Array (Excel 2000)

    Would it work to write the array values directly to a CSV (or other text file format) via code? Reading it back would be a bit of a pain. Would you be better off trying to whatever this is in Access?
    -John ... I float in liquid gardens
    UTC -7ąDS

  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

    Re: Saving an Array (Excel 2000)

    Where would you save it?
    You could sace it in several "continuous worksheets" that you would have to keep track of:
    Sheet 1 for Col 1 - 256, Sheet 2 for col 257-512, sheet 3 for 513 - 768, etc. 3 workshsheets would allow 65,536 rows and 768 columns if you created it 2 dimensionally.

    You could handle them with another dimension: so instead of 65536 rows x 768 col work with it as 65536 rows x 256 col x 3 sheets, and take the FULL Column range (1-768) and CONVERT with MOD and INT to get sheet and col
    Sheet# =INT((Col-1)/256)+1
    Col In sheet# = MOD(Col-1,256)+1

    You could use the same technique to getting more than 65536 rows by "adding" other sheets
    Steve

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Saving an Array (Excel 2000)

    write the array values directly to a CSV (or other text file format) via code
    ------------------------
    Yes that's exactly what I'm looking for. Do you think its' doable?

    I don't think an Access table will solve the problem.

    I want to manipulate these number in Excel and then save them in text file, because then another piece of specialty software is going to analyze them and this software only accepts text file formats.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Saving an Array (Excel 2000)

    I was considering working with a multidimensional array for just that purpose, but I have to admit that so far...
    it is out of my reach.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving an Array (Excel 2000)

    I think it's doable by you, but it would be a wicked stretch for my VBA skills! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Hypothetically I imagine using line numbers and line comma counts within the file as the two indices for writing and reading, and either using the Print #, Write # Input # commands, which I have tiny bit of experience with, or the more powerful (?) TextStream object commands, which I haven't used.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Saving an Array (Excel 2000)

    Well you've certainly given me something to chew on!

    Thanks for the pointers.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Saving an Array (Excel 2000)

    Catharine,
    Something like this should get you started:
    <pre>Sub TextStreamTest()
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim fs, f, ts, s
    Dim lngRow As Long, lngColumn As Long
    Dim strToWrite As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CreateTextFile "C:test1.txt" 'Create a file
    Set f = fs.GetFile("C:test1.txt")
    Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)
    For lngRow = 1 To 3
    strToWrite = ""
    For lngColumn = 1 To 300
    strToWrite = strToWrite & lngColumn * lngRow & ","
    Next lngColumn
    ts.WriteLine strToWrite
    Next lngRow
    ts.Close
    End Sub
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving an Array (Excel 2000)

    Or using normal VBA methods:

    Sub test()
    Dim icount1 As Integer
    Dim iCount2 As Integer
    Dim sstr As String
    Open "c:datatest.txt" For Output As #1
    For icount1 = 1 To 10
    sstr = "Line " & icount1 & ","
    For iCount2 = 1 To 10
    sstr = sstr & iCount2 & ","
    Next
    sstr = Left(sstr, Len(sstr) - 1)
    Print #1, sstr
    Next
    Close #1
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving an Array (Excel 2000)

    Gee, Rory and Jan Karel, while you don't have anything better to do, how about the code to read the array text file back into memory?
    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> <!rule=17>Rule 17<!/rule> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Saving an Array (Excel 2000)

    Thanks guys! You responded so quickly, I've only had an opportunity to read the help file - not actually try anything! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Saving an Array (Excel 2000)

    John,
    Between the ReadLine method and the Split function, I reckon that ought to be pretty easy! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Saving an Array (Excel 2000)

    Hey Rory
    It is amazing how these old posts never die!
    A couple of questions about the code you so thoughtfully posted over 2 years ago - I'm sure the answers will just be right at your fingertips <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

    1. You set the all constants - but didn't use them. Was this for clarity?
    2. Dim fs, f, ts, s - are these variants?
    3. I'm trying to work out if it is possible to append information, not at the end of the file; but rather at the end of each line. I'm going to be looping through a spreadsheet and want to build a txt version of the info - but by looping not dumping in one go. Your thoughts would be of interest to me.

    Regards
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving an Array (Excel 2000)

    Rory hasn't been online for a couple of days, so I'll attempt to reply.

    1. I assume that Rory included the constants for completeness sake, so that others reading this thread would have an idea how to modify the code if needed.

    2. They are defined as Variants here; he could have declared them as Object instead. If you set a reference to the Microsoft Scripting Runtime library, you could use specific types:

    Dim fs As Scripting.FileSystemObject, f As Scripting.File, ts As Scripting.TextStream

    (s is not used)

    3. The methods for manipulating text files don't provide for appending to individual lines. You'd have to open two TextStream objects, one for reading and one for writing. Read a line from the existing file, append some data, then write it to the other file. Repeat.

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Saving an Array (Excel 2000)

    Hi Hans
    I'm trying to compare speeds between two techniques:
    1) load the data into an array - and write the array to a new file. (All at once).

    2)loop through the spreadsheet, writing a column at a time to the new file.

    Right now 2 seems very slow because of opening and close of files I guess.

    Speed isn't the only issue - I'm back working with gigantic datasets and Excel starts to poop out at more than 230 Cols by 23235 Rows on multiple sheets <small> not to mention the amount of time it takes</small>. So I'm hoping to prune info out by placing it in another file after manipulating it.
    Any comments? other than ones about my sanity <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 2 12 LastLast

Posting Permissions

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