Automate import process of Excel-files

killerflappy · Mar 13, 2018

Hi Guys,

I have an Access database with order information. Per client I have an Excel file with the orders. I have an import button for the diffrent clients with an automated process for extracting the data to combine the orders.

One client is an expection. The deliver a lot of seperated files. These files are flaced in a (import)folder. We combine the files and then import them. Some files are for the next day. We can see this because in a column is the date for the next day.

I want to automate the import process of the Excel files. The end users clicks on a buttom and import the files that are in the importfolder.
I understand how to loop to the files.

How can I check if the file has the date of today (or earlier) in a column?
How can I move the imported files to a subfolder?

Ranman256 · Mar 13, 2018

manually, take 1 file, save it to a dummy file...c:\temp\File2Import.xls
in access, link this as an external table
build a query to add the data from the table to the internal data table.

now the code will run thru every file in a folder,
save it to the dummy file,
run the import query.
repeat.

change the folder in ImportXLFiles()
then run: ImportXLFiles

Code:

Public Sub ImportXLFiles
  ImportFilesInDir "c:\myfolder\folder\"
end sub


Public Sub ImportFilesInDir(ByVal pvDir)
Dim FSO, oFolder, oFile, oRX
Dim sTxt As String, sFile As String
Const kTARG = "C:\temp\File2Import.xlsx"

On Error GoTo errGetFiles

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)

If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"

For Each oFile In oFolder.Files
 If InStr(oFile.Name, ".xls") > 0 Then            'import file here
      sFile = pvDir & oFile.Name
      FileCopy sFile, kTARG
      DoCmd.OpenQuery "qaImportXL"
 End If
Next

endit:
Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
Exit Sub

errGetFiles:
If Err = 3265 Then     'catch error if NO Import table errors
  ' "no errors found"
   Resume Next
Else
  MsgBox Err.Description, , Err
End If
End Sub

Gasman · Mar 13, 2018

I would have thought you would need to open the workbook and inspect the column with the date to see if you want to copy and import that data?

Ranman256 · Mar 13, 2018

oops, then youd need to open it....
at top of code module, declare xl

Private XL As Excel.Application

Code:

 If InStr(oFile.Name, ".xls") > 0 Then            'import file here
      sFile = pvDir & oFile.Name
      If UseThisFile(sFile) then
         FileCopy sFile, kTARG
         DoCmd.OpenQuery "qaImportXL"
      endif
 End If

'then check for the date...

Code:

function UseThisFile(psFile) as boolean
Set XL = CreateObject("excel.application")
with xl
  .Visible = False
  .workbooks.open psFile
   UseThisFile = .range("a4").value < date() 
  .activeworkbook.close false
end with
end function

killerflappy · Mar 14, 2018

Ranman256 said:
oops, then youd need to open it....
at top of code module, declare xl

Private XL As Excel.Application

[/code]

The import is ok, but I can't get the check for the date.
Is't standard text in format dd-mm-yyyy (short notation like 17-3-2018) in Column J of the files.
Not sure if I declare the XL right and what you mean with "Private XL As Excel.Application"

It seems to use the code below, because it's taking a bit longer.
But it's not fill in any rows.

Code:

Function UseThisFile(psFile) As Boolean
Dim XL
Set XL = CreateObject("excel.application")
With XL
  .Visible = False
  .workbooks.Open psFile
   UseThisFile = .Range("J2").Value < Date
  .activeworkbook.Close False
End With
End Function

killerflappy · Mar 19, 2018

Still not sure if I declare the XL right and what you mean with "Private XL As Excel.Application"

The code works. I tested it by changing the J2 cells in 0 and 1.
Then: UseThisFile = .Range("J2").Value > 0
Only files with a 1 in J2 are imported.

Also I changed I tested with changing the format of J2 to a date-field.
This also works fine: UseThisFile = .Range("J2").Value < date

The only thing to do is set J2 as a date-field per file.
I tried this with a marco from Excel, but this didn't work.
How can I set the J2 as a date-field in the UseThisFile-function?

arnelgp · Mar 20, 2018

Use CDate() function.

Automate import process of Excel-files

killerflappy

Registered User.

Ranman256

Well-known member

Gasman

Enthusiastic Amateur

Ranman256

Well-known member

killerflappy

Registered User.

killerflappy

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread