Link to home
Start Free TrialLog in
Avatar of basskozz
basskozz

asked on

Calling VBA Code externally (via the internet)

Is there anyway to call code via the internet ?

So that the real code isn't stored in the Workbook, only a referance to the code that is located on a website/webpage.

Thanks in advance,
BassKozz
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Not as code to my knowledge. This is what the much touted web services are supposed to enable.

In the interests of security, I doubt you would be wanting to call VBA code from an external website.
You could call dll's probably.
ASKER CERTIFIED SOLUTION
Avatar of Harisha M G
Harisha M G
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
WOW!

this one i have to remember as it should allow centralised management of code. ie call in the base routines into each workbook. then only have to update / fix the central code.
Avatar of basskozz
basskozz

ASKER

mgh_mgharish,

Where do I put in the URL that points to the file containing:
Public Sub Proc()
      MsgBox "Hello"
End Sub
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or even...

If (blnDownload_File("http://nigellee.info", "c:/homepage2.htm")) Then
    ' Success
Else
    ' Failure
End If

:)
only that as I menioned ActiveWorkbook.VBProject.VBComponents.Import("http://localhost/EESamples/module.bas")
came back with error 1004: "Programmatic access to visual basic project is not trustes."

does it work when the file is local ?
> mgh_mgharish,

> Where do I put in the URL that points to the file containing:

You should write the code to open/download the file containing that code. Then you can get the code in the file to a string and then to MSSC. After that you can execute
@SnowFlake - yes, local files work (as expected); or at least they do in my environment.

@Harish - Thanks for your pointer; that's exactly what I did above to demonstrate your suggestion :)

See comment stamped "Date: 05/15/2005 09:19PM BST"

BFN,

fp.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
btw, I had no idea I could do something like this, thanks Harish!
Simple.. I too did not have idea before I started answering EE
I could not give full working code because I did not know how to get text from a webspace (through code).. so I put the text..
   'Open a text file or website page and get the code to "ImportText"
 
And now I can see many ways to download!!
There sure are many ways to do it!  There are two easy ways to save it to a string (the first is my "GetWebsiteAsString" function above), the other uses IE.  I personally don't like using the IE method as I've had too many issues with it, but still possible:

'---Internet Explorer method, saves to a string
Function TextFromHTML(strURL) As String
    Dim ie As Object
    Set ie = CreateObject("internetexplorer.application")
    ie.navigate strURL
    Do Until ie.readystate = 4
        DoEvents
    Loop
    TextFromHTML = ie.document.body.innerText
    ie.quit
    Set ie = Nothing
End Function


Likewise you can also download the file to your HD, the first is what fp used above, the second also uses xmlhttp like in GetWebsiteAsString above but saves any file and doesn't require an API call

'---URLDownloadToFile method, the API call must go at the top of the module
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True
End Function

'---Save any file to HD using XMLHTTP, doesn't require the API call:
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
 Dim oXMLHTTP As Object, i As Long, hFile As Long
 Dim bArray() As Byte
 Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
 oXMLHTTP.Open "GET", vWebFile, False
 oXMLHTTP.send
 bArray = oXMLHTTP.ResponseBody
 hFile = FreeFile
 Open vLocalFile For Binary As #hFile
 Put #hFile, , bArray
 Close #hFile
 Set oXMLHTTP = Nothing
End Function

You can also use a webbrowser control, but they're uncommon even in userforms, and there are better alternatives anyways. If you want to see that, see an old question I asked: http:Q_21117310.html#11967696

Fun stuff

BK- I have to say that I'm very glad you asked this question!
What is urlmon ? Where is it located ? It is not there in my machine at all!!
I have two urlmon.dll files on my computer (win2k, office2k), one located at C:\WINNT\system32, the other located at C:\WINNT\ServicePackFiles\i386.  I think you'd want it to be in \winnt\system32 as for me the other directory is not in my path.

I think you know how to use these, but just in case you have to make sure the "Private Declare Function URLDownloadToFile" line is at the top of your module.  Then you can call it anywhere within that module.

When I searched in google under "urlmon.dll" the very first site is one that lets you download it for free.  I've never used the site before (though the URL seems genuine), so I don't know for sure that such a thing is legal (nothing either way in the site faq).  I would assume it is, as the file should come with windows, but I'd rather not take any chances.
Yeah.. I can see it.. it is in Windows\System32

But I wonder why the Windows Search did not find it ??
Is your system32 folder hidden? Maybe your search options dont look in hidden folders..?
No all are set correctly, but it was showing no files... ok forget that.. now I have found the dll myself :)
mvidas:
>I personally don't like using the IE method as I've had too many issues with it, but still possible

this will if your document is an html document that its body contains only text
and maybe in some other cases IE will throw in the missing tags for free
but I have a feeling that it might not work in many other cases

I have a feeling that if the vba code has tags in it this will ignore them
and break

consider
function max(a as integer,b as integer) as integer
if b<a then max=a
else max=b
end function

I think the IE solution will only return the text up to "if b"
the rest will be interpreted as an unclosed A tag

SnowFlake
Thanks for the (many) question(s) recently, BassKozz.

I hope you found the exercise as interesting as I did.

Happy codin',

BFN,

fp.
[ http://NigelLee.info ]
fanpages,
Defiantly, and thank you fanpages for the solutions...  I still have a couple of questions lingering, that I am sure I will post soon.

All the EE members who contributed,
Thanks for your support with this.

-BassKozz