Results 1 to 7 of 7
  1. #1
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58

    Access VBA to save format text files to csv

    I'm attempting to set up automated functions to automatically download daily text files received by email (done), and then import them into a very basic database. Unfortunately the txt files are tab delimited and after fruitless research into how to automate importing multiple tab delimited text files into Access, I have decided it will be easier to convert all the txt files to csv files and then import them. It makes sense to code this in Access rather than having an Excel file just to hold the code for opening and converting multiple text files, but I have some issues.

    The following code works fine in Excel and does exactly what I need it to do. It opens every text file in a specified folder, one by one, converts them to csv files and saves them as such in the same folder with its same name:

    Sub ConvertFiles()
    Dim sName As String, sPath As String
    sPath = "P:\Shared Works\Catch Reports" '<== change to reflect your folder. Make sure it ends with a "" character
    sName = Dir(sPath)
    Do While sName <> ""
    If LCase(Right(sName, 3)) = "txt" Then
    Workbooks.OpenText Filename:=sPath & sName, _
    Origin:=437, _
    StartRow:=1, _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=True, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=False, _
    TrailingMinusNumbers:=True[/COLOR]
    ActiveWorkbook.SaveAs Filename:=sPath & Left(sName, Len(sName) - 4) & ".csv", _
    FileFormat:=xlCSV, _
    CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=False
    End If
    sName = Dir()
    Loop
    Kill "P:\Shared Works\Catch Reports\*.txt"
    End Sub

    However when I tried to adapt it to work with Access, it didn't work. The issue is in the formatting "sub" commands, because if I removed all of them the code runs without error. It opens each file and saves it with a csv extension but they haven't been converted:

    Public Function runExcelMacro2()

    Dim MyXL As Object
    Dim sName As String, sPath As String

    Set MyXL = CreateObject("Excel.Application")
    sPath = "P:\Shared Works\Catch Reports" '<== change to reflect your folder. Make sure it ends with a "" character
    sName = Dir(sPath)

    With MyXL
    Do While sName <> ""


    If LCase(Right(sName, 3)) = "txt" Then
    .Workbooks.Open FileName:=sPath & sName, _
    Origin:=437, _
    StartRow:=1, _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=True, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=False, _
    TrailingMinusNumbers:=True
    .ActiveWorkbook.SaveAs FileName:=sPath & Left(sName, Len(sName) - 4) & ".csv", _
    FileFormat:=xlCSV, _
    CreateBackup:=False
    .ActiveWorkbook.Close SaveChanges:=False
    End If
    sName = Dir()
    Loop
    End With

    Kill ("P:\Shared Works\Catch Reports\*.txt")
    End Function


    Can anyone advise what the issue might be and how to fix it? Would appreciate any advice!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Please post code within CODE tags to retain indentation and readability. Also, then will not drop things like the "\" character at end of path strings or in with a "\" character

    Don't use Active______ syntax, use explicit referencing. Declare and set a workbook object. This simplified code works for me:

    Code:
    Dim MyXL As Object, MyWkb As Object
    Dim sName As String, sPath As String
    
    Set MyXL = CreateObject("Excel.Application")
    sPath = "C:\Users\Owner\June\Forums\" '<== change to reflect your folder. Make sure it ends with a "\" character
    sName = Dir(sPath)
    
    With MyXL
    Do While sName <> ""
        If LCase(Right(sName, 3)) = "txt" Then
            Set MyWkb = MyXL.Workbooks.Open(sPath & sName)
            With MyWkb
                .SaveAs filename:=sPath & Left(sName, Len(sName) - 4) & ".csv", _
                    FileFormat:=xlCSV, _
                    CreateBackup:=False
                .Close SaveChanges:=False
            End With
        End If
        sName = Dir()
    Loop
    End With



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Quote Originally Posted by June7 View Post
    Please post code within CODE tags to retain indentation and readability. Also, then will not drop things like the "" character at end of path strings or in with a "" character

    Don't use Active______ syntax, use explicit referencing. Declare and set a workbook object. This simplified code works for me:

    Code:
    Dim MyXL As Object, MyWkb As Object
    Dim sName As String, sPath As String
    
    Set MyXL = CreateObject("Excel.Application")
    sPath = "C:\Users\Owner\June\Forums\" '<== change to reflect your folder. Make sure it ends with a "\" character
    sName = Dir(sPath)
    
    With MyXL
    Do While sName <> ""
        If LCase(Right(sName, 3)) = "txt" Then
            Set MyWkb = MyXL.Workbooks.Open(sPath & sName)
            With MyWkb
                .SaveAs filename:=sPath & Left(sName, Len(sName) - 4) & ".csv", _
                    FileFormat:=xlCSV, _
                    CreateBackup:=False
                .Close SaveChanges:=False
            End With
        End If
        sName = Dir()
    Loop
    End With



    Thank you June7, much appreciated, and noted your comments about the tags.

    Unfortunately I'm still getting an error. The error is "Run-time error 1004: SaveAs method of Workbook class failed". The problem are the parameters (FileFormat and CreateBackup) - if I remove them then the code runs without error, but all it's doing is saving each file as a csv file, but not actually converting it.

    The Option is set on Option Compare Database which might not be the right one. Should it be Option Explicit? if I change the Option I get a "variable not defined" error on the FileFormat:= XlCSV

    Appreciate any advice you have

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Because you are using late binding by declaring the variables as Object the xlCSV is not available, so use its value instead (6)
    https://learn.microsoft.com/en-us/of...l.xlfileformat

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The code worked for me probably because I have Excel library active in VBA References.

    More about binding: https://stackoverflow.com/questions/...ce-differences

    Should have both:
    Option Compare Database
    Option Explicit
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Quote Originally Posted by Gicu View Post
    Because you are using late binding by declaring the variables as Object the xlCSV is not available, so use its value instead (6)
    https://learn.microsoft.com/en-us/of...l.xlfileformat

    Cheers,
    Thank you Vlad... I tried this but it still didn't like it, I don't know why. In the end I did what I should've done right from the start if I'd understood it well enough, and added excel references to my library. Then June's code worked perfectly!

  7. #7
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Thank you June! I couldn't make it work but instead I did the obvious and added excel references to the library and then your code worked perfectly. I should have started with that

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

Similar Threads

  1. Newbie - saving Access files in FAIR format
    By fionabeglane in forum Import/Export Data
    Replies: 5
    Last Post: 12-20-2019, 12:31 PM
  2. Replies: 12
    Last Post: 03-27-2014, 04:49 AM
  3. Replies: 3
    Last Post: 05-16-2012, 02:56 PM
  4. Replies: 2
    Last Post: 03-01-2012, 12:21 PM
  5. Import Text Files without access
    By 95DSM in forum Import/Export Data
    Replies: 1
    Last Post: 09-10-2010, 04:36 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