Importing data from excel with file name (1 Viewer)

[miacino]

I have this code which appropriately imports all files from excel in a folder with a certain wildcard name:

Private Sub Command4_Click()
Dim ImportDir As String, ImportFile As String
ImportDir = "G:\CCC\Payer Relations-Contracting\Payer Data Files\Connecticare\"
ImportFile = Dir(ImportDir & "CCI_CCMC_MULTI_ERDLYRPT_BU_FD01_PROD*")
Do While ImportFile <> ""
  DoCmd.TransferSpreadsheet acImport, , "Import_CTCare_ER_Daily", ImportDir & ImportFile, True, "A:AB"
  ImportFile = Dir
  Loop

End Sub

In the access table that the data is imported in to, there is a field called FILENAME. When importing the data I would like it to put which excel file name it is importing from into that access field.

Is that possible?

Thank you!

 

[theDBguy]

After the DoCmd line, try running an UPDATE query to add the filename to those records without it.

Sent from phone...

 

[miacino]

The below gives me "Syntax error in UPDATE statement"

Private Sub Command6_Click()

    Dim ImportDir As String, ImportFile As String
    ImportDir = "G:\Connecticut Children's Care Network\Payer Relations-Contracting\Payer Data Files\Connecticare\"
    ImportFile = Dir(ImportDir & "CCI_CCMC_MULTI_ERDLYRPT_BU_FD01_PROD*")
    Do While ImportFile <> ""
    DoCmd.TransferSpreadsheet acImport, , "Import_CTCare_ER_Daily", ImportDir & ImportFile, True, "A:AB"
    DoCmd.SetWarnings False
    sSql = "update table set Filename = 'importFile'"
    DoCmd.RunSQL sSql
    ImportFile = Dir
    
    Loop


End Sub

 

[Pat Hartman]

1. table needs to be the name of the table you are trying to update.
2. 'importFile' is a literal string and will change filename to "importfile" on all rows.

The update query needs to be more like.
sSql = "update youractualtablename set Filename = '" & importFile & "'"

The syntax of creating a string within a string can be confusing. You have a string which is the SQL statement and within that you need a string that concatenates the value stored in the variable importfile within the SQL string. You cannot pass a VBA variable name to the query engine. The queryengine doesn't know anything about VBA and it can't reach into your code to pick out the variable value. You can pass in form field references

sSql = "update table set Filename = Forms!yourformname!somecontrolname" -- this will fetch the value in the form control and concatenate it into the sql string. No single quotes are required because the form control is an object, not a variable and so Access knows how to treat it based on its data type which is assumed to be text unless you have bound it to a date or number field or used the format property if the control is unbound.

PS, are you sure you don't want the full path which would be ImportDir & ImportFile?

 

[Gasman]

Debug.print sSql before you even try to use it

ALLWAYS check it first.

 

[theDBguy]

sSql = "update table set Filename = 'importFile'"

After you fix that SQL statement, don't forget to add a WHERE clause to only update the records you just imported.

 

[miacino]

Thank you all. I modified and it brings in a filename, but it lists the LAST file name it imports for ALL the records, instead of the each file name that it pulls in. (pulling in data from several different files).

    Dim ImportDir As String, ImportFile As String
    ImportDir = "G:\Connecticut Children's Care Network\Payer Relations-Contracting\Payer Data Files\Connecticare\"
    ImportFile = Dir(ImportDir & "CCI_CCMC_MULTI_ERDLYRPT_BU_FD01_PROD*")
    Do While ImportFile <> ""
    DoCmd.TransferSpreadsheet acImport, , "Import_CTCare_ER_Daily", ImportDir & ImportFile, True, "A:AB"
    DoCmd.SetWarnings False
    sSql = "update [Import_CTCare_ER_Daily] set Filename = '" & ImportFile & "'"
    DoCmd.RunSQL sSql
    ImportFile = Dir
    
    Loop

 

[theDBguy]

it brings in a filename, but it lists the LAST file name it imports for ALL the records, instead of the each file name that it pulls in.

I guess you didn't see my warning in post #6. Try:

sSql = "update [Import_CTCare_ER_Daily] set Filename = '" & ImportFile & "' where filename is null"

 

[miacino]

Thank you. One additional question. I would like to include the excel creation date (the date that excel file was created) in another field.
Same concept, but I can't find what to call the field that Access will recognize to pull in this date.

 

[Gasman]

Might have to use the Filesystem Object for that?

FileSystemObject object

Office VBA reference topic

learn.microsoft.com

File object

Office VBA reference topic

learn.microsoft.com

 

[theDBguy]

Thank you. One additional question. I would like to include the excel creation date (the date that excel file was created) in another field.
Same concept, but I can't find what to call the field that Access will recognize to pull in this date.

View attachment 110422

Did you want date created or modified? Perhaps the FileDateTime() function is good enough for you.

FileDateTime function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[miacino]

It does not seem to recognize FileDateTime

    Dim ImportDir As String, ImportFile As String
    ImportDir = "G:\Connecticut Children's Care Network\Payer Relations-Contracting\Payer Data Files\Connecticare\"
    ImportFile = Dir(ImportDir & "CCI_CCMC_MULTI_ERDLYRPT_BU_FD01_PROD*")
    Do While ImportFile <> ""
    DoCmd.TransferSpreadsheet acImport, , "Import_CTCare_ER_Daily", ImportDir & ImportFile, True, "A:AB"
    DoCmd.SetWarnings False
    sSql = "update [Import_CTCare_ER_Daily] set Filename = '" & ImportFile & "' where filename is null"
    sSql2 = "update [Import_CTCare_ER_Daily] set FileCreatedDate = '" & FileDateTime & "' where FileDateTime is null"
    DoCmd.RunSQL sSql
    DoCmd.RunSQL sSql2
    ImportFile = Dir
    
    Loop

With error message:

 

[Gasman]

Did you even look at the link theDBguy posted?

 

[miacino]

Hi Gasman,
I did see the link DBguy posted (thank you both). I tried to incorporate that into my code, but it didn't work. Either way - I can't seem to make it work. Apologies - I am a very new to VB and SQL - come a long way - but still get tricked up on some of this as I don't use it everyday. Any further guidance would help. - Michele

    Dim ImportDir As String, ImportFile As String
    Dim MyStamp
    ImportDir = "G:\Connecticut Children's Care Network\Payer Relations-Contracting\Payer Data Files\Connecticare\"
    ImportFile = Dir(ImportDir & "CCI_CCMC_MULTI_ERDLYRPT_BU_FD01_PROD*")
    Do While ImportFile <> ""
    DoCmd.TransferSpreadsheet acImport, , "Import_CTCare_ER_Daily", ImportDir & ImportFile, True, "A:AB"
    DoCmd.SetWarnings False
    Set MyStamp = FileDateTime(ImportFile)
    sSql = "update [Import_CTCare_ER_Daily] set Filename = '" & ImportFile & "' where filename is null"
    sSql2 = "update [import_CTCare_ER_Daily] set FileCreatedDate = '" & MyStamp & "' where MyStamp is null"
    DoCmd.RunSQL sSql
    DoCmd.RunSQL sSql2
    ImportFile = Dir
    Loop

Run-time error 53: File not found.

 

[Gasman]

And on which line do you get that error?, what statement?
The FileDateTime() function requires the full path of the file you are trying to get the details from?
Why would you think you need the ImportDir and ImportFile for the TransferSpreadSheet and not for any other function? :-(
How is Access meant to find the file if you do not give it the full path?

 

[Gasman]

Add a debug.print ImportFile after this line
Do While ImportFile <> ""
and post back what it shows. You should see it just gives you the name in the folder. That is useless without the path if you need to find it or Access needs to find it again.
FileDateTime() is not going to be able to find it like that.

You need the full path of the file, not just it's name. So you need ImportDir & ImportFile, both to store in the table and to find the file.
If you do not store the folder path with the filename, you have to keep it somewhere else you will never find the files.

Start gettiing used to adding debug.print to check your variables or set a breakpoing and walk though your code and hover over the variables.
That will also show you the path of your code and you will see what it is doing not what you think it is doing.

It might be slow, but at least you will get there, rather than just throwing code together and hoping it will work.

I have just run this code copied from yours for just one file.

Sub TestDir()
Dim ImportDir As String, ImportFile As String
    Dim DtStamp As Date
    ImportDir = "F:\Temp\DB\" 'G:\Connecticut Children's Care Network\Payer Relations-Contracting\Payer Data Files\Connecticare\"
    ImportFile = Dir(ImportDir & "*.accdb")  'CCI_CCMC_MULTI_ERDLYRPT_BU_FD01_PROD*")
    Do While ImportFile <> ""
    Debug.Print ImportFile
    'DoCmd.TransferSpreadsheet acImport, , "Import_CTCare_ER_Daily", ImportDir & ImportFile, True, "A:AB"
    DoCmd.SetWarnings False
    DtStamp = FileDateTime(ImportDir & ImportFile)
    Debug.print dtStamp
    'sSql = "update [Import_CTCare_ER_Daily] set Filename = '" & ImportFile & "' where filename is null"
    'sSql2 = "update [import_CTCare_ER_Daily] set FileCreatedDate = '" & MyStamp & "' where MyStamp is null"
    'DoCmd.RunSQL sSql
    'DoCmd.RunSQL sSql2
    ImportFile = Dir
    Loop
End Sub

and that produced
1 PCS Cost Seg Prog 32bit3.accdb
13/07/2023 17:51:06

You do not use Set to assign values to variables, that is for objects