Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: TSTom or anyone, some help please :)

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I got some code from TSTom to export and import ranges from excel to a text file. This has been working great, but now its time to put the text files to use. Im trying to import the ranges from the text files in a directory to a single sheet to do Data analysis on it. I have most of the code, but i have a few problems. Here is the code first:

    Sub DataReport()
    With Application.FileSearch
    .LookIn = LookIn
    .SearchSubFolders = True
    .Filename = "*" & Criteria & "*.txt"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then

    For i = 1 To .FoundFiles.Count

    '################################################
    'restore from textfile Code
    Dim BackUpRange
    Dim CurrentBytePosition
    Dim FilePathAndName
    Dim FileNum
    Dim DataSheet As Worksheet

    FileNum = FreeFile
    CurrentBytePosition = 1

    FilePathAndName = .FoundFiles(i)

    Open FilePathAndName For Binary As #FileNum
    'will restore
    Get #1, CurrentBytePosition, BackUpRange
    BColCount = AColCount + 4
    Sheets("DataReport").Range(Sheets("DataReport").Cells(1, AColCount), Sheets("DataReport").Cells(1000, BColCount)) = BackUpRange
    Close FileNum
    '################################################
    AColCount = BColCount + 1

    Next i

    End Sub


    My first problem: This code works, but it quickly reaches column 256 which is the max in excel. Is there a way to convert the imported range from columns to rows?

    Another question is: I export 5 columns of data to the text file. Can i choose which columns i want to import (the 2nd column for example) or do i have to import the WHOLE text file, then do my editing?

    I appreciate the help.

    Thanks

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Where are AColCount and BColCount declared?

    I think simply reversing the reference may be what you're looking for, changing columns to rows that is, but I'm not sure why row 1000 is used for the BColCount:

     
    Sheets("DataReport").Range(Sheets("DataReport").Cells(AColCount,1), Sheets("DataReport").Cells(BColCount, 1000)) = BackUpRange



    Of course you may want to change the variable names to "ARowCount" and "BRowCount", which would make more sense.

    Additionally, TsTom will probably show up and tweak it himself, he's probably better for tackling the 2nd part.

    HTH

    EDIT:: Is this page format slightly screwed now?
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-05-07 16:27 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark,
    Thanks, i will try your solution, but DOH, i forgot i have another problem now. The reason 1000 is in there is because the data backed up is from range a1 to e1000. there are many fields. Looks like i will only be able to import 256 customers at a time if i want to display the 1000 columns

    UNLESS, i can select which parts of the data to import. Then i can span customers over rows instead of columns. (cant span over rows right now because i have 1000 fields of data for each customer, excel only allows 256)

    Tom help!!

    Thanks

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was just going to say "Oops, no such thing as 1000 columns in Excel."

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Rob.
    I read the post and do not understand exactly what you nead help with?
    A range saved to a binary file must be restored to the same size range it was backed up from. If you store multiple ranges to one file, you will need to store the starting byte for each range as well.
    If you are needing an indexing system for your customer data, maybe use random access files as a working, though somewhat poor, substitute for Access.
    When I do my backups, I store all of my ranges to one file. At the beginning of this file I store an array to track my byte positions for each range.

    I do not understand why you need a loop to restore you customer data?

    How were the ranges backed up?

    I do not have the code I initialy wrote for you.

    Tom




    _________________
    Found a solution? If so, please post again so members of this board can spend their time helping others. Better still, edit your topic(intitial post), by tagging on a word or phrase such as, "Problem Solved", or "Resolved". Thanks for being courteous!

    [ This Message was edited by: TsTom on 2002-05-07 18:22 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom,

    thanks for the response, i figured out a way to fix the problem.

    The reason i import multiple times is i export text files for each customer. Each customer has a text file and a xls file. i do this to keep files small.

    my solution is to import to a worksheet, copy ranges i need, then import the next text file. works


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As long as it works...
    Good news

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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