Results 1 to 4 of 4
  1. #1
    niketowns is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    2

    Importing Excel Files to Access & Getting Excel Document Properties (Date Created)

    Hello!
    I am trying to created a database in Access that ingests excel files and populates the access database. One of the column parameters I would like is the "Date Created" Document property saved as a "date/time" data type.
    I found this Excel VBA function that works for an active workbook, but does not work for Access:
    "
    Public Function CreaDate() As Date
    CreaDate = ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
    End Function
    "

    Anyone know how to do this?


    Thanks!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You have to separate the Excel parts and the Access parts. Access and Excel are based on different object models.
    Is your intent to build and use an Access database? Or will it be some hybrid with parts of Excel and Access?

    You can certainly add field(s) to an Access table:
    CreatedDate
    CreatedBY
    ModifiedDate
    ModifiedBy

    and populate these from a form or as part of the import process.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    when user picks the file, you now have the file path,
    run it thru this function to put it in a text box on the form,
    txtCreate = getCreatedDate(txtFileName)

    then the query that imports, can pull this for the date.
    or post import , run an append query to update the Create Date.

    Code:
    Public Function getCreatedDate(ByVal pvFile)
        Dim fs, f, s
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFile(pvFile)
        getCreatedDate = f.DateCreated
    End Function
    

  4. #4
    niketowns is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    2
    Hi, thanks for the reply!
    Yes, the intent is to use an Access database, but the data ingested into the database is on Excel.

    How do I go about accessing those fields during the import process?
    What data type do I use for the field?

    i tried using the data type as "Date/Time" and in the default value box in the field properties menu i used "=CreatedDate()" and ran into this error: "A calculated column cannot be saved without a valid expression in the expression property"

    Thanks!

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

Similar Threads

  1. Importing Excel files using Access
    By nick243 in forum Import/Export Data
    Replies: 1
    Last Post: 07-25-2016, 12:11 PM
  2. Character Limit Importing Excel Document to Access
    By pterpumpkin in forum Import/Export Data
    Replies: 5
    Last Post: 08-09-2014, 11:19 PM
  3. Importing from several Excel files to Access.
    By cmdteardrops in forum Access
    Replies: 2
    Last Post: 10-02-2013, 03:33 PM
  4. regarding choking of access while importing excel files
    By ritimajain in forum Import/Export Data
    Replies: 1
    Last Post: 08-12-2013, 02:16 PM
  5. Replies: 10
    Last Post: 12-28-2012, 02:06 PM

Tags for this Thread

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