PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Sunday, August 28, 2022

[FIXED] How to save table in Excelworksheet as CSV in vba and continue working on the previous file

 August 28, 2022     csv, excel, vba     No comments   

Issue

Hey I am quite new to VBA and I am currently trying to export tables from different sheets, if there is an alternation made to it, as CSV data. Currently my code exports all tables from my file. How can I make it export only the current table that I am executing the makro on? Thank you for your help!

Public Sub SaveWorksheetsAsCsv()
Dim xWs As Worksheet
Dim xDir As String
Dim folder As FileDialog
Set folder = Application.FileDialog(msoFileDialogFolderPicker)

If folder.Show <> -1 Then Exit Sub

xDir = folder.SelectedItems(1)

For Each xWs In Application.ActiveWorkbook.Worksheets

xWs.SaveAs xDir & "\" & xWs.name, xlCSV

Next

End Sub

I figured out a way to do it but now the window opens as a CSV file. How do i close the csv file and reopen the worksheet I was working on?

Public Sub SaveWorksheetsAsCsvUndercarriageDefinition()
Dim wbk As Workbook
Dim xWs As Worksheet
Dim xDir As String
Dim folder As FileDialog

Set wbk = Workbooks("Vba_Fehlerprüfung.xlsm")
Set xWs = wbk.Worksheets("Undercarriage Definition")
Set folder = 
Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)
'For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.SaveAs xDir & "\" & xWs.name, xlCSV
'Next
End Sub

Solution

My suggestion would be to use the following sub in order to export a table resp. an listobject

Sub exportListobject(lo As ListObject, csvFilename As String)
    
    Dim wbNew As Workbook
    Set wbNew = Workbooks.Add
    
    Dim wsNew As Worksheet

    With wbNew
        Set wsNew = wbNew.Sheets(1)
        lo.Range.Copy
        wsNew.Range("A1").PasteSpecial Paste:=xlPasteAll
        .SaveAs Filename:=csvFilename, _
            FileFormat:=xlCSVMSDOS, CreateBackup:=False
        .Close False
    End With

End Sub

It will copy the listobject in question into a new workbook, save it as an csv file and close it. The workbook which contains the listobject will not be touched.

If you want to export a single sheet from your workbook you can use a similar sub

Sub exportSheet(sh As Worksheet, csvFilename As String)

    Dim wbNew As Workbook
    Set wbNew = Workbooks.Add
    
    Dim wsNew As Worksheet

    With wbNew
        sh.Copy wbNew.Sheets(1)
        Set wsNew = wbNew.Sheets(1)
        .SaveAs Filename:=csvFilename, _
            FileFormat:=xlCSVMSDOS, CreateBackup:=False
        .Close False
    End With

End Sub


Answered By - Storax
Answer Checked By - David Goodson (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing