Forum Discussion
How to automate a login and refresh of a spreadsheet via an Excel plug-in?
Hi.
I have a requirement to automatically refresh a spreadsheet's data. I'm an Excel novice and have no idea how to do this automatically. To refresh the data in a manual fashion, I perform the following steps:
1) Open the Excel file.
2) Click on the iLEVEL menu option (it's on the top amongst other menu options such as 'Data', 'Review', Automate', etc..). This will present an iLEVEL login icon. Click on this icon and then enter my email address and password. In fact, once I've entered my work email, it uses SSO to gain access instead of entering a password. I do have a password should I need it (needs to be entered somewhere).
3) On the toolbar ribbon, under the iLEVEL menu option, there is the 'Refresh All' option. I click on this, wait a few minutes, and the spreadsheet has its data refreshed.
iLEVEL is a third-party software solution.
What I need to do is to achieve the above result automatically. At, say, 3am each morning, I would like automatically refresh the data. The updated spreadsheet will then act as the data source for our reporting. In case it's asked, at this time, we cannot look to hook into the view/table that feeds this spreadsheet. Therefore, the spreadsheet is the endpoint.
I will investigate this but if you are aware if this can be done or not, and point me in the right direction, it will be appreciated. I'm wondering if Power Query, or something similar, could achieve this.
Thanks.
3 Replies
NikolinoDE thank you for your response. It is greatly appreciated. I will try to implement what you've suggested and see where that gets me.
I did follow a similar process between posting my question and your response. This involved recording a macro, then a .bas file, then a .bat file then Windows Scheduler. This was yesterday. I'm not about to view the results. I assume there is log that tells me if the process was a success (the to check the actual contents).This gets tricky because i LEVEL is not a native Excel data connector, so typical Power Query or Power Automate approaches might not work out of the box.
You can maybe write a VBA macro to simulate the refresh process and schedule it using Windows Task Scheduler.
This assumes that i LEVEL exposes some VBA-accessible objects. If not, you may need SendKeys (less reliable). Place this in ThisWorkbook or a Module.
Sub AutoLoginAndRefresh_iLEVEL() Dim waitTime As Double ' Optional: show Excel (if hidden) Application.Visible = True Application.DisplayAlerts = False ' Optional: Wait for the workbook and iLEVEL to fully load waitTime = Timer + 10 Do While Timer < waitTime DoEvents Loop ' Step 1: Navigate to iLEVEL tab (Alt key navigation) Application.SendKeys "%", True ' Activate Alt key Application.SendKeys "i", True ' Assume iLEVEL tab is activated via Alt+i Application.SendKeys "{ENTER}", True ' Confirm selection ' Step 2: Login (simulate clicking the login button) Application.SendKeys "{TAB}", True Application.SendKeys "{ENTER}", True ' Login button Application.SendKeys "email address removed for privacy reasons", True ' Replace with your email if needed Application.SendKeys "{ENTER}", True ' Wait for SSO login (adjust this if login takes longer) Application.Wait Now + TimeValue("00:00:10") ' Step 3: Click 'Refresh All' Application.SendKeys "%", True Application.SendKeys "i", True ' Return to iLEVEL tab Application.SendKeys "r", True ' Assume 'Refresh All' shortcut key is R (you may need to adjust) ' Wait for refresh to complete Application.Wait Now + TimeValue("00:05:00") ' 5 minutes refresh time ' Step 4: Save and close ThisWorkbook.Save Application.Quit End SubAutomation via Task Scheduler:
Create a .vbs script to open Excel and run your macro:
Dim xl Set xl = CreateObject("Excel.Application") xl.Visible = False Set wb = xl.Workbooks.Open("C:\Path\To\Your\File.xlsx") xl.Run "AutoLoginAndRefresh_iLEVEL"Schedule this .vbs using Windows Task Scheduler at 3:00 AM.
The VBA code uses SendKeys to simulate keyboard navigation, which is a fragile method that:
- Relies on specific UI layout, timing, and keyboard shortcuts.
- Fails if i LEVEL does not support keyboard navigation for login or refresh.
- Breaks easily if focus shifts, security prompts appear (e.g. SSO), or i LEVEL loads slowly.
Maybe the code will help you with your project, if not, contact i LEVEL support and ask about:
VBA integration
COM API or automation objects
Command-line refresh
Office Scripts or external automation hooks
If They Don't Support It:
Use fragile SendKeys (but highly customized and tested).NikolinoDE - I've come back to this matter.
I should state the number of rows the Excel file has before an iLevel plug-in data refresh is 3275. If I manually refresh the data by manually clicking on the iLevel plug-in, signing in, clicking 'Refresh All' - the number of rows increases to 3300. Of course, I don't save the Excel file as I wouldn't be able to test the row count going from 3275 to 3300 again.
----------------------------------------------------------------------------------------
Before you responded to this thread, I Googled the following method:
1) Take the Excel file with the iLevel plug-in present. Record a Macro that records clicking on the iLevel tab, recording the entering of the logon email address and SSO password, then clicking Refresh All.
2) Create a VB module with the following content and save the vb script as a .BAS file, save and close down the Excel workbook:3) Create a Windows Batch File that has "Script" followed by the path of the .BAS file.
4) Create a Windows Task Scheduler that executes the Windows Batch File.
I watch my screen at the scheduled time and a black cmd screen pops are for a second with some text on it (it disappears too soon for me to read the text).
I check the Excel file and the number of records hasn't increased. I take it the above is incorrect.
-------------------------------------------------------------------------------------------
As I haven't used Macros, VBA, etc.. before, I need to build my confidence so I created a simple Macro/VB script that shows HelloWorld() in the message box. Rather than schedule the Macro through the Windows Task Scheduler, I simply clicked 'Run' on the Macro from with the Developer tab and "Hello world" appears.
-------------------------------------------------------------------------------------------
With a little confidence gained. I attempted your helpful post.
1) I created a Macro/VB Script with the following content:2) I just wanted to see if the Macro had worked and see if the Excel row count had increased to 3300 (which would indicate a successful data refresh). To do this, I simply clicked 'Run' when in the Developer tab (as I did for the HelloWorld Macro). An egg timer appeared and remained for around 6 minutes (an egg timer being present for around six minutes is what happens when I manually refresh the data using the iLevel plug-in process). After six minutes the Excel spreadsheet just closes. I see the Excel file then gets an updated timestamp at when the Excel file closed. This would seem encouraging. However, when I opened the Excel file I see the row count remains at 3275 which indicates a refresh hasn't taken place (if it had refreshed the row count would be 3300).
Am I doing something wrong? I most probably am as I've not used Macros/VBA, etc.. before. I'm scrabbling to get all working.
Thanks.
