#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0

    VB Script - Macro on multiple workshets, save to csv


    Good Morning,

    I am attempting to run what I thought what was some very basic code, and am running into a wall.

    In a nutshell. Have a macro in one workbook. From that workbook I want to loop through worksheets in other workbooks (all in the same directory) then dump the contents of those worksheets into individual .csv files. The progam runs fine until it goes to save the .csv files.

    The code is below. Any help would be greatly appreciated.

    Option Explicit
    Sub Open_Dir()

    Dim FSO, FLD, FIL, XL
    Option Explicit
    Sub Open_Dir()

    Dim FSO, FLD, FIL, XL
    Dim strFolder, sFileName, sFull_File_Path

    Dim newWks As Worksheet
    Dim wks As Worksheet

    '############ Set up the folder
    strFolder = "C:\Backup2\"

    'Create the filesystem object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FLD = FSO.GetFolder(strFolder)

    ' 'loop through the folder and get the file names
    For Each FIL In FLD.Files

    'Get the file name
    sFileName = FIL.Name

    'String together the path and file name
    sFull_File_Path = strFolder & sFileName

    'Set up the Excel file
    Set XL = CreateObject("Excel.application")
    XL.Application.Workbooks.Open sFull_File_Path
    XL.Application.Visible = False

    'Do your thing here
    For Each wks In XL.ActiveWorkbook.Worksheets

    Debug.Print wks.Name
    'wks.Copy 'to a new workbook
    'Set newWks = ActiveSheet
    With wks
    'Code stops here
    .SaveAs FileName:=strFolder & wks.Name, FileFormat:=xlCSV
    .Parent.Close savechanges:=False
    End With
    Next wks

    MsgBox "done with: " & ActiveWorkbook.Name

    Debug.Print sFileName


    'End the Excel file
    Set XL = Nothing

    'Get the next file
    Next

    End Sub
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    18
    Rep Power
    0
    You are trying to save a SHEET using SaveAs whereas you can only save a WORKBOOK. Change to thus:


    Debug.Print wks.Name
    'wks.Copy 'to a new workbook
    'Set newWks = ActiveSheet
    With wks
    .Copy ' Copies the sheet to a newworkbook
    'Code stops here
    Activeworkbook.SaveAs FileName:=strFolder & wks.Name, FileFormat:=xlCSV
    Activeworkbook.Close savechanges:=False
    End With
    Next wks

    Just a thought, your "Save as" is only based on the sheet name, if you have e.g. a "Sheet1" in several of your workbooks then they will eachoverwrite each other except the last one

IMN logo majestic logo threadwatch logo seochat tools logo