Link to home
Start Free TrialLog in
Avatar of andyb7901
andyb7901

asked on

MS Access - Exporting file to MKDIR folder.

Hello,
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.
Thanks
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
            Else
                strExport = False
            End If
            
            If strExport = True Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTable, strName, True ' Export in Excel format
            End If
        Next
 
End Sub

Open in new window

Avatar of andyb7901
andyb7901

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial