Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Excel file name in Access Table

    Dear Seniors,

    Recently I was goggling to import multiple excel files in Access single table. I got the following code for importing Multiple excel files from a folder to access table and it works fine. However I would like to have excel file name also in access table. I had done it in excel but however I am not able to do the same in access.

    Here I have pasted the code I got it for import. Could you please modify the code so that I can get the File name also in access table.

    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
    Dim filename As String
    Dim path As String

    DoCmd.SetWarnings False
    path = "C:\Users\Vadivelan\Desktop\3. EXCEL FILES - Copy\"

    '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)
    filename = path & strFileList(intFile)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Check", filename, True

    Next intFile
    DoCmd.SetWarnings True

    End Sub

    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You already have these file names: they're in the array strFileList (although I don't see any reason why you use that array in the import process but that's another story). What do you want to do with them?
    Have a nice day!

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Quote Originally Posted by Sinndho View Post
    You already have these file names: they're in the array strFileList (although I don't see any reason why you use that array in the import process but that's another story). What do you want to do with them?
    I want that file name to appear in the Access table (which was created during the import process).

    i.e. When I run this module A new access table named "Check" was created with the fields available in excel file. Now apart from this I want to have one more field to show the file name (StrFilelist) in the Access Table.

    Also is it possible to put restriction to import the Duplicate files in the code,
    (i.e.) if I select the file imported already import again then it should say that "StrFileList Already imported" etc.,

    Thanks and Regards
    R. Vadivelan

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    I want that file name to appear in the Access table (which was created during the import process).

    i.e. When I run this module A new access table named "Check" was created with the fields available in excel file. Now apart from this I want to have one more field to show the file name (StrFilelist) in the Access Table.
    This is not possible while using the TransferSpreadsheet method. With this method, you get in the imported table what was in the source sheet, nothing more. If you want to add a column to the table created during the importation process, you need to do so after importing the Excel sheet.

    Quote Originally Posted by velu130486 View Post
    Also is it possible to put restriction to import the Duplicate files in the code,
    (i.e.) if I select the file imported already import again then it should say that "StrFileList Already imported" etc.
    From the moment you now the name of the table that will be created by the TransferSpreadsheet method, you can always check whether that table already exists or not and, according to the answer, chose to skip the importation process. You can check for the existence of a table using:
    Code:
    Public Function Exist(ByVal TableName As String) As Boolean
    
        Exist = DCount("*", "MSysObjects", "Name='" & TableName & "'")
    
    End Function
    Have a nice day!

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Quote Originally Posted by Sinndho View Post
    This is not possible while using the TransferSpreadsheet method. With this method, you get in the imported table what was in the source sheet, nothing more. If you want to add a column to the table created during the importation process, you need to do so after importing the Excel sheet.

    Could you please explain me how can I do this in Access?

    From the moment you now the name of the table that will be created by the TransferSpreadsheet method, you can always check whether that table already exists or not and, according to the answer, chose to skip the importation process. You can check for the existence of a table using:
    Code:
    Public Function Exist(ByVal TableName As String) As Boolean
    
        Exist = DCount("*", "MSysObjects", "Name='" & TableName & "'")
    
    End Function
    If I am not mistaken, the above function is to check whether the Table is already existing or not? But I can identify the same easily since I have only 10 tables. My objective is to avoid the duplicate excel workbooks getting imported into the Access Table.

    For (Ex) For the First time I am importing 5 files say
    File1
    File2
    File3
    File4
    File5

    In second import I am importing
    File5
    File6
    File7

    Then It should not import File 5 for the second time or it can overwrite the File5 which was already imported. I hope to explain you clearly.

    Thanks and Regards
    R. Vadivelan

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    If I am not mistaken, the above function is to check whether the Table is already existing or not? But I can identify the same easily since I have only 10 tables. My objective is to avoid the duplicate excel workbooks getting imported into the Access Table.
    If the same imported workbook yields the same table name, this is equivalent: if the table exists then the workbook that yielded that table was already imported.
    Have a nice day!

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Herewith I had enclosed my sample database which I am using for importing multiple excel files from a particular location. This code is working fine, However I have one missing data i.e ExcelFileName.

    Kindly help me to get the Excel file Name in the Table field, since it is not possibly to do it manually and its time consuming.

    And it is very much important for me to track the status of the document.

    Thanks and regards
    R. Vadivelan
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry, I'm at home where only Acc 2003 is installed. Anyway, it should not be very difficult:
    Code:
    Sub AddExcelFileName(ByVal TableName As String, ByVal XLFileName As String)
    
        Const c_DDL As String = "ALTER TABLE @T ADD COLUMN XLFileName TEXT(128);"
        Const c_SQL As String = "UPDATE @T SET XLFileName = '@X';"
        
        Dim strSQL As String
        
        strSQL = Replace(c_DDL, "@T", TableName)
        CurrentDb.Execute strSQL, dbFailOnError
        strSQL = Replace(Replace(c_SQL, "@T", TableName), "@X", XLFileName)
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    I am trying to convert my Database to 2003 format, but It ended in failure since my Code uses the function which are not compatible with 2003.

    So Could you explain further how to run this code in order to get the excel file name in the Table/Query.

    Thanks and Regards
    R. Vadivelan

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Call it immediately after importing each table.
    Have a nice day!

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Could you please edit the following code in order to get the excel file name in my table. I don't know where I have to call the Function and I am getting argument not optional error.

    Private Sub Command2_Click()
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
    Dim filename As String
    Dim path As String

    DoCmd.SetWarnings False
    path = "D:\Access MDR\MDR\"

    '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)
    filename = path & strFileList(intFile)

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "CPYTransmittal", filename, True

    Next intFile

    DoCmd.SetWarnings True

    End Sub


    Sub AddExcelFileName (ByVal CPYTransmittal As String, ByVal ExcelFileName As String)

    Const c_DDL As String = "ALTER TABLE @T ADD COLUMN ExcelFileName TEXT(128);"
    Const c_SQL As String = "UPDATE @T SET ExcelFileName = '@X';"

    Dim strSQL As String

    strSQL = Replace(c_DDL, "@T", CPYTransmittal)
    CurrentDb.Execute strSQL, dbFailOnError
    strSQL = Replace(Replace(c_SQL, "@T", CPYTransmittal), "@X", ExcelFileName)
    CurrentDb.Execute strSQL, dbFailOnError

    End Sub

    Is there any other method to import the multiple excel files to access from particular location with excel file names?

    If is possible, please modify the code so that I will use your code for my project use. Any thanks for your previous helps and it works like a gem in my project use.

    Thanks and Regards
    R. Vadivelan

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Sinndho View Post
    Call it immediately after importing each table.
    Then it should be:
    Code:
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "CPYTransmittal", filename, True
        AddExcelFileName "CPYTransmittal", FileName
    Next intFile
    Notice that, as your code is written, you import all spreadsheets in the same table [CPYTransmittal] as the syntax of the method is:
    Code:
    DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
    see: DoCmd.TransferSpreadsheet Method (Access)
    Have a nice day!

  13. #13
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Yes, My Objective is to get all the spreadsheets in the same table.

    If it is not possible kindly advice whether import as individual spreadsheets and merging the Tables are possible.

    I had updated the code as per your instruction, But I am getting the run time error 3380 saying Field ExcelFilename is already existing the Table.

    [QUOTE=Sinndho;6615894]Then it should be:
    Code:
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "CPYTransmittal", filename, True
        AddExcelFileName "CPYTransmittal", FileName
    Next intFile
    Notice that, as your code is written, you import all spreadsheets in the same table [CPYTransmittal] as the syntax of the method is:
    Code:
    DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
    I closed that window and opened my table and one found that 1 Spreadsheet is extracted with file name and second spreadsheet partial details are imported.

    I had enclosed the screen shots for your reference.

    Kindly help me to sort out this issue

    Thanks and Regards
    R. Vadivelan
    Attached Thumbnails Attached Thumbnails Capture1.JPG   Capture2.JPG  

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes, if you import all speadsheets into the same table, calling the procedure AddExcelFileName won't work. You need to split it into 2 procedures: One to add the column [ExcelFileName] into the table, which must be called only once. The second to update the column [ExcelFileName], which must be called after each importation.
    Have a nice day!

  15. #15
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    I am not a expert user of access and VBA coding. So could you please modify the code so that i can get the final result.

    Thanks and regards
    R. Vadivelan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •