Results 1 to 6 of 6
  1. #1
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43

    Changing the column format for all Excel-files in a folder

    Hi Guys,



    I have a folder with Excel-files to import.
    Before importing I want to change the standard format of colum J of the first sheet (temp_xls) to a DateValue.

    Access then can check the date. I don't want to import files with dates of tomorrow and later.

    How can I change the Column J format for all .xls files in a folder.

    I tried working with:
    For each
    Dim XL as Excel.Application (including activating Microsoft Excel 16 library).

    Also I searched, but can't get it to work.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the code below will load every xl book in the folder,
    it joins the book as an external table,
    then a query will check col J (it needs a name, not a col#) for the date < today
    if so, import.
    (you do not need: Dim XL as Excel.Application)

    you DO need a query:
    qaImportXLwithPriorDatesOnly", to import records whos date < today

    usage:
    ImportFilesInDir "your folder here"
    Code:
    Public Sub ImportAllFiles()
    ImportFilesInDir "c:\temp\"
    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"
    Const kTBL = "xlFile2Import"
    Dim vDat As Date
    
    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 = oFile
        
              'remove old xl table
          CurrentDb.TableDefs.Delete kTBL
              'connect to next workbook
          DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, kTBL, sFile, True
             
         'if you dont want to impor the wb at all if any record > today
                vDat = Dlookup("[dateFld]",kTBL, "[DateFld]>=#" & date() & "#")
                if IsNull(vDat) then  'no dates > today, so import
                       DoCmd.OpenQuery "qaImportXLwithPriorDatesOnly"
                endif
            
         'else
            '   'query to import ONLY records with date < today
          ' DoCmd.OpenQuery "qaImportXLwithPriorDatesOnly"
     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

  3. #3
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    This is genius!

    To complete this: Is there a way to move the imported files to a subdirectory?

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Name sFile as "c:\folder" & ofile.name

    (the website wont let me add the last backslash to the target folder)

  5. #5
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    The imports works fine. But I don't understand the lines below.
    vDat = DLookup("[date]", kTBL, "[date]>=#" & Date & "#") 'I see vDate gets the date from the J column of the file
    If IsNull(vDat) Then 'no dates > today, so import

    I changed this to (and this works)
    vDat = DLookup("[date]", kTBL, "[date]>=#" & Date & "#")
    If vDat < Date Then

    Also I didn't filter the date in my query, because the import will be done via: If vDat < Date Then

    What is the difference?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be warned/aware:
    "Date" is a reserved word (and a built in function/command) in Access and shouldn't be used as an object name. You will save yourself grief in the long run if you change the field name.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import 4 Different Excel Files from One Folder
    By Chaser in forum Import/Export Data
    Replies: 2
    Last Post: 10-20-2017, 10:17 AM
  2. Replies: 10
    Last Post: 09-09-2015, 03:25 AM
  3. Replies: 1
    Last Post: 05-15-2015, 10:58 AM
  4. Replies: 17
    Last Post: 06-04-2013, 07:36 PM
  5. Replies: 1
    Last Post: 06-20-2012, 12:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums