Forcing xl2K7 Column Count back to xl2K3 size

[Living the Dream]

Hi all

My company has upgraded most of its systems to run 2007 which has
created an issue for me as one of my files does not like working with it
which I'm fairly certain relates to the sheets column count.

I download a CSV file from another company that has to be converted
before I can essentially upload it into my workbook so all the "Do
Something Stuff"has to be done prior., As I now run 2007 the CSV opens
within that version environment and it is here where things go to crap.

When it comes time to copying and pasting the entire row ( A to XFD
)across to my 2003 structured worksheet which is only ( AA to IV ), it
throws a hissy-fit and halts because they are different sizes.

If copyLine = True Then
Rows(i & ":" & i).Select
Selection.Cut
Windows(cWkBook).Activate
Sheets("TMS DATA").Select
Rows(lastrow).Select


This is the code that actually opens the CSV file, is there a line I can
insert at the beginning that would tell the 2007 workbook being opened
to reduce it's column count back to ( A to IV ) before it starts doing
it's thing..

Sub openCSV(inp As String)
On Error Resume Next

Workbooks.OpenText Filename:=inp, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1)), TrailingMinusNumbers:=True

Columns("F:F").Select
Selection.Delete Shift:=xlToLeft

Columns("N:N").Select
Selection.Insert Shift:=xlToRight

For x = 1 To 10000

tVal = Cells(x, 12).Value
tVal1 = Cells(x, 13).Value
tVal3 = tVal & tVal1

If tVal3 = "" Then
Exit For
End If

tTime = WorksheetFunction.Search(" ", tVal)
If tTime <> "" Then
tTime = Right(tVal, Len(tVal) - tTime)
End If

tTime1 = WorksheetFunction.Search(" ", tVal1)
If tTime1 <> "" Then
tTime1 = Right(tVal1, Len(tVal1) - tTime1)
End If

If IsDate(tVal) Then
Cells(x, 12).Value = Day(Cells(x, 12).Value) & "/" & Month(Cells(x,
12).Value) & "/" & Year(Cells(x, 12).Value) '& " " & tTime

End If

If IsDate(tVal1) Then
Cells(x, 13).Value = Day(Cells(x, 13).Value) & "/" & Month(Cells(x,
13).Value) & "/" & Year(Cells(x, 13).Value) '& " " & tTime1
End If

tVal = ""
tVal1 = ""
tTime = ""
tTime1 = ""
Next x

Columns("M:M").Select
Selection.NumberFormat = "dd/mm/yyyy"

End Sub

I really appreciate any assistance please..

TIA
Mick.

 

[Ben McClave]

Mick,

Have you tried changing this line from:

If copyLine = True Then
Rows(i & ":" & i).Select
Selection.Cut

to:

If copyLine = True Then
Range("A" & i & ":" & "IV" & i).Cut

 

[Phil Hibbs]

If copyLine = True Then
Intersect(ActiveSheet.usedRange, Rows(i & ":" & i)).Select
Selection.Cut


That function "Intersect(ActiveSheet.usedRange, ...)" is really useful. Mostly I use it as a performance improvement if I'm looping over all cells or rows or columns in a range.

Phil.

 

[Living the Dream]

Hi all

Well it seems I took the long-winded scenic route on this one.

I managed a workaround for it, so instead of this:

If copyLine = True Then
Rows(i & ":" & i).Select
Selection.Cut

I changed it to this:

If copyLine = True Then
Range(Cells(i, 1),Cells(i, 256)).Select
Selection.Cut

And it work well..

Cheers
Mick.