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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
strPath = "C:\Documents and Settings\" & Environ("Username") & "\Desktop\Reconciliation DB Exports\"
If FSO.FolderExists(strPath) = True Then
Kill strPath
End If