By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Save each excel sheet (unknown amount) as csv file

P: 1
Hi
I`m trying to automate saving Excel sheets (each one) in csv file format
with vbScript. Loop preferable

I tried something... please someone help me.

Expand|Select|Wrap|Line Numbers
  1. Sub CSV2()
  2.     Dim appExcel As Excel.Application
  3.     Dim wbSource As Excel.Workbook
  4.     Dim wsSource As Excel.Worksheets
  5.     Dim MyPath As String
  6.     Dim MyFile As String
  7.  
  8.     Dim J As Integer
  9.  
  10.     J = 0
  11.     MyPath = "C:\Documents and Settings\Avi\My Documents\VB\Macros"
  12.     MyFile = "\Book1.xls"
  13.  
  14.     'appExcel.Visible = False
  15.  
  16.     Set wbSource = appExcel.Workbooks.Open(MyPath & MyFile)
  17.  
  18.     For J = 1 To wbSource.Worksheets.Count
  19.         Set wsSource = wbSource.Worksheets(I)
  20.         wsSource.Select
  21.         appExcel.ActiveWorkbook.SaveAs Filename:=MyPath & "Mysheet" _   
  22.         & J & ".csv", FileFormat  := xlCSV, CreateBackup := False
  23.         appExcel.DisplayAlerts = False
  24.     Next
  25.     wbSource.Close
  26.     appExcel.Workbooks.Close
  27.     appExcel.Quit
  28.     wsSource = Nothing
  29.     wbSource = Nothing
  30.     appExcel = Nothing
  31.  
  32. End Sub
Jan 22 '08 #1
Share this Question
Share on Google+
1 Reply


P: 58
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Call CSV2
  4.  
  5. Sub CSV2()
  6.    Dim obj_xl, obj_wb, obj_sheet
  7.    Dim path_name, file_name, i
  8.  
  9.    path_name  = "C:\"
  10.    file_name  = "Test.xls"
  11.  
  12.    Set obj_xl = CreateObject("Excel.Application")
  13.    Set obj_wb = obj_xl.Workbooks.Open(path_name & file_name)
  14.  
  15.    obj_xl.Visible       = False
  16.    obj_xl.DisplayAlerts = False
  17.  
  18.    For i = 1 To obj_wb.Worksheets.Count
  19.       Set obj_sheet = obj_wb.Worksheets(i)
  20.       obj_sheet.Activate
  21.       obj_xl.ActiveWorkbook.SaveAs ( path_name & "Mysheet" & i & ".csv" ), 6
  22.    Next
  23.  
  24.    obj_wb.Close
  25.    obj_xl.Quit
  26.  
  27.    Set obj_sheet = Nothing
  28.    Set obj_wb    = Nothing
  29.    Set obj_xl    = Nothing
  30. End Sub
  31.  
Jan 23 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.