File saving in excel

Question

There is a code written under a button click to save the workbook content to a txt file

Code:

Private Sub CommandButton1_Click()
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
    Dim strOutputFileName

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\Documents and Settings\bera02a\Desktop\Arun_TAT_Testing_orig_14022013.xls")

    For Each xlSheet In xlBook.Worksheets
        strOutputFileName = "C:\Documents and Settings\bera02a\Desktop\" & xlSheet.Name & ".txt"
        xlSheet.SaveAs Filename:=strOutputFileName, FileFormat:=xlUnicodeText
    Next
    xlApp.Quit
End Sub

Original line in workbook:

create table abc as select rpt2_id, rpt2_usgcount, rpt2_usgdate, rpt2_usgmins from rpt2 where rpt2_id = 'xyz;

Output is coming as:

"create table abc as select rpt2_id, rpt2_usgcount, rpt2_usgdate, rpt2_usgmins from rpt2 where rpt2_id = 'xyz;"

Thee output is coming with double quotes at the start and end and it is only coming for the insert and create statements present in the workbook.

Can anyone help me in not getting that quotes in the output.?

peter has helpmed me last time in gettnig the solution related to excel.if he could help me in this too — user2075017
– user2075017, Commented Feb 18, 2013 at 16:17
Please follow @SiddharthRout suggestion and take a look here: msdn.microsoft.com/en-us/library/office/… Good luck! — Peter L.
– Peter L., Commented Feb 18, 2013 at 16:33

Siddharth Rout · Accepted Answer · 2013-02-20 21:37:07Z

1

Though you have asked for Peter's help specifically but see if this is what you want?

xlSheet.SaveAs Filename:=strOutputFileName, FileFormat:=xlTextPrinter

FOLLOWUP (From Comments)

Try this

Private Sub CommandButton1_Click()
    Dim xlBook As Workbook, xlSheet As Worksheet
    Dim strOutputFileName As String
    Dim n As Long, i As Long, j As Long
    Dim MyData As String, strData() As String, MyArray() As String
    Dim strPath As String

    strPath = ActiveWorkbook.Path '<~~ \\plyalnppd3sm\d$\Temp\Arun\TAT\

    ThisWorkbook.SaveCopyAs strPath & "\Temp.xls"

    Set xlBook = Workbooks.Open(strPath & "\Temp.xls")

    For Each xlSheet In xlBook.Worksheets
        If xlSheet.Name <> "User_provided_data" Then
            strOutputFileName = strPath & "\" & xlSheet.Name & ".zup"
            xlSheet.SaveAs Filename:=strOutputFileName, FileFormat:=xlTextMSDOS

            n = n + 1

            ReDim Preserve MyArray(n)
            MyArray(n) = strOutputFileName
            Debug.Print strOutputFileName
        End If
    Next

    xlBook.Close SaveChanges:=False

    Kill strPath & "\Temp.xls"

    For i = 1 To UBound(MyArray)
        '~~> open the files in One go and store them in an array
        Open MyArray(i) For Binary As #1
        MyData = Space$(LOF(1))
        Get #1, , MyData
        Close #1
        strData() = Split(MyData, vbCrLf)

        '~~> Write to the text file
        Open MyArray(i) For Output As #1

        '~~> Loop through the array and check if the start and end has "
        '~~> And if it does then ignore those and write to the text file
        For j = LBound(strData) To UBound(strData)
            If Left(strData(j), 1) = """" And Right(strData(j), 1) = """" Then
                strData(j) = Mid(strData(j), 2, Len(strData(j)) - 2)
            End If
            Print #1, strData(j)
        Next j
        Close #1
    Next i
End Sub

edited Feb 20, 2013 at 21:37

answered Feb 18, 2013 at 16:23

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Peter L. Over a year ago

@user2075017 be aware about limitations: excelforum.com/excel-programming-vba-macros/…

Siddharth Rout Over a year ago

@user2075017: That is because it is beyond 256 characters. See the limitation in the link given above. I can give you an alternative code which can remove the " from the beginning and the end if you prefer?

Peter L. Over a year ago

@user2075017 if you follow the link you'll find a proposed solution to bypass that limitation. Moreover, that's another question.

Siddharth Rout Over a year ago

Send me the file when u reach home and then we will take it from there? I believe the errors are happening because of some other error in the module modAddin

user2075017 Over a year ago

Hi Siddharth..I will send you the excel as soon as i reach home..meanwhile if you can sort me with the above mentioned error which i am getting in the output where "" is present the output is generated as """"

|

Collectives™ on Stack Overflow

File saving in excel

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related