Results 1 to 4 of 4
  1. #1
    m698322h is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    2

    Access 2013 Macro to clear CSV file

    Good Afternoon,

    I have a CSV file that gets populated via scripts that run when people log in to their computer. I am importing these files into access for further processing. I have created a macro to import the files but I need to clear the files once this import is complete.

    There is one caveat. I need to clear everything except the first line of the CSV as this line contains the column names.

    I need help creating this macro and any help is appreciated.

    I am a newbie to Access.

    Any help appreciated.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm a little confused on how many CSV files there are.

    Easiest is to open the file for output (which overwrites the file) and write the header. But I don't think you can do it using a macro; would have to be VBA.

  3. #3
    m698322h is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    2
    Ok, sounds rather simple. How would I utilize VBA to clear the contents of these CSV files then rewrite the header. I am very new to Access as this is my first time using it. If I know how to clear one CSV file and re write the header, it is trivial to get the rest working.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You don't "clear the contents". You overwrite the file.
    First you open the file and read the first line (the header).
    Then you create a new file with the same name and write the header which overwrites the file, effectively "clearing the contents".

    In the IDE, go to TOOLS/References. Scroll down an put a check box in front of 'Microsoft Office x.x Object Library".
    In A2010, the x.x number is 14.0. Maybe A2013 is 15.0??

    Then use the following code. I named the sub "ClearCSV', but you can use your button name or rename it to whatever you want.
    Code:
    ' requires a Reference to Microsoft Office x.x Object Library
    Public Sub ClearCSV()
    '-----begin code ----------------------
        Dim fd As Office.FileDialog
        Dim strFileName As String
        Dim HeaderText As String
        Dim TF As Integer
    
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        
        fd.Filters.Clear
        'only show CSV files
        fd.Filters.Add "CSV Files", "*.csv"
    
        fd.InitialFileName = CurrentProject.Path
        'MsgBox CurrentProject.Path
        
        'select a file
        If fd.Show Then
            strFileName = fd.SelectedItems(1)
        Else
            MsgBox "No File Selected! Goodbye....."  '<<-- you can change to your message
            Exit Sub
        End If
    
        'open the file and read in the header
        TF = FreeFile
        Open strFileName For Input As #TF
        Line Input #TF, HeaderText    ' Read header line into variable.
        Close #TF
    
        '  overwrite the file and write the header
        TF = FreeFile
        Open strFileName For Output As #TF
        'write the header
        Print #TF, HeaderText
        Close #TF
    
        'clean up
        Set fd = Nothing
    '-----end code -------------------------
    End Sub
    Note: there is no error handling in this routine....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-04-2015, 02:04 PM
  2. PDF File content display on Access 2013 form
    By saleemsadique in forum Access
    Replies: 7
    Last Post: 02-07-2014, 06:36 PM
  3. How can i clear a query with vba or macro?
    By omeara4pheonix in forum Macros
    Replies: 7
    Last Post: 06-18-2013, 09:05 PM
  4. XML file opens in excel 2010/2013 correctly but not in Access
    By flebber in forum Import/Export Data
    Replies: 1
    Last Post: 05-21-2013, 09:23 PM
  5. Macro to add and clear fields
    By Skroof in forum Access
    Replies: 1
    Last Post: 05-14-2012, 01:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums