Results 1 to 5 of 5
  1. #1
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34

    Import from Excel

    Hi

    I having problems importing from Excel to tabel in Access.

    I want access to open excelworksheet, and wait 30 sek for excel to update, and then run and saved import in access.




    Do anyone have a suggestion, this one of the things i have tried:

    (No matter what I do, I dont get the data from the opdated Excel (Excel is set to autoopdate)

    I have tried to run a macro from access to run opdate macro in excel, bot still I get old data.


    Private Sub Command46_Click()
    Dim XL As Object


    Set XL = CreateObject("Excel.Application")


    DoCmd.SetWarnings False


    DoCmd.RunSQL "DELETE * FROM SAP_Import"

    DoCmd.SetWarnings True


    Shell """Excel.exe"" ""G:\CPH\Tools\AutoMW\FTP_Auto.xlsm""", vbNormalFocus



    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 40
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    XL.Wait waitTime



    DoCmd.RunSavedImportExport "ImportFTP_Auto"

    'XL.Quit
    'Set XL = Nothing



    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Use Transferspreadsheet. Or do users not have Access?

    Why did you need to wait 30 seconds? You don't.

  3. #3
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34
    Hi

    Users dont have access to the spredsheet.

    In Ms access i have buttom with automated import.

    Excel have an automated update to an ftp server, and then some calculations. this
    take about 30 sek to update.

    So Access have to wait 30 sek, so excel is updated.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you please try this:
    Code:
    Private Sub Command46_Click()
    Dim XL As Object
    
    
    
    
    Set XL = CreateObject("Excel.Application")
    
    
    DoCmd.SetWarnings False
    
    
    DoCmd.RunSQL "DELETE * FROM SAP_Import"
    
    
    DoCmd.SetWarnings True
    
    
    Shell """Excel.exe"" ""G:\CPH\Tools\AutoMW\FTP_Auto.xlsm""", vbNormalFocus
    
    
    
    
    'you want Access to wait, not Excel
    
    
    'newHour = Hour(Now())
    'newMinute = Minute(Now())
    'newSecond = Second(Now()) + 40
    'waitTime = TimeSerial(newHour, newMinute, newSecond)
    'XL.Wait waitTime
    
    
    Dim PauseTime, Start
    
    
    PauseTime = 40    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
       DoEvents    ' Yield to other processes.
    Loop
        
        
    End If
    
    
    
    
    DoCmd.RunSavedImportExport "ImportFTP_Auto"
    
    
    'XL.Quit
    'Set XL = Nothing
    
    
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    after you have opened excel and waited 30 seconds - you don't appear to close and save the updated excel file

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

Similar Threads

  1. Replies: 6
    Last Post: 09-08-2019, 03:18 PM
  2. Replies: 6
    Last Post: 07-10-2019, 07:34 PM
  3. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  4. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  5. Replies: 4
    Last Post: 12-17-2012, 01:21 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