CODEWITHSUNDEEP
ms-accessvbscriptvba
user3240517
user3240517

Reputation: 3

VBS file only works when double-clicked

I am trying to use the Windows 8 Location API in order to get GPS coordinates from a built-in sensor, and return these coordinates to MS Access. However, due to the unsupported variant types used in the API, Access is unable to reliably use its objects. I am trying to come up with a workaround using a VBScript file, and somehow returning the values to MS Access (using 2010 and 2013). The easiest way I could think of was to spit out a txt file to be read by Access and then deleted.

My VBS File works perfectly when I run it from Windows Explorer (double-clicking the file) but I can't find a way to make it work properly when running it from VBA code. Here is the VBS File:

Dim latlongfactory
Dim rptLong, rptLat
Dim report
Dim keepSleeping
Dim fs, f
Dim ts

Set latlongfactory = Wscript.CreateObject("LocationDisp.LatLongReportFactory", "llf_")
Set fs = Wscript.CreateObject("Scripting.FileSystemObject")

f = fs.BuildPath(CurrentDirectory, "gpsTempFile.txt")

keepSleeping = True
latlongfactory.ListenForReports(1000)

Sub llf_NewLatLongReport(report)    
    rptLong = report.Longitude
    rptLat = report.Latitude
    keepSleeping = False
End Sub

Do While keepSleeping
    Wscript.Sleep(20)
Loop

Set ts = fs.CreateTextFile(f, True)
ts.WriteLine rptLat & "," & rptLong
ts.Close

Set fs = Nothing
Set latlongfactory = Nothing
set report = Nothing

When run through VBA, it doesn't create the text file anymore, and I'm not entirely sure why. I tried adding a Msgbox at the end of the script just to see if the code was running. The Msgbox does come up.

The line I use to execute in Access VBA is:

Shell "Wscript """ & CurrentProject.Path & "\gpscoordinates.vbs""", 1

It runs the VBS file, but the text file isn't getting created and I cannot figure out why. Any help would be much appreciated!

Upvotes: 0

Views: 1144

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200213

If you want the output file to be created in the same folder in which the script resides, you can use the ScriptFullName property of the WScript object:

outputFolder = fs.GetParentFolderName(WScript.ScriptFullName)
f = fs.BuildPath(outputFolder, "gpsTempFile.txt")

Upvotes: 1

Related Questions