VBA Excel Import /w Loop

J

jc0r

Board Regular
Joined
Mar 16, 2009
Messages
124
Could someone please tell me where i have gone wrong with the following bit of code. It is supposed to be dragging data from Excel by using my pre-saved import: jc0r123 at specific times and the code is re-run every 1 second to find out if it is time to run the import file: jc0r123.

However, it does nothing! yet i have no errors :s

Any help would be much appreciated, thankyou.

Code: 
Dim TimeToRun
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub auto_open()
    Call ScheduleImport
End Sub

Sub ScheduleImport()
    TimeToRun = Now + TimeValue("00:00:01")
    Application.OnTime TimeToRun, "Import"
End Sub

Sub Import()
If Time > " 13:29:50    " And Time < "  13:30:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 13:34:50    " And Time < "  13:35:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 13:39:50    " And Time < "  13:40:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 13:44:50    " And Time < "  13:45:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 13:49:50    " And Time < "  13:50:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 13:54:50    " And Time < "  13:55:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 13:59:50    " And Time < "  14:00:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:04:50    " And Time < "  14:05:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:09:50    " And Time < "  14:10:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:14:50    " And Time < "  14:15:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:19:50    " And Time < "  14:20:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:24:50    " And Time < "  14:25:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:29:50    " And Time < "  14:30:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:34:50    " And Time < "  14:35:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:39:50    " And Time < "  14:40:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:44:50    " And Time < "  14:45:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:49:50    " And Time < "  14:50:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:54:50    " And Time < "  14:55:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 14:59:50    " And Time < "  15:00:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:04:50    " And Time < "  15:05:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:09:50    " And Time < "  15:10:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:14:50    " And Time < "  15:15:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:19:50    " And Time < "  15:20:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:24:50    " And Time < "  15:25:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:29:50    " And Time < "  15:30:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:34:50    " And Time < "  15:35:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:39:50    " And Time < "  15:40:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:44:50    " And Time < "  15:45:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:49:50    " And Time < "  15:50:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:54:50    " And Time < "  15:55:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 15:59:50    " And Time < "  16:00:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:04:50    " And Time < "  16:05:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:09:50    " And Time < "  16:10:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:14:50    " And Time < "  16:15:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:19:50    " And Time < "  16:20:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:24:50    " And Time < "  16:25:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:29:50    " And Time < "  16:30:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:34:50    " And Time < "  16:35:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:39:50    " And Time < "  16:40:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:44:50    " And Time < "  16:45:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:49:50    " And Time < "  16:50:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:54:50    " And Time < "  16:55:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 16:59:50    " And Time < "  17:00:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:04:50    " And Time < "  17:05:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:09:50    " And Time < "  17:10:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:14:50    " And Time < "  17:15:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:19:50    " And Time < "  17:20:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:24:50    " And Time < "  17:25:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:29:50    " And Time < "  17:30:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:34:50    " And Time < "  17:35:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:39:50    " And Time < "  17:40:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:44:50    " And Time < "  17:45:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:49:50    " And Time < "  17:50:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:54:50    " And Time < "  17:55:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 17:59:50    " And Time < "  18:00:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:04:50    " And Time < "  18:05:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:09:50    " And Time < "  18:10:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:14:50    " And Time < "  18:15:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:19:50    " And Time < "  18:20:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:24:50    " And Time < "  18:25:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:29:50    " And Time < "  18:30:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:34:50    " And Time < "  18:35:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:39:50    " And Time < "  18:40:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:44:50    " And Time < "  18:45:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:49:50    " And Time < "  18:50:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:54:50    " And Time < "  18:55:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 18:59:50    " And Time < "  19:00:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 19:04:50    " And Time < "  19:05:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 19:09:50    " And Time < "  19:10:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 19:14:50    " And Time < "  19:15:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 19:19:50    " And Time < "  19:20:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 19:24:50    " And Time < "  19:25:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
Else
If Time > " 19:29:50    " And Time < "  19:30:00    " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
If Time > "     19:34:50        " And Time < "      19:35:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10000
Call ScheduleImport
If Time > "     19:39:50        " And Time < "      19:40:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10001
Call ScheduleImport
If Time > "     19:44:50        " And Time < "      19:45:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10001
Call ScheduleImport
If Time > "     19:49:50        " And Time < "      19:50:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10002
Call ScheduleImport
If Time > "     19:54:50        " And Time < "      19:55:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10002
Call ScheduleImport
If Time > "     19:59:50        " And Time < "      20:00:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10003
Call ScheduleImport
If Time > "     20:04:50        " And Time < "      20:05:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10003
Call ScheduleImport
If Time > "     20:09:50        " And Time < "      20:10:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10004
Call ScheduleImport
If Time > "     20:14:50        " And Time < "      20:15:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10004
Call ScheduleImport
If Time > "     20:19:50        " And Time < "      20:20:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10005
Call ScheduleImport
If Time > "     20:24:50        " And Time < "      20:25:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10005
Call ScheduleImport
If Time > "     20:29:50        " And Time < "      20:30:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10006
Call ScheduleImport
If Time > "     20:34:50        " And Time < "      20:35:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10006
Call ScheduleImport
If Time > "     20:39:50        " And Time < "      20:40:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10007
Call ScheduleImport
If Time > "     20:44:50        " And Time < "      20:45:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10007
Call ScheduleImport
If Time > "     20:49:50        " And Time < "      20:50:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10008
Call ScheduleImport
If Time > "     20:54:50        " And Time < "      20:55:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10008
Call ScheduleImport
If Time > "     20:59:50        " And Time < "      21:00:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10009
Call ScheduleImport
If Time > "     21:04:50        " And Time < "      21:05:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10009
Call ScheduleImport
If Time > "     21:09:50        " And Time < "      21:10:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10010
Call ScheduleImport
If Time > "     21:14:50        " And Time < "      21:15:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10010
Call ScheduleImport
If Time > "     21:19:50        " And Time < "      21:20:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10011
Call ScheduleImport
If Time > "     21:24:50        " And Time < "      21:25:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10011
Call ScheduleImport
If Time > "     21:29:50        " And Time < "      21:30:00        " Then
DoCmd.RunSavedImportExport "jc0r123"
Sleep 10012
Call ScheduleImport
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "Import", , False
End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
jc:
This code has a lot of lines for very little action.

If the code is not resulting in errors, it's likely doing nothing.
In some ways you've created a haystack and are now looking for the needle


Here are a few suggestions:
*rem out all the If statements until you have just the basic commands
*add some debug.print or msgbox lines to prove that the code executes within an if statement
*then add the time testing statements one at a time
 
Upvote 0
First off, why are you importing every 5 minutes? That seems mighty excessive.

Second, the way you are referring to the times is way off. It should be:
Code: 
If Time > #21:19:50#  And Time < #21:20:00#  Then

Or you could just narrow it down (this would replace all of that code of yours) Not sure what the Sleep part is supposed to be doing - why are you using that?
Code: 
Select Case Format(Time, "nn")
Case 4, 9
   Select Case Format(Time, "ss")
     Case 0, 50 to 59
        ....do your stuff here
     End Select
End Select
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Access table set start of day and end of day to my schedule
Replies
2
Views
639
Tcurtis
T
A
  • Solved
Count entry by time
Replies
3
Views
211
admvlad
A
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
68
Bernie Galve
B
D
  • Solved
Error 1004
2
Replies
18
Views
397
dmt32
D
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
283
Cubist
Cubist

Forum statistics

Threads
1,224,518
Messages
6,179,253
Members
452,900
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top