p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   How To Export a specific Excel Sheet as a csv file (http://p2p.wrox.com/showthread.php?t=45944)

mrjits July 31st, 2006 02:07 PM

How To Export a specific Excel Sheet as a csv file
 
hey all:

I was wondering if there was a way in EXCEL VBE to export a specific Excel Worksheet (not the entire workbook) as a .csv file. Thanks

MarioP August 1st, 2006 09:44 AM

This is a way to do it:

ActiveWorkbook.SaveAs Filename:="Filename.csv", _
        FileFormat:=xlCSV

Ciao

Mario

mrjits August 1st, 2006 12:55 PM

unfortunately, that seems to save the entire workbook. is there a way i can just save one worksheet (like Sheet1)? Thanks

KALOR55 August 1st, 2006 01:41 PM

This module exports current region from worksheet to .csv file. Mayby it help You.

Sub ExportRange()
Dim FirstCol As Integer
Dim LastCol As Integer
Dim C As Integer
Dim FirstRow
Dim LastRow
Dim R
Dim data
Dim ExpRng As Range
Range("A1").Select
    Set ExpRng = ActiveCell.CurrentRegion
    FirstCol = ExpRng.Columns(1).Column
    LastCol = FirstCol + ExpRng.Columns.Count - 1
    FirstRow = ExpRng.Rows(1).Row
    LastRow = FirstRow + ExpRng.Rows.Count - 1

    Open ThisWorkbook.Path & "\textfile.csv" For Output As #1 ' csv file
    '''''''Open ThisWorkbook.Path & "\textfile.txt" For Output As #1 '''''or txt file
        For R = FirstRow To LastRow
            For C = FirstCol To LastCol
                data = ExpRng.Cells(R, C).Value
                If data = "" Then data = ""
                If IsNumeric(data) Then data = Val(data)
                If C <> LastCol Then
                    Write #1, data;
                Else
                    Write #1, data
                End If
            Next C
        Next R
    Close #1
End Sub


mrjits August 1st, 2006 02:45 PM

thank you. that does help :)

KALOR55 August 1st, 2006 03:04 PM


I have the next solution (I think that it is better)
Please read comments
Sub KOPIA1()
    Dim NAME_OF_SHEET As String
    NAME_OF_SHEET = Application.ActiveSheet.Name ' name of sheet which will be saved as .csv
    Workbooks.Add
    ActiveWorkbook.SaveAs ("C:\Documents and Settings\KAZIKL\Pulpit\" & NAME_OF_SHEET & ".xls") ' C:\Documents and Settings\KAZIKL\Pulpit\ path to temporary .xls file (You should change this patch)
    Windows("KAZIK1.xls").Activate ' here put name of your file
    Sheets(NAME_OF_SHEET).Select
    Sheets(NAME_OF_SHEET).Copy Before:=Workbooks(NAME_OF_SHEET & ".xls").Sheets(1)
    Sheets(Array("Arkusz1", "Arkusz2", "Arkusz3")).Select ' in English Sheet1, Sheet2 and Sheet3
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\KAZIKL\Pulpit\" & NAME_OF_SHEET & ".csv", FileFormat:=xlCSV 'C:\Documents and Settings\KAZIKL\Pulpit\ path to .csv file (You should change this patch)
    ActiveWorkbook.Save
    ActiveWindow.Close
    Windows("KAZIK1.xls").Activate
    Kill ("C:\Documents and Settings\KAZIKL\Pulpit\" & NAME_OF_SHEET & ".xls") 'C:\Documents and Settings\KAZIKL\Pulpit\ path to temporary .xls file (You should change this patch)
    Application.DisplayAlerts = True
End Sub



All times are GMT -4. The time now is 11:45 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.