+ Reply to Thread
Results 1 to 9 of 9

Ceating a csv file using vba

Hybrid View

  1. #1
    Registered User
    Join Date
    06-18-2014
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Ceating a csv file using vba

    Hi Guys.

    I need help with some code in vba.

    I have a worksheet called Upload DTB, with data in rows 5 to 2174 across columns B to F.

    I need to put a CommandButton on the sheet and, when clicked, it should create a .csv file of the data in the aforementioned cells. Also; if the file already exists on the desktop, a means of overwriting it.

    I know that I can export the sheet as an xlCSV file; but that doesn't specify the delimiter, so what I need is a way to create a non-xlcsv file (just a simple csv file) on my desktop with a 'pipe' delimiter, of the information contained in the range B5 to F2174.

    Any help would be greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Ceating a csv file using vba


    Hi,

    many samples in threads of this forum, just use the Advanced Search …

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524

    Re: Ceating a csv file using vba

    Try this,
    Sub Button1_Click()
        Dim MyFile As String, Str As String, MyDir As String
        'change the address to suite
        MyDir = "C:\Users\Dave\Downloads\"'Change this folder address 
        ChDir MyDir
        Application.ScreenUpdating = 0
        Application.DisplayAlerts = 0
        Dim wb As Workbook, ws As Worksheet, cRng As Range
        Dim rng As Range, sh As Worksheet
        Set sh = ActiveSheet
        Set rng = sh.Range("B5:F2174")
        Set wb = Workbooks.Add(xlWBATWorksheet)
        Set ws = wb.Sheets(1)
        Set cRng = ws.Range("A1")
        rng.Copy cRng
        wb.SaveAs (MyDir & cRng & ".csv")
    End Sub
    Change the Folder address in the code.
    See attached, wb.
    The workbooks saves what is in A1 in the new wb.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-18-2014
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Ceating a csv file using vba

    Thanks for that Dave; however, when I ran the code I got run time error 1004 "Method 'Save As' of object '_Workbook' failed"

    The only change I made to your code was "C:\Users\Acer\Downloads\".

    When I tried your example I made a similar change to the path and it worked; however, the resulting file was in xlCSV format and therefore opened in Excel. I'm trying to send a straightforward non-Excel specific file to be opened in Oracle or Access or just a plain old Notepad.

    Any ideas?

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Ceating a csv file using vba


    Put your data with column separators and line separators in a String variable and write it to a text file for example …

    Advanced Search -> CSV !

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524

    Re: Ceating a csv file using vba

    Ya, what is in A1? is it a saveable text?

  7. #7
    Registered User
    Join Date
    06-18-2014
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Ceating a csv file using vba

    It's ok Dave thanks. I checked through threads on here and found the following code that seems to work.

    It produces a txt file instead of a csv and I'd prefer it to go on the desktop rather than into the Temp folder; so if you have any amendments to achieve those ends I'd be very grateful.

    Private Sub CommandButton21_Click()
    
    Dim rng As Range, lRow As Long
    Dim stOutput As String, stNextLine As String, stSeparator As String
    Dim stFilename As String, stEncoding As String
    Dim fso As Object
     
    '-------------------------------------------------------------------------------------
    'CHANGE THESE PARAMETERS TO SUIT
    Set rng = ActiveSheet.UsedRange 'this is the range which will be written to text file
    stFilename = "C:\Temp\Upload.txt" 'this is the text file path / name
    stSeparator = "|" 'vbTab 'e.g. for comma seperated value, change this to ","
    stEncoding = "UTF-8" 'e.g. "UTF-8", "ASCII"
    '-------------------------------------------------------------------------------------
     
    For lRow = 1 To rng.Rows.Count
        If rng.Columns.Count = 1 Then
            stNextLine = rng.Rows(lRow).Value
        Else
            stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
        End If
        If stOutput = "" Then
            stOutput = stNextLine
        Else
            stOutput = stOutput & vbCrLf & stNextLine
        End If
    Next lRow
     
    Set fso = CreateObject("ADODB.Stream")
    With fso
        .Type = 2
        .Charset = stEncoding
        .Open
        .WriteText stOutput
        .SaveToFile stFilename, 2
    End With
    Set fso = Nothing
         
    End Sub

  8. #8
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Ceating a csv file using vba

    stFilename = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Upload.txt"

  9. #9
    Registered User
    Join Date
    06-18-2014
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Ceating a csv file using vba

    Thanks Kenneth; that works perfectly. Greatly appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating a biding tool using Excel Macro
    By kumargvm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2013, 01:09 AM
  2. Save as macro that specifies file type, file location and takes file name from three cells
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-13-2013, 10:09 PM
  3. Open 2nd file(CSV) from cell reference, copy columns to main file & close 2nd file
    By Langchop in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2013, 05:09 AM
  4. [SOLVED] Ceating multiple blank cells
    By memocm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2011, 04:02 PM
  5. Replies: 1
    Last Post: 02-24-2011, 06:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1