Opening And Saving Chrome Shortcust

[DMT Dave]

Hi Guy's i wonder if it is possible to find if Chrome Shortcut exists in FilePath Called FileName then

If Doesn't exist, Open Chrome and add myURL which will put the Me.PostCode at the end of the URL then save the file to: FilePath & FileName

If It Does Exist Then Open FileName And The URL = myURL & "/" & Me.PostCode



So the result would be, Checking if a file exists under the job date then add another postcode and save it

Next time the file is opened on a different postcode record, there would be 3 added to it now

Hope all of this makes sense

Dim CrtFile As String, FileName As String, FilePath As String, ChromePath As String, ChromeFile As String, cPC As String, myURL As String

ChromePath = "C:\Program Files (x86)\Google\Chrome\Application\Chrome.exe"
myURL = "https://www.google.co.uk/maps/dir/" & Me.PostCode
FileName = Format(Me.txtJobDate, "dd-mm-yyyy") & ".lnk"
FilePath = "T:\My Folder\Routes\"


If Forms!frmMainMenu!txtIndexNo = "0" Then
If Forms!frmMainMenu!cboOptions = "Plan Routes" Then


OpenFile = Shell(ChromePath, vbMaximizedFocus)
Name ChromePath As FilePath & FileName
End If
End If

[Gicu]

Dave, I see nobody jumped in so I'll try my best. Looks like to you save the links to your T:\ drive so checking for one would be fairly easy using Dir:

If Dir( FilePath & FileName)="" then 
'file does NOT exists

Else
'file exists

End if

Now I don't get the reference to three postcodes, can you please explain?\

Cheers,
Vlad

[DMT Dave]

Hi Vlad thank you yes, I have programmed our systems to do a lot file moving, renaming and killing files to their relevant folders for customers, i have many additions and tweaks to do, one of is the email listing in which that is a slightly longer term change so i will be going back to that at some point, on this one, we plan routes for about 20 to 30 destinations weekly, once a week, i open google maps than start entering postcodes to view on map to start working times out for clients then call all clients with an ETA.

I have all of the data on a form in datasheet view, the idea of this one is a time saving idea

Instead of opening Google maps manually and start looking at my report to see the postcodes and start entering them, what i was thinking is:

1: Save my self a Google Chrome Template called New Routes in "T:\My Folder\Routes", (the folder isn't actually called my folder, it is the company name but changed it on here)
2: Job Date (on change) Check to see if there is a file called Routes 21-08-20.lnk (21-08-20 is formatted from the job date)
3: If There isn't a file called (Routes 21-08-20)
4: Copy the file called New Routes and Save as "Routes " & Format JobDate etc...
5: If there is a file called "Routes " & Format JobDate etc....
6: Open the File and add the postcode (me.postcode)

The only issue that i know about is google maps will only allow 10 postcodes

Does this all make sense ?

So the result would be is after the change of date, a google routes is created just to open and view to prevent printing a report with all postcodes on and start entering them

Much appreciated, i will follow up with an idea that i have used in the past on another post

[DMT Dave]

Hi Vlad, i have used this before but would probably need a lot of adjustment, this is on a different database, this should open a new browser and add the destinations based on the input of postcodes (qty) you still have to type the postcodes in via an input though

Dim mPC As String, mPC2 As String, mPC3 As String, mPC4 As String, mPC5 As String, mPC6 As StringDim mPC7 As String, mPC8 As String, mPC9 As String, mPC10 As String, stAppName As String
Dim mPCQTy As Long
Dim mPC11 As String, mPC12 As String, mPC13 As String, mPC14 As String, mPC15 As String, mPC16 As String, mPC17 As String, mPC18 As String, mPC19 As String, mPC20 As String
Dim mStr As String, mFin As String, mPlan As String, rsBody As String, MySQL As String, TotalPCs As String, Col As String, ColRed As String
Dim mDate As Date


Dim myPostCodes As String, MyURL As String
Dim iQty As Integer, MyInput As Integer
Dim rs As DAO.Recordset


Col = "Collection"
ColRed = "Collection Ready"
mPlan = "Planning"
mDate = Format(Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1, "mm/dd/yyyy")


MyInput = InputBox("Do You Want To View Map With:" & vbNewLine & vbNewLine & _
"1" & vbTab & "What Is Left Planning" & vbNewLine & vbNewLine & _
"2" & vbTab & "The Full Week", "ENTER MAP OPTION")


Select Case MyInput
Case 1
iQty = DCount("PostCode", "qryPostCodes")
Case 2
iQty = DCount("PostCode", "qryPostCodesAll")
End Select


If MyInput = "1" Then
Set rs = CurrentDb.OpenRecordset("Select tblEdit.PostCode From tblEdit WHERE ShipmentDate = #" & mDate & "# And Status = '" & mPlan & "' Group By PostCode;")
Do Until rs.EOF
rs.MoveFirst
myPostCodes = rs.Fields("PostCode")
MyURL = "https://www.google.co.uk/maps/dir/" & myPostCodes
Loop
OpenFile = Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE " & MyURL, vbMaximizedFocus)
End If


If MyInput = "2" Then
Set rs = CurrentDb.OpenRecordset("Select tblEdit.PostCode From tblEdit WHERE ShipmentDate = #" & mDate & "# And Status <> '" & Col & "' Or Status <> '" & ColRed & "' Group By PostCode;")
rs.MoveFirst
myPostCodes = rs.Fields("PostCode")
If MsgBox(rs.RecordCount & " " & "PostCodes", vbOKCancel, "POSTCODES COUNTED") = vbCancel Then
DoCmd.CancelEvent
Else
Do Until rs.EOF
MyURL = "https://www.google.co.uk/maps/dir/" & myPostCodes
Loop
OpenFile = Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE " & MyURL, vbMaximizedFocus)
End If
End If

[DMT Dave]

Hi Vlad, this is what i have saved manually in Routes folder

[Gicu]

Hi Dave,

So you have a table holding the "jobs", I assume there is a way to link each job to an address with a post code. Is your intent to create an optimized daily route for a driver (lets say delivering 5 jobs)? For that I think you would need to allow for some sort of "job order" and run some iterations to get the minimal total time or distance from the mapping site.

I don't really understand why would you save the link when you can open a browser and get the route immediately.

Here is what I use to get directions from a fixed location to another:

Private Sub cmdGoogleMaps_Click()
Dim strFrom As String, strTo As String
Dim MyAddress
On Error Resume Next


MyAddress = Replace(Me.[School Address], "#", " ")
strFrom = Forms![Main Switchboard Form]!frmInstitutionInfo![Street] & ", " & Forms![Main Switchboard Form]!frmInstitutionInfo![City_Province_Postal] '& _
             ", " & Forms![Main Switchboard Form]!frmInstitutionInfo![Institution Postal]
strTo = " to: " & MyAddress & ", " & Me.[School City] ' & ", " & Me![School PC]
Application.FollowHyperlink "http://www.google.com/maps?q=" & strFrom & " " & strTo

End Sub

Instead of the full address you could use postal codes in the "from" and "to".

I know Colin on this forum (https://www.accessforums.net/member.php?u=36976) has lots of experience with UK mapping. might be worth getting in touch with him.

Can you show a route you produced manually with some postcodes?

Cheers,

[DMT Dave]

Hi Vlad, I think i am close with this, my testing idea is to check if in tblRouting the postcode exists then if so, open it from LastURL, if it doesn't exist then add to the table and open it from NewURL, i am just slightly lost with InstR option, the following is coming up with Run time error 13 type mismatch, i must not be correct with checking by InStr function ?

The Bold line is saying the type mismatch error

Dim myPC As String, stAppName As String, myURL As String, MyURLDate As String, LastURL As String, newURL As String, CurrURL As StringDim myStr As String
Dim rs As DAO.Recordset
Dim i As Integer, iLastRec As Integer, NextRec As Integer
Dim MyDate As Date


MyDate = Format(Now(), "dd/mm/yyyy")
stAppName = "C:\Program Files (x86)\Google\Chrome\Application\Chrome.exe"
iLastRec = DMax("RecordNo", "tblRouting")
LastURL = DLookup("URL", "tblRouting", "[RecordNo] = " & iLastRec)
myPC = Me.PostCode
myStr = InStr(LastURL, myPC)


If InStr(myPC, myStr) <> vbNullString Then
MsgBox (myPC & " " & "Already Saved")
OpenFile = Shell(stAppName & LastURL, vbMaximizedFocus)
End If


If InStr(myPC, myStr) = vbNullString Then
Set rs = CurrentDb.OpenRecordset("Select * From tblRouting")
With rs
.Edit
!RemDate = MyDate
!Url = LastURL & "/" & myPC
.Update
.Close
End With
newURL = DLookup("URL", "tblRouting", "[RecordNo] = " & iLastRec)
OpenFile = Shell(stAppName & newURL, vbMaximizedFocus)
End If

[Gicu]

Hi Dave,
Instr returns a number, try

If InStr(myPC, myStr) >0 '<> vbNullString Then
MsgBox (myPC & " " & "Already Saved")
OpenFile = Shell(stAppName & LastURL, vbMaximizedFocus)
Else
      Set rs = CurrentDb.OpenRecordset("Select * From tblRouting")
      With rs
          .AddNew   '.Edit
           !RemDate = MyDate
            !Url = LastURL & "/" & myPC
            .Update
           .Close
       End With
       newURL = DLookup("URL", "tblRouting", "[RecordNo] = " & iLastRec)
        OpenFile = Shell(stAppName & newURL, vbMaximizedFocus)
End If

Also please note that I've changed the recordset .Edit to .AddNew.

Cheers,

[DMT Dave]

Hi Vlad, thank you very much, my OpenFile is empty

Is it easy enough to adapt:
stAppName being the path to Chrome
strAddress should replace strfile as google maps/Dir Postcode/Postcode etc
MyURL being the last one added to the table

but its debugging at OpenFile, would you have a more reliable suggestion ?

[DMT Dave]

strFile = "https://www.google.co.uk/maps/Dir/"
LastURL = strFile & DLookup("URL", "tblRouting")

Just added this in so chrome picks up the address and adds the postcodes stored in the table

[Gicu]

Sorry Dave, but I don't follow you. Can you show me your tblRouting table? LastURL = strFile & DLookup("URL", "tblRouting") will return the URL in the first record in the table. Do you empty the table at some point?
Cheers.

[DMT Dave]

Hi Vlad, thank you again for your response, i am going to have to come back to this another time, kind regards