Access macro 1

[jisoo22]

Hello people!

I think I may have figured out a way to have a macro take text from a webpage and stick it into an access database. I have a bit of code I worked on but it doesn't work. Can someone at least tell me if the theory/idea is valid and possible? Here's the code:

Sub EnterText()
Dim Text1
Dim appAccess As Access.Application
Set appAccess = CreateObject("Access.Application&quot
DoCmd.OpenForm "Form1"
Text1.Value = ActiveXCtl0.OpenURL("

http://websiteurl/")

End Sub


Thanks!

 

[jonscott8]

Yes, you can request the HTML/TEXT if, for example, ActiveXCtl0 represents an object that can make an HTTP request, like the Internet Transfer Control.

You'll receive back the same thing as you would if you open the site in IE, and then select View Source. Jon Hawkins

 

[jisoo22]

Ok I ran into a snag on that one, apparently I have to buy a license if I want to use the MsInet control =P So I have an alternative route. I wrote a simple little perl script that pulls html off a webpage and sticks it into a text file. Now here's the thing: I need to get a macro to execute the perl script (using the command "perl script.pl" in a dos shell), then retrieve and open the text file, take the text and stick it into cells in an Access database spreadsheet.

I've done some VBA stuff in Excel and Outlook but I don't know a whole lot of Access. Can someone help me with the basic scructure of the coding?

Thanks,
Jisoo22

 

[jisoo22]

Ok so I think I figured it out partially, but I still get error messages (including invalid procedure call or argument). Here's the code I have so far:

Sub EnterText()
Dim fso, f
Dim y As String
Dim x As Double
Dim filespec As String
filespec = "C:\text.pl"
Set fso = CreateObject("Scripting.FileSystemObject&quot
Set f = fso.GetFile(filespec)
y = UCase(f.Drive) & "\"
x = Shell(y & "perl " & "text.pl&quot
End Sub


The error appears when it hits the 2nd to last line. What I'm trying to do is to tell it to execute the "perl" command after setting the directory to "C:\" so that it'll run the "text.pl" perl script. Can someone help me?

Thanks,
Jisoo22

End Sub

 

[jisoo22]

Doh, forgot to mention that the commands are being inputed within a DOS shell. Is it opening up a DOS shell? If not, can someone tell me how I can do that?

Thanks,
Jisoo22

 

[jisoo22]

Ok so disregard the original code. I finally figured out how to open the MS-DOS shell and to give it a command. Unfortunately I need to give it at least two commands -- 1. change the directory to "C:\Perl\Bin" then execute the command "Perl C:\text.pl". Does anyone know how to do that? Here's the new code I have:

Sub EnterText()
Dim fso, f, s
Dim x As Double
Dim filespec As String
filespec = "C:\Text.pl"
Set fso = CreateObject("Scripting.FileSystemObject&quot
Set f = fso.GetFile(filespec)
x = Shell("command.com /c cd\perl\bin", vbNormalFocus)
End Sub

Thanks!