"System resources overloaded on .txt file import

vimilac

New Member
Joined
Feb 11, 2011
Messages
8
Hello All:
I have come across a rather frustrating problem while trying to code a file import. Our engineering staff uses data on a series of motors that is contained in a rather large .txt file (~40K lines). The data in the text file is arranged in a single column. Each motor’s data includes some heading info (motor number, etc.), followed by a list of 300 parameters used in configuring it to its intended use. Below is a sample of the text file:
.
.
[299] = 0
[300] = 0
Field: MOT_SV2[1].$MOT_SPD_LIM Access: RW: INTEGER = 2700
Field: MOT_SV2[2].$SVMTR_ID Access: RW: INTEGER = 40743
Field: MOT_SV2[2].$ROBOT_ID Access: RW: STRING[21] = 'EXTENDED AXIS'
Field: MOT_SV2[2].$GRP_NUM Access: RW: INTEGER = 1
Field: MOT_SV2[2].$AXIS_NUM Access: RW: INTEGER = 1
Field: MOT_SV2[2].$MTR_ID Access: RW: STRING[21] = 'BAUTZ/3300 80A'
Field: MOT_SV2[2].$MTR_INF_ID Access: RW: STRING[13] = 'H DSP -'
Field: MOT_SV2[2].$SV_PARAM_ID Access: RW: STRING[9] = 'P01.00'
Field: MOT_SV2[2].$PARAM ARRAY[300] OF SHORT
[1] = 9
[2] = 640
[3] = 0
[4] = 3072
[5] = 0
[6] = 0
[7] = 16384
[8] = 0
.
.
I have been tasked with importing this text file into a spreadsheet, and dividing the data up so that each motor and its associated parameters are in separate rows, meaning the desired output sheet will have about 130 rows of 312 columns in each row. After using the macro recorder for generating the file import code, I wrote the code below to evaluate each line in the text file sequentially and then cut & paste it into the appropriate place.
It appears the macro works when first executed. However, periodically the root data (the .txt file) is updated from the manufacturer and the .txt file needs to be re-imported, which is why the code attempts to wipe the sheet clear before beginning the import.
The problem arises when the command button is selected to re-import the data. I get a “not enough system resources” message.
Any ideas on where I’ve gone wrong?
Sub ConvertImportedData_Click()
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim NewMotorHeader As String
Dim BeginningOfParameters As String
Dim LastRowsText As String
'
' This sub will first convert the continuous column of motor control data from a
' single long column (as it appears after Excel's data import function) into rows
' containing each motor's number followed by the ~300 parameters following
'
' Activate & Unprotect the sheet and unfreeze the view:
Windows("MotorFilesImported.xlsm").Activate
ThisWorkbook.Unprotect "actuator"
ActiveWindow.FreezePanes = False
' First, clear all cells in the sheet:
'
Cells.Select
Selection.QueryTable.Delete
Selection.ClearContents
' Delete the picture:
On Error Resume Next
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
If Err.Number <> 0 Then
Err.Clear
Else
Selection.Delete
End If
' Reformat all the cells in the sheet:
Range("D1").Select
Selection.Copy
Columns("D:LF").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'
' Add the text adjacent to the macro button at the top left of the sheet:
Range("A1:A3").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.MergeCells = True
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 9
End With
Range("A1:A3").Select
ActiveCell.FormulaR1C1 = " Clicking this button will clear the sheet and reload text file data. The process requires several minutes to complete. Use this control to update or refresh this data sheet."
' Now retrieve/import the data from the text file:
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;H:\Engineer\Fanuc Robot\Fanuc Motor Files.txt", Destination:=Range( _
"$D$4"))
.Name = "Fanuc Motor Files_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
'
'
'Format the column widths where the data will be distributed:
'
Columns("D:M").Select
Selection.ColumnWidth = 75
Columns("N:LA").Select
Selection.ColumnWidth = 9
'
'Now that the data has been imported, delete the query table:
'
Worksheets(1).QueryTable.Delete
'
'Cut and paste the entry in the top row of the data column:
'
Range("A14").Select
Selection.Cut
Range("D14").Select
ActiveSheet.Paste
'
'Now begin to evaluate each entry in the column of text sequentially (I will be the row counter)
'Look for the "Field: MOT_SV" as first 13 characters of a motor's header data and the left-hand
'brace, "[", as the beginning of parameter data. I, as noted before, is a row counter for the
'column of imported data. J is a row counter for the field on the sheet where the data will be
'distributed. K & L are column counters; K is for motor heading data types & L is used for the
'parameter data.
'
'Initialize variables:
'
I = 0
J = 0
K = 0
L = 0
NewMotorHeader = "Field: MOT_SV"
BeginningOfParameters = "["
'
'Begin looping through the column of data, evaluating each entry sequentially (I is a row counter
' for the column of data, J is a row counter for the field area where the data will be distributed,
' K is a column counter for header data, and L is a column counter for parameter data):
'
Do While Cells((15 + I), 1) <> ""
LastRowsText = Cells((14 + J), (K + 4)).text
'
'If the last row's text looks like the start of heading data, then either start a new row (the last
'row began with a "["), or paste it into the next column to the right:
'
If (VBA.Left(Cells((15 + I), 1), 13) = NewMotorHeader) Then
'
'If the text string matches a motor file's header string & the previous entry does not or the previous line
' began with the left brace for a parameter designation, then start a new row:
If (VBA.Left(LastRowsText, 1) = BeginningOfParameters) And (VBA.Left(LastRowsText, 13) = NewMotorHeader) Then
J = J + 1
Cells((15 + I), 1).Select
Selection.Cut
Cells((14 + J), 4).Select
ActiveSheet.Paste
K = 0 'reset the column counters for the new row
L = 0
End If
If (VBA.Left(LastRowsText, 1) <> BeginningOfParameters) Then
K = K + 1
Cells((15 + I), 1).Select
Selection.Cut
Cells((14 + J), (K + 4)).Select
ActiveSheet.Paste
End If
End If
'
'If the text string begins with a left brace, "[", then begin inserting the text into
'row J, starting a column 14 (N).
'
If (VBA.Left(Cells((15 + I), 1), 1)) = BeginningOfParameters Then
If (VBA.Left(LastRowsText, 1) = BeginningOfParameters) Then
K = 0 'reset the column counter for heading data
Cells((15 + I), 1).Select
Selection.Cut
Cells((15 + J), (14 + L)).Select
ActiveSheet.Paste
L = L + 1
End If
If (VBA.Left(LastRowsText, 1) <> BeginningOfParameters) Then
L = 0
Cells((15 + I), 1).Select
Selection.Cut
Cells((15 + J), (14 + L)).Select
ActiveSheet.Paste
End If
I = I + 1
Loop
' Next, some formatting cell backgrounds:
I = 2
Range("D14:LA14").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 26578297
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.Color = 65535
End With
Do While I <= J
Range(Cells(14, 4), Cells(15, 312)).Select
Selection.Copy
Range(Cells(14 + J, 4), Cells(15 + J, 312)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
J = J + 2
Loop
' Add the picture:
ActiveSheet.Shapes.AddPicture(Filename:="C:\Users\vml\Pictures\GE-Fanuc-motors.jpg", linktofile:=False, savewithdocument:=True, Left:=760, Top:=10, Width:=100, Height:=100).Select
'
' Insert the worksheet's title text:
Range("B6:B9").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection.Font
.Name = "Raavi"
.FontStyle = "Bold Italic"
.Size = 28
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 4240886
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "FANUC MOTOR PARAMETERS"
' Now, let's reduce the very wide column widths from when the columnar data occupied them:
Cells.Select
Selection.Columns.AutoFit
Columns("A:C").Select
Selection.ColumnWidth = 8
'
' Next, some column header information:
' First, format the header area (the fonts):
Range("D12:LA13").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
' Put in the heading titles:
Range("E12").Select
ActiveCell.FormulaR1C1 = "Heading Data"
Range("K12").Select
ActiveCell.FormulaR1C1 = "Heading Data"
Range("D12:L13").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 1512886
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("M12:LA13").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 1738786
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("M12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("AB12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("AQ12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("BF12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("BU12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("CJ12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("CY12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("DN12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("EC12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("ER12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("FG12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("FV12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("GK12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("GZ12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("HO12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("ID12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("IS12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("JH12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("JW12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("KL12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("LA12").Select
ActiveCell.FormulaR1C1 = "Parameters"
Range("M13").Select
ActiveCell.FormulaR1C1 = "1"
Range("N13").Select
ActiveCell.FormulaR1C1 = "2"
Range("O13").Select
ActiveCell.FormulaR1C1 = "3"
Range("N13:O13").Select
Selection.AutoFill Destination:=Range("M13:LA13"), Type:=xlFillDefault
' Insert the sheet's unlock password:
Range("A8").Select
ActiveCell.FormulaR1C1 = "Unlock password: actuator (lower case)"
'
' Replace the worksheet protection:
Range("A1:LA13").Select
Selection.CellFormat.Locked = True
ThisWorkbook.Protect "actuator"
' Freeze the view pane:
Range("A14").Select
ActiveWindow.FreezePanes = True
Range("A11").Select
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I'm suspecting that one of the properties listed within the WITH block under the querytables.add statement isn't right.
 
Upvote 0

Forum statistics

Threads
1,224,225
Messages
6,177,273
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top