TSTom or anyone, some help please :)

[robfo0]

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

[Mark O'Brien]

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 ]

[robfo0]

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

[Mark O'Brien]

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

[Tom Schreiner]

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 ]

[robfo0]

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

[Tom Schreiner]

As long as it works...
Good news