VBA Import Text Line by Line to formatted Cells

[Kalain]

Hi Guys,
I have the following problem:


I will import some very large Logs (*.txt files)
Source looks like this:

 id:name:IO_group_id:IO_group_name:status:mdisk_grp_id:mdisk_grp_name:capacity:type:FC_id:FC_name:RC_id:RC_name:vdisk_UID:fc_map_count:copy_count:fast_write_state:se_copy_count:RC_change:compressed_copy_count:parent_mdisk_grp_id:parent_mdisk_grp_name:formatting:encrypt:volume_id:volume_name:function
    0:BCAS_IBMi_Template_V7R2:1:io_grp1:online:0:BCSTG01-DEMO-Power:250.00GB:striped:::::6005076801818772D000000000000000:0:1:empty:0:no:0:0:BCSTG01-DEMO-Power:no:no:0:BCAS_IBMi_Template_V7R2:
    1:BCAS_BCSTG01_Live_Source_Lun001:1:io_grp1:online:0:BCSTG01-DEMO-Power:250.00GB:striped:many:many:::6005076801818772D000000000000003:3:1:not_empty:0:no:1:0:BCSTG01-DEMO-Power:no:no:1:BCAS_BCSTG01_Live_Source_Lun001:
    2:BCAS_Mimix01_Target_Lun001:1:io_grp1:online:0:BCSTG01-DEMO-Power:250.00GB:striped:::::6005076801818772D000000000000004:0:1:not_empty:0:no:1:0:BCSTG01-DEMO-Power:no:no:2:BCAS_Mimix01_Target_Lun001:
    3:BCAS_IBMi_Template_V7R1:1:io_grp1:online:0:BCSTG01-DEMO-Power:250.00GB:striped:::::6005076801818772D000000000000005:0:1:empty:0:no:0:0:BCSTG01-DEMO-Power:no:no:3:BCAS_IBMi_Template_V7R1:
    4:BCAS_BCAS001_Lun001:0:io_grp0:online:0:BCSTG01-DEMO-Power:150.00GB:striped:::::6005076801818772D000000000000006:0:1:empty:0:no:0:0:BCSTG01-DEMO-Power:no:no:4:BCAS_BCAS001_Lun001:
    5:BCAS_BCAS001_Lun002:0:io_grp0:online:0:BCSTG01-DEMO-Power:150.00GB:striped:::::6005076801818772D000000000000007:0:1:empty:0:no:0:0:BCSTG01-DEMO-Power:no:no:5:BCAS_BCAS001_Lun002:
    6:BCAS_BCAS002_Lun001:1:io_grp1:online:0:BCSTG01-DEMO-Power:240.00GB:striped:::::6005076801818772D000000000000008:0:1:empty:0:no:0:0:BCSTG01-DEMO-Power:no:no:6:BCAS_BCAS002_Lun001:

I've imported them yet with this code:

With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & lsvdiskf, Destination _
            :=Range("$A$1"))
            .Name = "lsvdisk_1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = ":"
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, _
            1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh

Now I have the Problem, that I run into a Runtime Error (Not enough ressources).


I think the module is going too big by the import task.


So I will import 10 lines from the file, and paste them (splitted into cells) - and again - and again till the end of file is reached.


I've looked in the WWW - found some ideas - but nothing that fits to me.


Do anyone have an idea? - Or: Is it possible?


Thank you very much!


BR,
Kalain

[mdmackillop]

Can you post a sample log file of approx. 100 rows?

[p45cal]

You could try something like (stolen from http://stackoverflow.com/questions/1...by-line-in-vba ):

Sub blah()
Dim FileNum As Integer
Dim DataLine As String

FileNum = FreeFile()
Open lsvdiskf For Input As #FileNum
Set Destn = Range("A1")
While Not EOF(FileNum)
  Line Input #FileNum, DataLine  ' read in data 1 line at a time
  ' decide what to do with dataline,
  ' depending on what processing you need to do for each case
  Z = Split(DataLine, ":")
  Destn.Resize(, UBound(Z) + 1).Value = Z
  Set Destn = Destn.Offset(1)
Wend
End Sub

but I have no idea whether it will use fewer resources!


Are you going to filter/remove rows after import? If so you could avoid importing these rows at all by using an sql query with ADO or similar.


Ahh, I see you've cross posted to that site anyway (http://stackoverflow.com/questions/4...ormatted-cells)

[Kalain]

Sorry for this late answer..

Well, I won't filter or remove lines after import. My idea is:
When I copy and paste maybe 10 lines at ones, the module cache wont get too big and I dont run into the resources problem.

BR,
Flo