Announcement

Collapse
No announcement yet.

Export Part Of Spreadsheet As .csv File

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

  • 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

  • #2
    Re: Export Part Of Spreadsheet As .csv File

    Thanks For Sharing Elmura.
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: Export Part Of Spreadsheet As .csv File

      Yes, thank you!

      Comment


      • #4
        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

        Comment


        • #5
          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.

          Comment


          • #6
            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.

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #9
                  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]

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Export Part Of Spreadsheet As .csv File

                      Hi everybody

                      Why not use Format function?

                      Format(Now(), "dd_mm_yyyy")

                      Ivan

                      Comment


                      • #12


                        Re: Export Part Of Spreadsheet As .csv File

                        Hi Ivan

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

                        Rich

                        Comment

                        Working...
                        X