Print to .csv file

D

Dee_Conrad

I have several workbooks with hidden columns, filtered rows, and
merged cells.

I am trying to write a macro that will emulate the print to file
option, but in a .csv format.

I only need the data that can be seen, but copy visible only doesn't
work because of the merged cells. There is data in the merged cells
that I need, but often the row that contains the initial merged cell
is filtered out.

The desired data is visible when the file is printed, but I need this
data in a comma delimited file.

Because I have several workbooks with different cells visible when
filtered, a range specific macro is not desirable.

Does anyone have a good solution?

TIA,
Dee
 
K

Kevin McCartney

Hi Dee,
I'd reckonmend the following.

Add a menu option or menu button to activate a macro on the active sheet. The macro needs to loop through all columns within the active sheet and for each visiable column copy and paste special as values to a new sheet. Once all columns have been processed you can then export the new sheet out to a csv file. Using this process on active sheet the macro won't care how the sheet is layed out, it will just copy and paste visible columns to a new sheet.

So you need something like this

Set wksData = application.activesheet
Set wksExport = application.activeworkbook.addsheet
intCol = 1

For Each Col in wksData.DataRange
If Col.Visible then
wksData.Range(Col:Col).Copy
wksExport.Range(IntCol:IntCol).PasteSpecial As Values
intCol = intCol +1
end if
Next Col

Export wksExport as csv

wksData,Activate
Msgbox "All Done"

Hope that helps
ciao
KM
 
D

Dee_Conrad

Kevin,

I tried your macro but kept on getting Runtime error 438 - Object
doesn't support property or method. I'm working in Excel 2002.

I've been trying to work my way through the syntax, but as I am new to
VBA it is taking awhile. Is there a solution which works in 2002?

Dee
---------------------------------------------------------------------
Kevin McCartney said:
Hi Dee,
I'd reckonmend the following.

Add a menu option or menu button to activate a macro on the active
Click to expand...
sheet. The macro needs to loop through all columns within the active
sheet and for each visiable column copy and paste special as values to
a new sheet. Once all columns have been processed you can then export
the new sheet out to a csv file. Using this process on active sheet
the macro won't care how the sheet is layed out, it will just copy and
paste visible columns to a new sheet.
 
T

Tom Ogilvy

Kevin said "something like this."

The code is pseudo code. It is no where near executable.
 
D

Dee_Conrad

Tom,

I worked through the code (Thanks, like I said I'm new to VBA) but I
still can not get it to work with the merged cells.

Is there a 'printer' that I can load where I can print to file and it
will be formatted as csv instead of all the special characters?

TIA

Dee


--------------------------------------------------------------
 
D

Dave Peterson

I'm not sure what you want to do with the merged cells, but maybe you could
write your own CSV file:

Option Explicit
Sub testme()

Dim myRow As Range
Dim myCell As Range
Dim wks As Worksheet

Dim myFileName As String
Dim FileNum As Long
Dim myLine As String

myFileName = "C:\test.txt"

Set wks = Worksheets("sheet1")

With wks

FileNum = FreeFile
Close FileNum
Open myFileName For Output As FileNum

For Each myRow In .UsedRange.Rows
myLine = ""
For Each myCell In myRow.Cells
If myCell.EntireRow.Hidden = True Then
'skip the whole row
Exit For
Else
If myCell.EntireColumn.Hidden = True Then
'skip this one
ElseIf myCell.MergeArea(1).Address = myCell.Address Then
myLine = myLine & "," & myCell.Text
End If
End If
Next myCell
If myLine <> "" Then
Print #FileNum, Mid(myLine, 2)
End If
Next myRow

Close FileNum
End With

End Sub

If this doesn't help, there's lots of code you might want to look at to help:

Earl Kiosterud's Text Write program:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html
 
Top