2

I am using Excel macros to drive image changes for a webpage. If I want an image to change on the page, I execute the macro, which grabs the desired image and replaces the file the webpage is using, and the webpage refreshes automatically.

It works perfectly in a local machine, but the intent is for remote users to pull up the webpage and see the new image as I control the macro.

Here is the excel macro:

Sub update_image()

newName = "C:\Cases\Nominal\new_image_to_show.png"
oldName = "C:\Cases\Images\updated_image.png"
FileCopy newName, oldName

End Sub

When I replace the file locations with the web locations, the macro stops working. The web space is an internal location within my organization, and two options I tried were:

newName = "https:://location_of_content\Cases\Nominal\new_image_to_show.png"
oldName = "https:://location_of_content\Cases\Images\updated_image.png"

and 

newName = "file://///location_of_content\Cases\Nominal\new_image_to_show.png"
oldName = "file://///location_of_content\Cases\Images\updated_image.png"

I also tried relative path nomenclature, 

newName = ThisWorkbook.Path & "\Cases\Nominal\new_image_to_show.png"

but in both cases I get

Run Time Error '52'

Any ideas on how I can rename the paths? Or alternatives to Excel Macros?

braX
  • 9,702
  • 5
  • 16
  • 29
fonsi
  • 371
  • 5
  • 16
  • Where is `img1` on your HTML page? – Tim Williams Jun 02 '18 at 00:04
  • Is the web server accessible via a UNC path like `\\serverName\C$\inetpub\wwwroot\Cases\Images\updated_image.png` ? You will need something like that... – Tim Williams Jun 02 '18 at 00:07
  • It is part of the generic code for other pages. I use this framework for other pages with more than one image. That's where the ID="img1" would come into play. For my question, I left that one out, but the result is that for an image with ID="img1" it would also refresh just like ID="img" – fonsi Jun 02 '18 at 00:08
  • in your function updateImage() can you console.log image.src and show us the results? Also, are you changing this line of your html – DCR Jun 02 '18 at 00:12
  • 1
    Maybe I am misunderstanding what you're trying to do, but it seems to me that you can't force a client's browser to update an image through VBA alone, without changing the HTML page itself to auto-refresh on timer, or something similar? (ie., replacing the image on the server isn't going to affect a page that's was already loaded on client-side). --I'm not sure if you're aware, but all the users needs to do to "force refresh" the image is hit SHIFT+F5 – ashleedawg Jun 02 '18 at 00:29
  • The HTML code forces the images to refresh every second. The VBA copies an image from one location and pastes it in the folder where the HTML is looking for the image to render. Thus, the VBA "updates" the image that the client's browser is looking for. – fonsi Jun 02 '18 at 01:08
  • @fonsi - ok - so the HTML needs not a regular "refresh" (like F5) but a "full refresh" to [**ignore cached content**](https://stackoverflow.com/a/20569488/8112776) (like Shift+F5) – ashleedawg Jun 02 '18 at 01:16
  • I know. It was created after a major training deficiency in our organization was discovered. This was a quick and non-elegant way to show a scenario. The instructor controls what the student sees via the macros. However, people ended up liking it, so they now want to increase exposure to other organizations, thus the need to put it in a server location if possible. – fonsi Jun 02 '18 at 01:20
  • Ok so can't you just add this to the head of the HTML page:? ` ` – ashleedawg Jun 02 '18 at 01:20
  • The HTML part is working fine for me. Is the moving the files from one location to another that is my real problem when I move the application from local machine to server. – fonsi Jun 02 '18 at 01:23
  • @fonsi ok - I did misunderstand. So to confirm: once the new photo is in the correct location, the users can load it properly, correct? (for example,**if you replace the photo manually, everything works fine?**) -- if Yes, the HTML codes and tag should be removed from the question since it's irrelevant to your issue. – ashleedawg Jun 02 '18 at 04:50

0 Answers0