Create an export file using user generated title and export data t

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

The code below will export the recordset data to a know filename and known
path.

I want to be able to have the user create the filename using a message box,
and then have the code incorporate that filename, add a .kml extenstion to
the filename and then export the recordset output to it.

If at all possible, I would love to be able to use the standard windows file
system dialog boxes, which would allow for a filename and the ability to
change paths. If not, I would gladly settle for just the dynamic filename
naming and export.

these are the lines that contains the pre-exisitng filename:

Open "W:\Folder\Addresses.kml" For Output As #lngFN


Dim stAppName As String
stAppName = "C:\Program Files\Google\Google Earth\GoogleEarth.exe
W:\Folder\Addresses.kml"
Call Shell(stAppName, 1)



here is my exisitng code
***********************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strStartRun_No As String
Dim strEndRun_No As String
Dim FileTitle As String
' Dim strRun_No As String
Dim lngFN As Long


'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

strStartRun_No = InputBox("Enter the lower Run No")
strEndRun_No = InputBox("Enter the higher Run No")
FileTitle = InputBox("Enter the file title")

If Len(strStartRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML_Run_Titles")
qdf.Parameters("StartRun") = strStartRun_No
qdf.Parameters("EndRun") = strEndRun_No

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>" & FileTitle & "</name>"

Print #lngFN, "<Folder>"
'Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
'Print #lngFN, rs.Fields("KML_Address")
Dim strWork As String

strWork = rs.Fields("KML_Address")
strWork = Replace(strWork, "&", "and")
'strWork = Replace(strWork, "&", "&")
strWork = Replace(strWork, "'", "&apos;")
'strWork = Replace(strWork, "<", "<")
Print #lngFN, strWork

rs.MoveNext
Loop
rs.Close
'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
Close #lngFN
End If

On Error GoTo Err_Google_Earth_Points_Click

Dim stAppName As String

stAppName = "C:\Program Files\Google\Google Earth\GoogleEarth.exe
W:\Folder\Addresses.kml"

Call Shell(stAppName, 1)

Exit_Google_Earth_Points_Click:
Exit Sub

Err_Google_Earth_Points_Click:
MsgBox Err.Description
Resume Exit_Google_Earth_Points_Click
*****************************
 
Dale,

I just realised that this method will not work with what I want to do. My
code as it is, has the benefit of parsing the filename straight to the
application (Google Earth), so I think the best solution is to have a method
of having a simple Access 'Message Box' pop-up asking for the filename, and
then have that filename merge with the line:
stAppName = "C:\Program Files\Google\Google Earth\GoogleEarth.exe
W:\Folder\Addresses.kml"
Click to expand...

do you know how I can get the string variable into this line?
 
Assuming you grab the code to which Dale pointed, you'd need something like:

Dim stAppName As String
Dim strFile As String
Dim strFilter As String

stAppName = """C:\Program Files\Google\Google Earth\GoogleEarth.exe"""

strFilter = ahtAddFilterItem(strFilter, "KML Files (*.KML)", "*.KML")
strFile= ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

If Len(strFile) > 0 Then
Call Shell(stAppName & " """ & strFile & """", 1)
End If


Note the extra double quotes there: they're to handle spaces in the file
path. There are three double quotes at the beginning and end of the
statement assigning the value to stAppName. In the call to the Shell
statement, there are three double quotes in front of strFile, and four
double quotes after it.
 
Douglas,

Thanks for your timely input on this, and an answer for how to run the
filename with the App which I plan to use extensively for another similar
project.

But for now, with thisproject I want to continue with the simple 'message
box'. I have managed to incorporate the filename string from the message box
into the OPen file argument. But for some reason though the file is created
and is correct, it will not open into Google Earth; it's as if the file is
not closed before going into google earth; but when I run the old code which
uses a fixed/known filename, it opens perfectly in GE.

Here is the code that opens in GE:

Dim stAppName As String
stAppName = "C:\Program Files\Google\Google Earth\GoogleEarth.exe
W:\Folder\Addresses.kml"
Call Shell(stAppName, 1)


Here is the code that doesn't open:

Dim stAppName As String

stAppName = "C:\Program Files\Google\Google Earth\GoogleEarth.exe
W:\Folder\" & strFileName & ".kml"""

Call Shell(stAppName, 1)
 
Douglas,

I have since solved the 'Message Box' method, with this:

Dim stAppName As String

stAppName = "C:\Program Files\Google\Google Earth\GoogleEarth.exe
W:\Folder\" & strFileName & ".kml"

Call Shell(stAppName, 1)


Thank you, and Dale for your help.
 
To be honest, I'm surprised that that works due to the presence of spaces in
the path.

I would think that you'd need

stAppName = """C:\Program Files\Google\Google Earth\GoogleEarth.exe"" " &
"W:\Folder\" & strFileName & ".kml"
 
Back
Top