Results 1 to 13 of 13
  1. #1
    lmarconi is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    29

    How to control i'm importing correct excel file

    Good morning everyone. I would like to you have your kindly suggestion how to control the following issue:
    In my application, I have a form with a combo box which allow the user to select one template (from a list of 4 different possible templates); after select the template the user browse the file to be imported (excel) and the system automatically import the selected file in a table (which is refer to the selected template).


    I discover that some users, by mistake, select one template and import the file which is not refer to that template.
    Question is: how I can block the importing (maybe given a warning message) if the selected file is not refer to the template I selected in the form ?
    I was thinking to control the first column header of the selected file but not sure how to do.
    Thank you for usual and kindly support.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,298
    How would you know the selected file is not appropriate for the template?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,203
    Store the header names in a table and compare against file selected?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  4. #4
    lmarconi is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    29
    Quote Originally Posted by June7 View Post
    How would you know the selected file is not appropriate for the template?
    I discovered that, for some reason, the wrong data was append to the selected template. E.g. : I selected template A, I import data suite for template B, "part" of data of template B (because tables are different but name of common fields are same) was appended to template A.

  5. #5
    lmarconi is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    29
    Quote Originally Posted by Welshgasman View Post
    Store the header names in a table and compare against file selected?
    Yes this was the idea i had and i believe could be enough only to check the first header field to understand if i'm importing the correct template but i have little bit difficulty to build it in VBA so i will appreciate if someone can help me with this.
    So the idea is: browse the file and, before to import, to check if the header of the first column is the same header of the table connected to the selected template.
    Hope is clear.
    Thank you

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,298
    See if this gets you started:
    Code:
    Dim rsA As DAO.Recordset
    Dim rsE As ADODB.Recordset
    Dim cn As ADODB.Connection
    
    Set cn = CreateObject("ADODB.Connection")
    Set rsE = CreateObject("ADODB.Recordset")
    
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilePath  _
        & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    rsE.Open "SELECT * FROM [Sheet1$A1:A2]", cn, adOpenDynamic, adLockOptimistic
    
    Set rsA = CurrentDb.OpenRecordset("SELECT * FROM " & strTable " WHERE 0=1")
    
    If rsE(0).Name = rsA(0).Name Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,203
    Well you said now that some of the fields are the same, so it is quite possible the first 5 or 10 fields could be the same?
    If you have any control over the Excel files, the template name in a static cell address would be safer, if you cannot rely on any naming convention.?

    As of now, I would just open the excel file, open a recordset for that particular template (one field per record with template name, and correct order of fields)
    Walk through the record set with a counter, incrementing as you go,using same counter for offset from "A1" and compare recordset value with cell value.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  8. #8
    lmarconi is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    29
    Quote Originally Posted by June7 View Post
    See if this gets you started:
    Code:
    Dim rsA As DAO.Recordset
    Dim rsE As ADODB.Recordset
    Dim cn As ADODB.Connection
    
    Set cn = CreateObject("ADODB.Connection")
    Set rsE = CreateObject("ADODB.Recordset")
    
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilePath  _
        & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    rsE.Open "SELECT * FROM [Sheet1$A1:A2]", cn, adOpenDynamic, adLockOptimistic
    
    Set rsA = CurrentDb.OpenRecordset("SELECT * FROM " & strTable " WHERE 0=1")
    
    If rsE(0).Name = rsA(0).Name Then
    Thanks a lot for the suggested code. May i ask you how if it is possible to modify the script testing the name of the worksheet , instead of the first column of the file? This for two reasons: the first that the name of worksheet will immediately let me understand if I'm importing the correct file (sorry if i didn't tell you before) secondly, if I test the first column, as per your script, I should indicate the name of the worksheet ([Sheet1$A1:A2]) which is not always the same for the different templates, so i should repeat the test more than one time. Thank you

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,298
    Here is a version that doesn't care what the sheet name is:

    Code:
    Dim ExcelFile As StringDim objExcel As Object, wb As Object
    Dim rsA As DAO.Recordset
    ExcelFile = "Excel file path"
    If ExcelFile > "" Then
        Set objExcel = CreateObject("Excel.Application")
        Set wb = objExcel.Workbooks.Open(ExcelFile)
        Debug.Print wb.Worksheets(1).Range("A1")
        Set rsA = CurrentDb.OpenRecordset("SELECT * FROM " & strTable " WHERE 0=1")
        'If wb.Worksheets(1).Range("A1") = rsA(0) Then
    End If
    Or if you want the worksheet name: wb.Worksheets(1).Name

    What would you test that name against?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,203
    If I understand you correctly

    Using June7's code with this code (air code as well)
    Code:
    DEbug.Print wb.Sheets(1).Name ' This will show the first sheet name, adjust if you need another sheet
    Test that against what you expect it to be.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  11. #11
    lmarconi is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    29
    Thank you all for the great support and solutions you give me. I will use it and I will let you know.
    About the June7 Question : "What would you test that name against?" I will test with the template they will select from the drop down list before importing.
    Cheers

  12. #12
    lmarconi is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    29
    This is how i customized the code you kindly prepare and who works perfect for my scope :

    Function check_selected_file(myfile, mytemplate As String, xx As Variant)


    Dim ExcelFile As String
    Dim objExcel As Object, wb As Object
    ExcelFile = myfile
    If ExcelFile > "" Then
    Set objExcel = CreateObject("Excel.Application")
    Set wb = objExcel.Workbooks.Open(ExcelFile)

    If mytemplate = "Functional Location" And wb.Worksheets(1).Range("A1") <> "Level" Then
    xx = 1
    ElseIf mytemplate = "Equipment" And wb.Worksheets(1).Range("A1") <> "TechIdNo" Then
    xx = 1
    ElseIf mytemplate = "FLOC Class & Characteristics" Or mytemplate = "EQUI Class & Characteristics" Or mytemplate = "Full Class & Characteristics" And wb.Worksheets(1).Range("A1") <> "CLASS_TYPE" Then
    xx = 1
    ElseIf mytemplate = "Task List" And wb.Worksheets(1).Range("A1") <> "GROUP" Then
    xx = 1
    ElseIf mytemplate = "Maintenance Plan" And wb.Worksheets(1).Range("A1") <> "MP number" Then
    xx = 1
    ElseIf mytemplate = "EDW" And wb.Worksheets(1).Range("A1") <> "Plant Code" Then
    xx = 1
    Else
    xx = 0
    End If


    End If


    Set wb = Nothing
    Set objExcel = Nothing


    End Function

    Where myfile is the file i select to be imported and mytemplate is the template i want to import (selected from drop down list).
    I test the first column of the excel file (which is better than the worksheet name, just in case users will change it for some reason) based on the template and if it is not what should be i assign 1 to a variable. Then in my main routine i test xx, if it is 1 i will get a warning message and the importing will be stop.
    Maybe there is more quick and nice way to do, but it works excellent for me so thanks a lot for support.

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,203
    No, no
    Use case statements and set the result of the function to true or false.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



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

Similar Threads

  1. Importing an excel file through vba
    By andy49 in forum Import/Export Data
    Replies: 4
    Last Post: 01-13-2017, 12:50 AM
  2. Importing Excel file to access
    By bambereczek in forum Access
    Replies: 1
    Last Post: 09-10-2012, 06:38 AM
  3. Importing .CSV file to excel
    By herbc0704 in forum Import/Export Data
    Replies: 1
    Last Post: 05-17-2012, 05:25 PM
  4. importing data from an excel file
    By slimjen in forum Access
    Replies: 3
    Last Post: 09-21-2011, 12:38 PM
  5. Importing an Excel file
    By bvtterflygirl in forum Import/Export Data
    Replies: 5
    Last Post: 02-24-2011, 11:54 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