none
How to save range to a closed CSV file or closed Excel file? RRS feed

  • Question

  • I have some code that has to create a copy of a template, copy/paste everything to a sheet that was just created from the template, and save this matrix of data.  The script worked when I wrote the matrices to a summary sheet in the workbook, but it got really slow after a while, because that summary sheet was growing larger and larger.  So, I thought I could just dump the contents of the matrix to a closed CSV file, or close Excel file.  I need to run the loop over 8,000 times.  That's the problem.  I think if I save each run to an external file, it should work fine.  Here's my code so far . . .


    Sub Export()
    
       ' SOME CODE HERE; ALL WORKS FINE 
    
           Range("BG5:BG" & LastRowCusip).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy
    
                Call OpenAndManipulate
            
            Sheets(MyCell.Value).Select
            ActiveWindow.SelectedSheets.Delete
    
       ' SOME CODE HERE; ALL WORKS FINE 
    
    End Sub
    
    Sub OpenAndManipulate()
        Dim xl0 As New Excel.Application
        Dim xlw As New Excel.Workbook
        Set xlw = xl0.Workbooks.Open(ThisWorkbook.Path & "\DataDump.xlsx")
        xl0.Worksheets(1).Select
        
            LastRowSummary = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row + 3
            
            Range("A" & LastRowSummary).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            
        xlw.Save
        xlw.Close
        Set xl0 = Nothing
        Set xlw = Nothing
    End Sub

    The problem is that nothing gets saved to the DataDump.xlsx file.  How can I make this work?  Also, is this the fastest way of doing things?  Would it be easier to write to a CSV file?  I need to append the Range2 under Range1, and Renage3 under Range2, and so on and so forth.  That's why I need to find the LastRowSummary in the DataDump.xlsx file.

    If it's faster, or easier, to write to a CSV file, or even text file, I'm all for it.  I just need to keep appending all new data sets under the previous data set.  Finally, I' sure it's easier if the file that the data is being dumped to is open all the time, rather than opening and closing it each time.

    Thanks for your help and insight.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.



    • Edited by ryguy72 Thursday, January 14, 2016 8:40 PM
    Thursday, January 14, 2016 8:35 PM

Answers

  • I just figured it out.  This is how you do it.

            Set Rng = Range("BG5:BW74")
            Workbooks.Open Filename:="C:\Users\Desktop\DataDump.csv"
            LR = Cells(Rows.Count, "A").End(xlUp).Row + 3 'last used row
            Rng.Copy Range("A" & LR)
            ActiveWorkbook.Close True



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Proposed as answer by David_JunFeng Wednesday, January 20, 2016 12:57 AM
    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:43 AM
    Saturday, January 16, 2016 5:33 PM

All replies

  • Hi, ryguy72

    According to your description, I suggest that you could use Open Statement to input/output (I/O) to a "*.CSV" file, refer to below code:

    Sub AppendCSV()
      
        Dim rCell As Range
        Dim rRow As Range
        Dim sOutput As String
        Dim sFname As String, lFnum As Long
         
        Const sDELIM As String = ","
        
        'Open a text file to write
        sFname = "D:\April.csv"
        lFnum = FreeFile
        
        Open sFname For Append As lFnum    
        'Loop through the rows
        For Each rRow In Sheet1.UsedRange.Rows
            'Loop through the cells in the rows
            For Each rCell In rRow.Cells
                'use the cell value
                sOutput = sOutput & rCell.Value & sDELIM
        
             Next rCell
             'remove the last comma
             sOutput = Left(sOutput, Len(sOutput) - Len(sDELIM))
            'write to the file and reinitialize the variables
            Print #lFnum, sOutput
            sOutput = ""
            
        Next rRow
        
        'Close the file
        Close lFnum
        
    End Sub

    For more information, click here to refer about Open Statement

    and here to refer about Macro to Append Text from One Text File to Another


    • Proposed as answer by André Santo Friday, January 15, 2016 10:51 AM
    • Edited by David_JunFeng Saturday, January 16, 2016 6:29 AM
    Friday, January 15, 2016 5:49 AM
  • Thanks, but that doesn't write anything to the CSV file.  It's just like the sample that I posted.  It seems like the VBA script is almost forgetting what to do.  Here's my code now . . .


    ' SOME CODE HERE . . .
     
            Range("BG5:BG" & LastRowCusip).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy
    
            Call AppendCSV
    
    ' SOME CODE HERE . . . 
    
    Sub AppendCSV()
      
    
    ' YOUR CODE HERE . . . 
    
    End Sub
    I want to write the Selection.Copy part to the CSV, not the UsedRange.  How can I do that?



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, January 15, 2016 3:31 PM
  • >>>I want to write the Selection.Copy part to the CSV, not the UsedRange.  How can I do that?

    According to your description, you could modify like below:

    ' SOME CODE HERE . . . Range("BG5:BG" & LastRowCusip).Select Range(Selection, Selection.End(xlToRight)).Select set curRng=Selection Selection.Copy Call AppendCSV curRng Sub AppendCSV(ByVal currentRng As Range) ......

    Open sFname For Append As lFnum 'Loop through the rows For Each rRow In currentRng.Rows ...... Next rRow





    Saturday, January 16, 2016 5:49 AM
  • Thanks for that, but it still doesn't work.  It doesn't write anything to the CSV.  The focus gets to this line:

    Open sFname For Append As lFnum


    Then it skips back to the main Sub, which calls the sub named 'Sub AppendCSV()'

    Do you have any idea why it loses focus?  Even if I put the second Sub inside the first Sub, it still doesn't do anything.  When I look at  this line:

    For Each rRow In currentRng.Rows

    rRow is nothing.  It's totally empty. 

    Maybe this isn't possible.  I need to do some more research on this.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, January 16, 2016 5:07 PM
  • I just figured it out.  This is how you do it.

            Set Rng = Range("BG5:BW74")
            Workbooks.Open Filename:="C:\Users\Desktop\DataDump.csv"
            LR = Cells(Rows.Count, "A").End(xlUp).Row + 3 'last used row
            Rng.Copy Range("A" & LR)
            ActiveWorkbook.Close True



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Proposed as answer by David_JunFeng Wednesday, January 20, 2016 12:57 AM
    • Marked as answer by David_JunFeng Monday, January 25, 2016 5:43 AM
    Saturday, January 16, 2016 5:33 PM
  • >>>
    Then it skips back to the main Sub, which calls the sub named 'Sub AppendCSV()'

    Do you have any idea why it loses focus?  Even if I put the second Sub inside the first Sub, it still doesn't do anything.  When I look at  this line:

    For Each rRow In currentRng.Rows

    rRow is nothing.  It's totally empty. 
    <<<

    According to your description, do you get some errors? Could you provide more information about your issue, for example sample code, screenshot and errors etc., that will help us reproduce and resolve it.

    Thanks for your understanding.

    Monday, January 18, 2016 9:43 AM
  • There was no error, it just didn't write anything  to the CSV file.  This is what I came up with, and it works fine.

            LastRowCusip = ActiveSheet.Cells(ActiveSheet.Rows.Count, "BW").End(xlUp).Row
            Set Rng = Range("BG5:BW" & LastRowCusip)
            Workbooks.Open Filename:="C:\Users\Desktop\DataDump.csv"
            LR = Cells(Rows.Count, "A").End(xlUp).Row + 3 'last used row
            Rng.Copy Range("A" & LR)
            ActiveWorkbook.Close True

    Thanks to everyone who looked at this question and tried to help.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, January 18, 2016 2:45 PM
  • Hi, ryguy72

    Thanks for your effort and sharing your solution, it is helpful for other community when they meet the same question.

    Tuesday, January 19, 2016 8:25 AM