0

I’m currently trying to write a macro to pull information from a website and then display it in a worksheet. So far, I have code that will open Edge and navigate to the required URL. The user has to login using their CAC certificate, and then it goes to the correct page.

Dim browser
browser = Shell(EDGE_PATH & URL, 1)

Once the page is loaded, it submits the form which creates a new window with a text file that has the needed information. I need to get the information from that text file, but I’m not sure how to do it with VBA or shell. If it’s even possible. I know of Selenium, but I can’t use that in this environment.

Does anyone know of a way to do this? Any information would be appreciated.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Joseph Kessler
  • 3
  • 2
  • As far as I know you cannot do that with Edge, but there exist tutorials for Internet Explorer (IE) and Chrome (using the 3ʳᵈ party tool selenium). Please do some research and try that. If you get stuck or errors come back with your attempt and a question to it. – Pᴇʜ May 30 '22 at 06:10
  • Can you ask the user to press Ctrl+S to save the content to a file on the local disk? – DecimalTurn May 30 '22 at 06:21
  • @Pᴇʜ yea I already have it working with IE. IE is being retired June 15th though, and I can’t download and use Selenium on these computers. As far as I’m aware anyways. They’re DOD. – Joseph Kessler Jun 01 '22 at 18:21
  • @DecimalTurn it needs to pull that information on its own. If the user just saves the file it’ll defeat the purpose of the script. – Joseph Kessler Jun 01 '22 at 18:22
  • 1
    Well then there is probably no reliable way to do that unless the website exposes an API that you can directly use to download the file. So check if the website provides an API if not I only see the solutions IE or Selenium that will work. – Pᴇʜ Jun 02 '22 at 06:25
  • If nothing else is working, you could still look into [UIAutomation](https://stackoverflow.com/q/44756042/5958842) or the [Windows API SendMessage function](https://stackoverflow.com/questions/38022878/unable-to-write-the-filename-and-click-on-save-button-simultaneously-vba-code), but these would likely be less reliable than Selenium. – DecimalTurn Jun 02 '22 at 22:22
  • @Pᴇʜ Thanks for the input. I found out recently that we can reload IE after it gets retired, so it will work for now. – Joseph Kessler Jun 04 '22 at 10:04

0 Answers0