Write Data From a Recordset into an EXCEL Worksheet using EXCEL’s CopyFromRecordset (VBA)
Code to open a recordset for the data that are to be written into a worksheet in an EXCEL file (for this example, the EXCEL file does not already exist), and then to use EXCEL’s CopyFromRecordset method to copy the data from the recordset into the first worksheet in that EXCEL file, with each record being written into a separate row in the worksheet. The code allows for a header row to be created in the worksheet if this is desired. This code example uses “late binding” for the EXCEL automation.
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strPathFileName As String, strWorksheetName As String
Dim strRecordsetDataSource As String
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False
' Replace C:\Filename.xls with the actual path and filename
' that will be used to save the new EXCEL file into which you
' will write the data
strPathFileName = "C:\Filename.xls"
' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
strRecordsetDataSource = "QueryOrTableName"
' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = True
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
On Error GoTo 0
' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True
' Create a new EXCEL workbook
Set xlw = xlx.Workbooks.Add
' Rename the first worksheet in the EXCEL file to be the first 31
' characters of the string in the strRecordsetDataSource variable
Set xls = xlw.Worksheets(1)
xls.Name = Trim(Left(strRecordsetDataSource, 31))
' Replace A1 with the cell reference of the first cell into which the
' headers will be written (blnHeaderRow = True), or into which the data
' values will be written (blnHeaderRow = False)
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
' Write the header row to worksheet
If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Set xlc = xlc.Offset(1,0)
' copy the recordset's data to worksheet
Set rst = Nothing
Set dbs = Nothing
' Save and close the EXCEL file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
I would like to share with you simple hint – How to use Google Earth as a geocoding tool.
You should have list with addresses and names of the points in Microsoft Exel file.
.KML format have couple of structures and one of them is:
<?xml version="1.0" encoding="UTF-8"?>
So we should take the name of the point and the address and we are putting it in this frame structure.
To do this we have to create simple macro which will create .KML file with the names and the addresses from our Microsoft Excel file.
' GenerateKML Macro
' Macro recorded 27/02/2010 by ipt
' Set file details
Set filePath = [File_details!C2]
' Set document name
Set docName = [File_details!C3]
Open filePath For Output As #1
'Write header to file
outputText = [File_details!C5] & docName & [File_details!C6]
Print #1, outputText
'Start to loop through stations
For Each cell In [Data!A2.A50001]
pmName = cell.Offset(0, 0)
pmAddress = cell.Offset(0, 1)
If pmName = "" Then
'Create a placemark
outputText = [File_details!C8] & pmName & [File_details!C9] & pmAddress & [File_details!C10]
Print #1, outputText
'Write footer to file
outputText = [File_details!C12]
Print #1, outputText
We are creating two sheets – Sheet “data” – is where we have to paste the names and the addresses
The second Sheet is called “File_Details” and in this sheet we should describe the structure of the .KML file.
We are describing output file name and directory also.
The macro will Loop thru the record set from “Data” Sheet and it should Save data as .KML file.
When you open the file with Google Earth it will start geocoding. For approximately 2500 POI – raw addresses with removed fullstops it took 30 minutes to geocode
To get the geocoded data you have save your file as .KML from Google Earth in specific directory.
After that you opening saved file with Microsoft Excel.
Microsoft Excel will ask you to open this file as an .xml table(picture)
And opening the file you should see something like:
Copy POI and coordinates and bring them back in Microsoft Excel by “Paste special as value”.
By Alexandra Levich, Product Manager
Cross-posted from the Chromium Blog
We recently expanded the reach of the Chrome Web Store from the U.S. to 24 more countries. Developers from around the world have already launched successful apps in the Chrome Web Store to US users. Now all developers can reach a global user base.
What makes this global reach even more interesting is the global payments infrastructure that goes along with it. The store allows developers from 20 countries to sell apps in the store, and users to buy apps in their local currency. We also recently launched the In-App Payments API, which allows developers (U.S.-only for now; international soon) to sell virtual goods in their apps. Integration is easy and transaction fees are only 5%. Graphicly, an early user of in-app payments, saw its net revenues double after starting to use the API and experienced an even bigger rise in profit margins due to increased conversions and lower transaction fees.
In keeping with our international theme, we’d like to highlight a few developers from different parts of the world who have utilized Chrome’s global reach to find success in the store:
- Audiotool is an online music production app that was built by a team of German developers. They saw the Chrome Web Store as a way to present their app to an international audience. Audiotool’s traffic increased by 20% after launching in the store, and this motivated the team to release another app in the store.
- Psykopaint is the brainchild of French developer Mathieu Gosselin. The Chrome Web Store provided Mathieu an opportunity to get his photo painting app noticed outside of France. Traffic to Psykopaint has jumped by 700% since it launched in the store and Mathieu has found that Chrome Web Store users tend to be more engaged than other users.
- Finally, Nulab, a Japanese company, launched its online diagramming app, Cacoo, in the store to expand its user base outside Japan. In just a few months after Cacoo was released in multiple languages in the Chrome Web Store, the app already accounts for 20% of Cacoo’s user base.
The experience of Audiotool, Psykopaint and Nulab shows that no matter where you’re located, you can always find a global audience for your applications in the Chrome Web Store. To learn more about the stories of these and other successful Chrome Web Store developers, read our case studies. And if you want to find out more about posting your app in the store, visit our documentation at code.google.com/chrome/webstore.
Alex Levich is a product manager working on Chrome Web Store.
Posted by Scott Knaster, Editor