Format Excel / Import

[china99boy]

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:\Users\Chinaboy\Desktop\Data\" ' 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:\Users\Chinaboy\Desktop\Data\*.xls", "C:\Users\Chinaboy\Desktop\Archives\"
    
  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 Settings\340364\60 Project Files\140 Excel Automation\ZXOR27NovB.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

 

[georgedwilkinson]

Open a copy of one of the Excel files and turn on "record macro". Delete the stuff you were talking about and end the record macro. Look at the recorded macro and integrate it into the code snippet above (where xlSheet.Rows(1).Delete is now).

Modify that subroutine to take a file name and put the parameter name in the Set xlwrkBk = xlApp.Workbooks.Open("C:\Documents and Settings\340364\60 Project Files\140 Excel Automation\ZXOR27NovB.xls") line, like this:
Set xlwrkBk = xlApp.Workbooks.Open(p_strMyIncomingFileName).

Call that subroutine from within your filename iteration loop.

I do this exact thing in one of the systems under my control. Never have a problem.

 

[china99boy]

This what I have so far. I placed the code in a button to test, but nothings seems to happen. Am on the right path. Does it at least make a little bit of sense. LOL.

Option Compare Database

Private Sub btnFormatExcel_Click()
Dim xlApp As New Excel.Application
    Dim xlwkBk As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim NextRow As Long
    
    path = "C:\Users\Chinaboy\Desktop\Data\"
    strFile = Dir(path & "*.xls")
    
    
    Set xlwkBk = xlApp.Workbooks.Open(strFile)

    Set xlSheet = xlwkBk.Worksheets("sheet1")
    
    
    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Range("B:B,F:F,I:I,L:L,O:O,R:R").Select
    Range("R1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    NextRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & NextRow).Resize(5, 15).Delete
    
    
    xlwkBk.Save
    xlwkBk.Close
    
    Set xlSheet = Nothing
    Set xlwkBk = Nothing
    
    xlApp.Quit
    
    Set xlApp = Nothing
    
    MsgBox "Done"


End Sub

 

[andrey_anikin]

Gentlemen,

I'm now trying to cope with a somewhat similar task - need to format an Excel file I export data to. I have an issue: when trying to declare things like Set xlapp = New Excel.Application I get an error message 'User-denied type not defined' - in a word, Access seems to be unfamiliar with such an approach to Excel files. On the other hand, it looks like something quite casual for other guys here - which implies the approach is correct. What am I doing wrong?

 

[georgedwilkinson]

You need to include the Excel library in your project. From the VBA editor, select "Tools|References" and find the most recent Microsoft Excel object library. In my case, it is "Microsoft Excel 10.0 Object Library". Click on the box and click OK. You should then be able to do Excel automation stuff.

 

[andrey_anikin]

Cool, thank you very much

 

[georgedwilkinson]

No problem. Glad we could help.