Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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

0
andyb7901
Asked:
andyb7901
1 Solution
 
andyb7901Author Commented:
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
0
 
rockiroadsCommented:
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

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now