Announcement

Collapse
No announcement yet.

Export Part Of Spreadsheet As .csv File

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Elmura
    started a topic Export Part Of Spreadsheet As .csv File

    Export Part Of Spreadsheet As .csv File

    Do you need to export part of a spreadsheet (in this example, A3:A5) into an external, yet uncreated spreadsheet, saved as a special file type (.csv in the example)?

    My pain is your gain.

    Code:
    Sub testexport() 
         '
         ' export Macro
         
         Range("A3:A5").Select 
        Selection.Copy 
        Workbooks.Add 
        ActiveSheet.Paste 
        ActiveWorkbook. SaveAs Filename:= _ 
        "C:\Documents and Settings\Simon\My Documents\Book2.csv" _ 
        , FileFormat:=xlCSV, CreateBackup:=False 
        Application.DisplayAlerts = False 
        ActiveWorkbook.Close 
        Application.DisplayAlerts = True 
         
    End Sub

  • Rich1954
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Hi Ivan

    No reason, just didn't think of it at the time.

    Rich

    Leave a comment:


  • ivanm101
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Hi everybody

    Why not use Format function?

    Format(Now(), "dd_mm_yyyy")

    Ivan

    Leave a comment:


  • Rich1954
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Originally posted by Elmura View Post
    Code looks good. Haven't tested it, and am a bit rusty.

    Note that I recall the Shell command being unreliable
    I've found the Shell command OK, especially for doing something simple like opening notepad. I believe the shell command is less reliable when used in a batch file to run another batch file.

    Try creating a new spreadsheet, populating it with a range of random numbers, and copying the code as is into the VB macro area.
    Select a range of cells, and run the macro and ... Bingo!

    Rich

    Leave a comment:


  • Dave Hawley
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Rich1954, thanks for sharing your code. Can I ask you to use code tags when posting code, thanks. [code]YOUR CODE HERE[/code]

    Leave a comment:


  • Elmura
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Code looks good. Haven't tested it, and am a bit rusty.

    Note that I recall the Shell command being unreliable

    Leave a comment:


  • Rich1954
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Oops, sorry, forgot to add my thanks to Elmura for getting me started.

    Also the CSV file is stored automatically in the same folder as the spreadsheet.

    Rich

    Leave a comment:


  • Rich1954
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Hi, I had to do something similar, and used the above for guidance. I've now completed the code, and decided to share it. Hope the comments make it understandable.
    I've put a command button on the sheet, and connected it to the macro, though you could just run the macro directly.
    I select a range, then press the button. The selected range is then copied to a csv file, and then opened in Notepad for onward selection into another application.
    I made a slight addition to help mkrprasad get started with the date in the file name.


    Code:
    Sub CmdButton_Click()
    On Error GoTo CmdButton_Click_Error
         '
         ' export Macro
         
        Selection.Copy
        
        'create new workbook
        Workbooks.Add
        ' Paste into it
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        
        ' Note I'm from the UK so my date format might be different - dd/mm/yyyy :)
        Yearstr = Mid(Date, 7, 4)
        Monthstr = Mid(Date, 4, 2)
        Daystr = Mid(Date, 1, 2)
        Datestr = Yearstr & "_" & Monthstr & "_" & Daystr
         
        FilenameStr = "\" & Datestr & "CSVpaste.txt"
       
        On Error GoTo Cont 'Ignore error if file cannot be deleted
        Kill [Path] & FilenameStr
    Cont:
       On Error GoTo CmdButton_Click_Error
       
       'Save workbook as csv text file
       ActiveWorkbook.SaveAs Filename:= _
       [Path] & FilenameStr _
        , FileFormat:=xlCSV, CreateBackup:=False
        
        'concatenate strings into Notepad string
        Notepad = "notepad.exe " & [Path] & FilenameStr
            
        Application.DisplayAlerts = False
        ActiveWorkbook.Close
        Application.DisplayAlerts = True
        'start notepad with csv file
        Shell Notepad, vbNormalFocus
    GoTo endit
    
    
    CmdButton_Click_Error:
    MsgBox ("Error-" & Error)
    
    
    endit:
    
    
    End Sub

    Regards


    Rich
    Last edited by Dave Hawley; January 5th, 2012, 14:08.

    Leave a comment:


  • Elmura
    replied
    Re: Export Part Of Spreadsheet As .csv File

    This takes me back, wow. I'm a bit rusty with VBA code but my guess would be to have a cell in spreadsheet showing current date & time, and the code referring to that cell for the filename. The how part would need someone with fresh VBA thinking.

    Leave a comment:


  • mkrprasad
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Thanks ... this is very helpful.

    Also, how to save the csv with file name to have present date and time in the format FC_Model_YYYY_MM_DD_HH_MM

    Leave a comment:


  • Dave Hawley
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Yes, thank you!

    Leave a comment:


  • Reafidy
    replied
    Re: Export Part Of Spreadsheet As .csv File

    Thanks For Sharing Elmura.

    Leave a comment:

Working...
X