MS Access - Exporting file to MKDIR folder.

Posted on 2009-04-17
Last Modified: 2013-11-28
I have some code which creates a new folder and then export certain files to it. However, im getting stuck on the line of code which removes the folder if it already exists. I get the error Object variable or With block variable not set. I dont know why. I have the same piece of code which deletes a file, but it wont delete a folder? all I want to be able to do is create a folder ( delete if it already exists), and then export certain tables into that folder.
Public Sub MakeFolder()

Dim strTable As String

Dim strPath As String

Dim strExport As Boolean

Set dbs = CurrentDb

Set Tdfs = dbs.TableDefs

    strPath = "C:\Documents and Settings\" & Environ("Username") & "\Desktop\Reconciliation DB Exports\"

        If FSO.FolderExists(strPath) Then

            Kill strPath

        End If

        MkDir strPath

        For Each tdf In Tdfs

            If tdf.SourceTableName = "tbl_Historical_SPN_Trades" Then

                strTable = "tbl_Historical_SPN_Trades"

                strName = strPath & "Historical SPN Trades.xls"

                strExport = True

            ElseIf tdf.SourceTableName = "tbl_SKYC_Clients" Then

                strTable = "tbl_SKYC_Clients"

                strName = strPath & "SKYC Clients.xls"

                strExport = True

            ElseIf tdf.SourceTableName = "tbl_Historical_Break" Then

                strTable = "tbl_Historical_Break"

                strName = strPath & "Historical Breaks.xls"

                strExport = True


                strExport = False

            End If


            If strExport = True Then

                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTable, strName, True ' Export in Excel format

            End If


End Sub

Open in new window

Question by:andyb7901

    Author Comment

    I seem to have resolved the issue by forgetting to put in Set FSO = New FileSystemObject. However, I am now getting another issue. When it comes to delete the folder I get File not Found? any reason why? It must have foudn the file in order to trigger the kill?

    strPath = "C:\Documents and Settings\" & Environ("Username") & "\Desktop\Reconciliation DB Exports\"

            If FSO.FolderExists(strPath) = True Then
                Kill strPath
            End If
    LVL 65

    Accepted Solution

    Why are you deleting the folder. If you have calculated strName, why dont you just check to see if that file exists then just delete that file instead?

    Anyways, use this to delete your folder since you are using fso

    fso.DeleteFolder strPath

    I always thought kill was for files


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now