Results 1 to 15 of 15
  1. #1
    YehiaH is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    6

    Angry Import From Excel

    Hello guys,



    I am trying to automate a procedure on Ms Access where we used to copy paste data manually on Access. I added a button that will do this job automatically and insert the data in its specific table. Anyway I imported the Excel spread sheet into a Temp table and reinserted the data in Temp in the corresponding table. The insert was done properly, yet the access database I am using has some queries related to it and once I refresh those queries, different results occurs then the ones I get when I upload the data manually by Copy/ Paste from Excel

    I am using Office 2013

    Any advice on the issue?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont copy/paste into access.
    you import via transferspreadsheet ,
    or
    attach the xl file as a table then run an append query.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Either your import is not importing the correct data or the wrong rows or leaving fields blank due to data type mismatch issues or your query is ignoring certain data for some reason. Do a row by row, column by column comparison between your copy/paste version and your imported version.

    If you want more detailed help you will need to provide more information - what code are you using to import the data? what is the sql to the queries with different results? some example data that has been imported identifying which records are not being included in your query

  4. #4
    YehiaH is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    6
    I am using VBA to import the data, I tried to import the whole Spreadsheet into a Temp Table, and by default the table was created. I also tried to select the fields from Excel into their places in the table in Access, I also tried to drop and recreate the original tables .Nothing is working.
    I tried to recreate the Temp table to avoid any data miss match and set all fields to text

    What is funny actually is that if I take the final data imported to the original table itself and I copy/ paste it manually into the same table, the queries are giving a different results, which is weird. Is there any event that trigger something when we do Copy/Paste and that is not triggered while we import?

    The number of records is the same whether I import or Copy/Paste and I assume the data is correct consequently

    This is what I am doing in the first attempt:
    DoCmd.TransferSpreadsheet acImport
    DoCmd.RunSQL

    or

    DoCmd.RunSQL to create the temp table
    DoCmd.TransferSpreadsheet acImport
    DoCmd.RunSQL

    Or selecting the firlds from Excel and directly inserting into the Access Table

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Is there any event that trigger something when we do Copy/Paste and that is not triggered while we import?
    not so far as I am aware

    The number of records is the same whether I import or Copy/Paste and I assume the data is correct consequently
    assume nothing - check the columns match as well

    and what is the sql to your queries that give different results

  6. #6
    YehiaH is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    6
    This is the query:

    SELECT [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].DISC, [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].DOCNO, [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[DOC DESC], [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].REV, [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[APV CODE2], [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[APV STATUS], [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[REV TRANS], [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[TRANS NO], [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[TRANS DATE], [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[CYCLE PL], [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[REV ANALYSIS], [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[STATUS ANALYSIS]
    FROM [0 Qry_AWQ0109-1 TRANSLOG_2 ALL] LEFT JOIN [tbl_Discipline Code] ON [0 Qry_AWQ0109-1 TRANSLOG_2 ALL].DISC = [tbl_Discipline Code].[DISC Code]
    WHERE ((([0 Qry_AWQ0109-1 TRANSLOG_2 ALL].[APV CODE2])="CLIENT PENDING"))
    ORDER BY [tbl_Discipline Code].SEQ;

    Why is everything aligned properly when I copy paste the table into itself? I mean it is the same data being copy pasted at the same place

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so we are talking about alignment, not wrong results? if you are talking about within a column that is usually to do with the data type - by default text is aligned left and numbers aligned right. So suggests copy/paste is creating a field as a text datatype and transferspreadtsheet as a numeric datatype - or the other way round.

    If this is not the issue, as previously requested, provide some sample data to demonstrate the problem - initial data from the excel file, and examples of the different outcomes in your query

  8. #8
    YehiaH is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    6
    FIND BELOW ALL OF THE MODULE WRITTEN ON THE DB, AS I SAID IT IS INSERTING PROPERLY BECAUSE WHEN I COPY PASTE THE DATA PRESENT IN THE TABLE W_TRANS FROM ITSELF, THE QUERIES ARE BACK TO NORMAL

    THANK YOU FOR YOUR HELP

    Option Compare Database
    Option Explicit

    Dim holdPercComplete As Single

    Private Sub Browse_but_Click()
    GetFolder
    End Sub

    Private Sub Browse_but_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Me.Browse_but.FontBold = 0 Then
    Me.Browse_but.FontBold = 1
    End If
    End Sub

    Private Sub Import_but_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Me.Import_but.FontBold = 0 Then
    Me.Import_but.FontBold = 1
    End If
    End Sub

    Private Sub Excel_File_Directory_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Me.Excel_File_Directory.ForeColor <> vbRed Then
    Me.Excel_File_Directory.ForeColor = vbRed
    End If

    If Me.Excel_File_Directory.FontBold = 0 Then
    Me.Excel_File_Directory.FontBold = 1
    End If

    If Me.Label2.ForeColor <> vbRed Then
    Me.Label2.ForeColor = vbRed
    End If

    If Me.Label2.FontBold = 0 Then
    Me.Label2.FontBold = 1
    End If
    End Sub

    Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Me.Excel_File_Directory.ForeColor <> vbBlack Then
    Me.Excel_File_Directory.ForeColor = vbBlack
    End If

    If Me.Excel_File_Directory.FontBold = 1 Then
    Me.Excel_File_Directory.FontBold = 0
    End If

    If Me.Label2.ForeColor <> vbBlack Then
    Me.Label2.ForeColor = vbBlack
    End If

    If Me.Label2.FontBold = 1 Then
    Me.Label2.FontBold = 0
    End If

    If Me.Browse_but.FontBold = 1 Then
    Me.Browse_but.FontBold = 0
    End If

    If Me.Import_but.FontBold = 1 Then
    Me.Import_but.FontBold = 0
    End If
    End Sub

    Private Sub Import_but_Click()
    Dim FilePath As String
    Dim DBPath As String
    Dim lngItem As Long
    Const lngTotal As Long = 100000

    FilePath = Me.Excel_File_Directory & "\FILE NAME.xlsx"
    DBPath = CurrentDb.Name

    If File_Exists(FilePath) Then
    DropTable DBPath, "W_MAIN_TEMP"
    DoCmd.TransferSpreadsheet acImport, , "W_MAIN_TEMP", FilePath, True

    Do While lngItem < 10000
    lngItem = lngItem + 1
    Call UpdateProgress(lngItem, lngTotal, "Importing Main Data...")
    Loop

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM W_MAIN;"
    DoCmd.RunSQL "INSERT INTO W_MAIN ([Project ID], [Document Number], [Task Name], [Area Code], [Doc Class], [Percentage of Completion], [Contribution to Project], [Document Genealogy]) " & _
    "SELECT W_MAIN_TEMP.[Project ID], W_MAIN_TEMP.[Document Number], W_MAIN_TEMP.[Task Name], W_MAIN_TEMP.[Area Code], W_MAIN_TEMP.[Doc Class], W_MAIN_TEMP.[Percentage of Completion], W_MAIN_TEMP.[Contribution to Project], W_MAIN_TEMP.[Document Genealogy] FROM W_MAIN_TEMP;"
    DoCmd.SetWarnings True

    Do While lngItem < 25000
    lngItem = lngItem + 1
    Call UpdateProgress(lngItem, lngTotal, "Importing Main Data...")
    Loop

    DropTable DBPath, "W_MAIN_TEMP"

    'On Error GoTo ErrorHandling
    Else
    MsgBox "Main File not found, Please Check"
    Exit Sub
    End If

    FilePath = Me.Excel_File_Directory & "\EDMS_UPDATE Detail.xlsx"

    If File_Exists(FilePath) Then
    DropTable DBPath, "W_TRANS_Temp"

    DoCmd.RunSQL "CREATE TABLE [W_TRANS_TEMP] ([Document Number] VARCHAR(255), [Document Description] VARCHAR(255), [Sheet Number] VARCHAR(255), " & _
    "[Doc Class] VARCHAR(255), [Document Status] VARCHAR(255), [Life Cycle Status/Issue Purpose] VARCHAR(255), [Approval Status Code( Client) ] VARCHAR(255), " & _
    "[Revision Number] VARCHAR(255), [Latest Transmittal No] VARCHAR(255), [Latest Transmittal Released On] VARCHAR(255), [Approval Status Description] VARCHAR(255), " & _
    "[Received Back On] VARCHAR(255), [Client Document Number] VARCHAR(255), [Vendor Document Number] VARCHAR(255), [Document Genealogy] VARCHAR(255));"

    DoCmd.TransferSpreadsheet acImport, , "W_TRANS_Temp", FilePath, True

    Do While lngItem < 50000
    lngItem = lngItem + 1
    Call UpdateProgress(lngItem, lngTotal, "Importing Trans Data...")
    Loop

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM W_TRANS;"
    DoCmd.RunSQL "INSERT INTO W_TRANS ([Document Number], [Document Description], [Sheet Number], " & _
    "[Doc Class], [Document Status], [Life Cycle Status/Issue Purpose], [Approval Status Code( Client)], " & _
    "[Revision Number], [Latest Transmittal No], [Latest Transmittal Released On], [Approval Status Description], " & _
    "[Received Back On], [Client Reference], [Vendor Document Number], [Document Genealogy]) " & _
    "SELECT [W_TRANS_TEMP].[Document Number], [W_TRANS_TEMP].[Document Description], [W_TRANS_TEMP].[Sheet Number], " & _
    "[W_TRANS_TEMP].[Doc Class], [W_TRANS_TEMP].[Document Status], [W_TRANS_TEMP].[Life Cycle Status/Issue Purpose], [W_TRANS_TEMP].[Approval Status Code( Client) ], " & _
    "[W_TRANS_TEMP].[Revision Number], [W_TRANS_TEMP].[Latest Transmittal No], [W_TRANS_TEMP].[Latest Transmittal Released On], [W_TRANS_TEMP].[Approval Status Description], " & _
    "[W_TRANS_TEMP].[Received Back On], [W_TRANS_TEMP].[Client Document Number], [W_TRANS_TEMP].[Vendor Document Number], [W_TRANS_TEMP].[Document Genealogy] FROM [W_TRANS_TEMP];"
    DoCmd.SetWarnings True

    Do While lngItem < 90000
    lngItem = lngItem + 1
    Call UpdateProgress(lngItem, lngTotal, "Importing Trans Data...")
    Loop

    DropTable DBPath, "W_TRANS_Temp"

    'On Error GoTo ErrorHandling
    Else
    MsgBox "Trs File not found, Please Check"
    Exit Sub
    End If

    Do While lngItem <= 100000
    lngItem = lngItem + 1
    Call UpdateProgress(lngItem, lngTotal, "Finilizing Import...")
    Loop

    Call UpdateProgress(lngItem, lngTotal, "Import complete")

    MsgBox "Import Completed Successfully"

    'ErrorHandling:
    ' MsgBox "Invalid entry. Make sure you are uploading the correct file - " & Err.Description
    ' Exit Sub
    End Sub

    Private Function File_Exists(tFile As String) As Boolean
    Dim lSize As Long
    On Error Resume Next

    lSize = -1
    lSize = FileLen(tFile)

    If lSize > -1 Then
    File_Exists = True
    Else
    File_Exists = False
    End If
    End Function

    Function DropTable(tFile As String, tTable As String)
    Dim dbs As Database

    Set dbs = OpenDatabase(tFile)

    If IsTableExists(tTable) Then
    ' Delete the Employees table.
    dbs.Execute "DROP TABLE " & tTable & ";"
    End If

    dbs.Close
    End Function

    Public Function IsTableExists(ByVal strTableName As String) As Boolean
    On Error Resume Next
    IsTableExists = IsObject(CurrentDb.TableDefs(strTableName))
    End Function

    Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = ‘ADD DEFAULT DIRECTORY
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
    End With

    NextCode:
    GetFolder = sItem
    Me.Excel_File_Directory = sItem
    Set fldr = Nothing
    End Function

    Private Sub UpdateProgress(CurrentItem As Long, TotalItems As Long, taskName As String)
    Dim PercComplete As Single
    Dim intWidth As Integer

    Me.Label22.Caption = taskName

    'Validate data
    If CurrentItem <= 0 Or TotalItems <= 0 Then
    Box20.Width = 0
    Exit Sub
    End If

    'Calculate the percentage complete
    PercComplete = CurrentItem / TotalItems
    If Int(PercComplete * 100) = Int(holdPercComplete * 100) Then
    Exit Sub
    End If
    'Save it for comparison
    holdPercComplete = PercComplete

    'Calculate how wide to make the progress bar
    If (PercComplete * 100) Mod 5 = 0 Then
    intWidth = (Box21.Width * PercComplete)
    Box20.Width = intWidth
    DoEvents 'or Me.Repaint
    End If

    End Sub

    Private Sub Form_Load()
    Call UpdateProgress(0, 0, "Idle")
    End Sub

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    with respect, I'm not going to sort through that lot to find the bit you mean and I'm not sure it is relevant - it is not what I asked for

  10. #10
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    Also to preserve spacing using the [CODE] wrappers in your post. Its the little pound-sign button in the formatting bar of the reply box. It will definitely help everyone read whats going on in your code.

  11. #11
    YehiaH is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    6
    Thank you for your help guys. The program is still not properly working and I cannot figure out the answer.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without knowing what 'not working properly' means and without you answering our questions, I don't think we can help any further

  13. #13
    YehiaH is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    6
    Quote Originally Posted by Ajax View Post
    without knowing what 'not working properly' means and without you answering our questions, I don't think we can help any further
    Not sure I understood what you really want.

    The data I am working on is a set of almost 27,000 record and related to at least 10 Queries depending sometimes on each other. But I was researching further and not sure if because the queries sometimes contain Not Null or "" which might differ between importing from Excel and Copy/Paste some data.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Not sure I understood what you really want.
    reread all the posts and answer the questions /supply the information requested

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I looked through the code.......
    I have to keep doing a hard close (using TaskManager) because of the errors.


    First, do you have these two lines at the top of EVERY module?
    Code:
    Option Compare Database
    Option Explicit

    Next, I found an error in the "GetFolder" code (that is in RED that you should have seen and fixed).
    Code:
    Function GetFolder() As String
        Dim fldr As FileDialog
        Dim sItem As String
        Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
        With fldr
            .Title = "Select a Folder"
            .AllowMultiSelect = False
            .InitialFileName = ‘ADD DEFAULT DIRECTORY
            If .Show <> -1 Then GoTo NextCode
            sItem = .SelectedItems(1)
        End With
    
    NextCode:
        GetFolder = sItem
        Me.Excel_File_Directory = sItem
        Set fldr = Nothing
    End Function
    This an oddly named parameter. Even though it is "InitialFileName", it is actually the initial path. If you were picking a file, you would also set the filter property. So you need to (or should) set an initial (default) path OR comment out the line.
    Next the "GOTO" command. It would have been so much easier to use the block form of the IF() function... like this
    Code:
            If .Show <> -1 Then
                sItem = .SelectedItems(1)
            End If
        End With
    It would have automatically gone to the "NextCode:" label WITHOUT having to use the depreciated GOTO command. (actually, you wouldn't need the label)



    What does this code do?
    Code:
            Do While lngItem < 50000
                lngItem = lngItem + 1
                Call UpdateProgress(lngItem, lngTotal, "Importing Trans Data...")
            Loop
    Looks to me that it is an attempt to use a progress bar. Don't know where the value of lngTotal is set.
    The progress does not really show the progress of anything but being able to count.


    Code:
            DropTable DBPath, "W_MAIN_TEMP"
    Then there is a function to delete tables.
    There are parameters to set the table name and the BE name. IMHO, it is better to delete the RECORDS, than to constantly delete, then create the tables.


    And not a lick of error handling........ I would expect it in at least the sub Import_but_Click().

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

Similar Threads

  1. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  2. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  5. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 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