Can you show us your database? I'm interested in getting to the website from Access/vba.
This one confuses me...
are you importing or linking? My guess is importing. If you have imported and saved the data, what exactly is the issue?
it is a linked excel table. the code below is what i use to open the webpage, navigate to the page and download the file. after download i have to open the file and get rid of headers in the spreadsheet that i dont need. then save the file.
Private Sub Command19_Click()
'routine to open webpage, download dispense data, import it into dashboard.
'log in information
Const strURL = enter the full website url here in quotation marks
Const strID = this is where my user name goes
Const strPswd = this is my passoword
strexcelpath = this is the path where i want to save the excel spreadsheet
todaydt = Date ' todays date
lastweek = Date - 7 ' go back 7 days
' import from website
' please note that the twait is important to set correctly or you can end up on a different page of your website
Set objie = CreateObject("InternetExplorer.Application")
objie.Visible = True
objie.Navigate2 strURL 'open website
Set objshell = CreateObject("WScript.Shell")
TWait = Time
TWait = DateAdd("s", 3, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
'log in script
objshell.SendKeys strID ' types in my logon for me
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB}" ' tabs to the password field
Wait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys strPswd 'types in my password field for me
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ENTER}"
TWait = Time
TWait = DateAdd("s", 3, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
'navigates to the correct page. i had to count how many tab key strokes it took to get to the right button on the webpage
objshell.SendKeys "{TAB 19}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ENTER}"
TWait = Time
TWait = DateAdd("s", 5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB 14}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ENTER}"
TWait = Time
TWait = DateAdd("s", 5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB 15}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ENTER}"
TWait = Time
TWait = DateAdd("s", 3, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB 15}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys lastweek ' enter the date range for the report this is the start date
TWait = Time
TWait = DateAdd("s", 0.1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB}"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{DOWN}"
TWait = Time
TWait = DateAdd("s", 0.1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB}"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys todaydt ' this is the end date
TWait = Time
TWait = DateAdd("s", 0.1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB}"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ }"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB}"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ }"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB}"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ }"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB}"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ }"
TWait = Time
TWait = DateAdd("s", 0.5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB 16}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ENTER}"
TWait = Time
TWait = DateAdd("s", 5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{TAB 39}"
TWait = Time
TWait = DateAdd("s", 5, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ENTER}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{down 2}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ENTER}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys strexcelpath ' tells where to save the downloaded excel file
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{ENTER}"
TWait = Time
TWait = DateAdd("s", 3, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objshell.SendKeys "{y}"
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
objie.Quit ' close the webpage
Set objchell = Nothing
Set objie = Nothing
' open imported excel spreadsheet and clean up rows
TWait = Time
TWait = DateAdd("s", 1, TWait)
Do Until TNow >= TWait
TNow = Time
Loop
Dim xl As Object
'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("your excel file")
'Step 2: Make Excel visible
xl.Visible = True
'Step 3: delete the first 7 rows from the workbook
xl.Rows("1:7").Delete
'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit
'Step 5: Memory Clean up.
Set xl = Nothing
from here it goes into my code which i have the questions. my loop does not end when eof is reached..
