Format Excel / Import

Hey Guys,

I read several threads for suggestions, and most of them ask to import raw data into a temp table then append. But thought I see if this would be possible instead. The following codes imports all excel files in a folder, and extracts the date from the file name and puts that into a field in the table. And also moves the imported file to an archive folder after import.

Private Sub btnImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim path As String
Dim TheDate As Date
Dim fs

DoCmd.SetWarnings False
path = "C:UsersChinaboyDesktopData" ' Path that stores Historical Report Downloaded files.

'Loop through the folder & build file list
strFile = Dir(path & "*.xls")

While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend

'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If

'cycle through the list of files
For intFile = 1 To UBound(strFileList)
strFile = path & strFileList(intFile)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblAgentSummary", strFile, False

'Adds date to callDate field based on the date on file name.

TheDate = Mid(strFile, 54, 2) & "/" & _
Mid(strFile, 56, 2) & "/" & _
Mid(strFile, 58, 4)
CurrentDb.Execute "UPDATE tblAgentSummary SET callDate =" & "'" & TheDate & "' where callDate is null"

'set directory to look for next text file

Next intFile

'Moves imported file to Archive folder

Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile "C:UsersChinaboyDesktopData*.xls", "C:UsersChinaboyDesktopArchives"

DoCmd.SetWarnings True

End Sub

Is it possible for me to open each excel file in thebackground in the folder, format it before or during the import process. What I need to do is first delete rows 1 and 2, delete column B, D, F, and I. Then move to the last row with data then delete that row along with the previous 2.

I found this code that I thought may help, but I am not sure how to work it in with my exsiting code. Any suggestion and idea will be greatfully appreciated.

Private Sub Command0_Click()

Dim xlApp As New Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlwrkBk = xlApp.Workbooks.Open("C:Documents and Settings34036460 Project Files140 Excel AutomationXOR27NovB.xls")

Set xlSheet = xlwrkBk.Worksheets("OpenExchangeOrders27Nov")

xlSheet.Rows(1).Delete

xlwrkBk.Save
xlwrkBk.Close

Set xlSheet = Nothing
Set xlwrkBk = Nothing

xlApp.Quit

Set xlApp = Nothing

MsgBox "Done"

End Sub