256 Column limitation

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

I have 10 sheets each with 256 columns each. I would like
to export these data into one *.csv, *.txt, or any other
text like format. Is there a way to export all 2560
columns into one text document? (they are all sorted in
the same order).

Please help
JD
 
Do you mean that you want to print the first row of each of the sheets on
one line, and then on the next line print the second row fo wach of the
sheets, etc? I.e., go through each of the sheets in an inner loop to cycling
through row numbers?

You'll need a VBA routine (well, that pops into my small brain), but also to
clarify exactly what you mean.

Dave Braden
 
Harlan,
Nice response (as usual). If you pull that IIf out of the loop and instead
lop off the extra SEP at the end, you speed things up, for the OP's case, by
about 12% on the Wintel I am running at the moment; coercing the range to a
variant, one row at a time, plus the above idea, gives code that is about 4
times faster, as follows.

Regards,
Dave Braden

Revised foo:
=========
Option Explicit
Declare Function QueryPerformanceCounter Lib "Kernel32" _
(X As Currency) As Boolean
Declare Function QueryPerformanceFrequency Lib "Kernel32" _
(X As Currency) As Boolean
#Const bPrintTime = True

Sub foo3()
'Original post by Harlan Grove 2003/08/22
'Modified by Dave Braden 2003/08/23

'modify by making these constants variables and using InputBox to change
them
Const SEP As String = ","
Const FN As String = "C:\output.csv"

Dim l As Long, k As Long, lLastRow As Long, lFd As Long
Dim s As String, v As Variant
Dim sht As Worksheet, shtColl As Object

#If bPrintTime Then
Dim Count1 As Currency, Count2 As Currency, Freq As Currency
QueryPerformanceFrequency Freq
#End If

On Error GoTo HandleErr

lFd = FreeFile
Open FN For Output As #lFd

'determine sheets to print side-by-side
Set shtColl = ActiveWorkbook.Worksheets

'establish greatest rownumber of all sheets
For Each sht In shtColl
With sht.UsedRange: l = .Row + .Rows.Count - 1: End With
If l > lLastRow Then lLastRow = l
Next

#If bPrintTime Then
QueryPerformanceCounter Count1
#End If

For l = 1 To lLastRow
s = ""
For Each sht In shtColl
v = sht.Rows(l)
For k = 1 To 256
s = s & SEP & v(1, k)
Next
Next
Print #lFd, Mid(s, 1 + Len(SEP))
Next

#If bPrintTime Then
QueryPerformanceCounter Count2
Debug.Print (Count2 - Count1) / Freq ' * 1000 & " mSec"
#End If

HandleErr:
Close #lFd
End Sub
=============
 
Back
Top