VBA to delete Excel data after import to Access? (1 Viewer)

[SazzleTWG]

Hi all,

I am currently trying to create an automated process to import data from several Excel files into Access daily, which I have managed to get working, however due to the ever increasing size of the excel files I need to delete the imported data from Excel after. I have managed to find a way of deleting the entire Excel file but I don't want to do that, just the data!

The code I have so far works to import the data:

Sub ImportCreditorData()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim strRangeName As String
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "G:\Creditor\Individual Performance\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tblCreditorTimesheets"
strRangeName = "TimesheetDataExport"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, strRangeName
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
strFile = Dir()
Loop
End Sub
Can anyone help me with the rest?

Many anticipatory thanks

Sazzle

 

[Bilbo_Baggins_Esq]

In your code, you are basically just importing and doing file management.

To actually edit the Excel file, you'll need to operate Excel from Access.

You'll first need to create an Excel Object within your Access code to be the source file.
Once you're in, the cell management functionality is basically the same as it would be if you were manipulating the cells (or their contents) from Excel based VBA.

However, personally, I'm not too sure about the wisdom of deleting source data in general. Finger pointing when data seems incorrect can get ugly in an office environment.

In similar situations in the past, I have adopted a file naming convention which allows for retention of the source data and prevents files from getting too swollen and covers my a$$.

 

[SazzleTWG]

Thanks for your reply. On further discussions I have managed to persuade the team I am creating the process for to manually create a new file monthly, reducing the risk of data loss and also making my life easier!