How to make one CSV file from two Excel sheets?

[Marek L.]

I have a huge database with about 400 columns. They are divided into two
sheets - due to Excel limitation 256 columns in one sheet.

I need to make one CSV file from these both sheets. The last column of first
sheet should be followed by the first column of the second sheet.

Is there any way how to do it directly from Excel? Or do you know any simple
utility that can put together two CSV files exported from Excel?

Thanks for any help

Marek L.

[nbrcrunch]

That sort of stiching can only be done using a hex editor. (And not all
hex editors are created equal.) I did a quick search on the web and
there are free editors out there, but I don't know if they are any
good. Its been 10 years since I used one.


--
nbrcrunch

[Dave Peterson]

J.E. McGimpsey has some nice code to create text files at:
http://mcgimpsey.com/excel/textfiles.html

Chip Pearson has some more nice code at:
http://www.cpearson.com/excel/imptext.htm

This example just adds commas between each field. This may not be exactly what
you need (double quotes around some strings/values formatted as date/time???).

But it may get you started:

Option Explicit

Sub testme01()

Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut1 As String
Dim sOut2 As String
Dim wks1 As Worksheet
Dim wks2 As Worksheet

Dim myCell As Range

Dim iRow As Long
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastCol1 As Long
Dim LastCol2 As Long
Dim FirstRow As Long

Set wks1 = Worksheets("sheet1")
Set wks2 = Worksheets("sheet2")

With wks1
LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With wks2
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

If LastRow1 <> LastRow2 Then
MsgBox "Rows don't match!"
Exit Sub
End If

'adjust to match your data
FirstRow = 1 'common first row
LastCol1 = 256 'last column of wks1
LastCol2 = 10 'last column of wks2

nFileNum = FreeFile
Open "c:\File1.txt" For Output As #nFileNum

For iRow = FirstRow To LastRow1
sOut1 = ""
sOut2 = ""
With wks1
For Each myCell In .Range(.Cells(iRow, "A"), .Cells(iRow, LastCol1))
sOut1 = sOut1 & "," & myCell.Value
Next myCell
End With
sOut1 = Mid(sOut1, 2)
With wks2
For Each myCell In .Range(.Cells(iRow, "A"), .Cells(iRow, LastCol2))
sOut2 = sOut2 & "," & myCell.Value
Next myCell
End With
sOut2 = Mid(sOut2, 2)

Print #nFileNum, sOut1 & sOut2

Next iRow
Close #nFileNum
End Sub




Marek L. wrote:
>
> I have a huge database with about 400 columns. They are divided into two
> sheets - due to Excel limitation 256 columns in one sheet.
>
> I need to make one CSV file from these both sheets. The last column of first
> sheet should be followed by the first column of the second sheet.
>
> Is there any way how to do it directly from Excel? Or do you know any simple
> utility that can put together two CSV files exported from Excel?
>
> Thanks for any help
>
> Marek L.

--

Dave Peterson

[Kerala Ayurveda Treatments]

Choose save as option from file menu and choose Excel CSV format from file
menu type a file name and save it

Kerala Ayurveda Treatments
www.ayurvedatreatements.net
Ayurveda- A natural way to perfect health.

[email protected]


"Marek L." wrote:

> I have a huge database with about 400 columns. They are divided into two
> sheets - due to Excel limitation 256 columns in one sheet.
>
> I need to make one CSV file from these both sheets. The last column of first
> sheet should be followed by the first column of the second sheet.
>
> Is there any way how to do it directly from Excel? Or do you know any simple
> utility that can put together two CSV files exported from Excel?
>
> Thanks for any help
>
> Marek L.