Examples for Exporting Access table or query to EXCEL Workbook Files Part 6

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.

[php]
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
End If
Err.Clear
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
Next lngColumn
Set xlc = xlc.Offset(1,0)
End If

‘ copy the recordset’s data to worksheet
xlc.CopyFromRecordset rst
End If

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

‘ Save and close the EXCEL file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.SaveAs strPathFileName
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

[/php]

Google Maps: The Hiroshima Peace Memorial

Known as the “Atomic Bomb Dome,” the Memorial bears witness to the devastation of nuclear war. The once proud pre-war structure nearly destroyed by the bomb still stands unrestored — a monument to those lost and a powerful symbol of peace.

Google went one step further, making new imagery of the interior of the Memorial available via its Place page. While millions have visited the Memorial, you can only safely view it from the outside, standing behind a fence, because of the extent of the damage.

However iconic the Dome may be, it’s difficult to comprehend the magnitude and scope of the devastation from the outside of the building. But with the support of the Hiroshima city government, we gathered hi-resolution imagery that actually enables you to “walk” through the building room by room, using the same technology we’ve used to photograph other historic monuments around the world. The difference here is that while you can wander the gardens of Versailles or streets of Rome in person, the only way you can access the interior of the structure is digitally.

 

We launched this imagery in Japan on August 5th, one day before the 66th anniversary of the bombing of Hiroshima. You can access this collection via the Memorial’s Place page, or start exploring via this link. As with all of our special collections, we hope this imagery helps people around the world virtually travel to places they’d never otherwise have a chance to see.

School Projects, Geospatial Data and Content Management

 

WeoGeo has over 8 terabytes of free and inexpensive data available in the WeoGeo Market for inclusion in your analysis. Just this week we uploaded some great data from the State of Hawaii on Hawaiian Natural Areas and data from the Center for Disease Control and Prevention on U.S. Diabetes and Risk Factor Prevalence.

Hawaiian Natural Areas

We’ve also have the complete USGS National Hydragraphy Dataset and USGS Earthquake, Fault and Seismic Hazard data available for customization. Bonus points for using the WeoGeo Tools for ArcGIS to import these datasets into your ArcMap projects.

Another great option for students is our WeoGeo Library. Generally after the end of the school year, students need to archive off their projects to some personal stoarge device. Students using WeoGeo Library know their projects are available semester after semester no matter where they are. Since WeoGeo Library is a system of record, you’ll always have them at hand. My masters thesis was stored on a Brother Word Processor which meant that the minute I lost access to that hardware device, I lost all my hard work 2. That’s why a real geospatial content management system like WeoGeo is the best way to manage your school work. Plus you can get started today, for free.

Browsing WeoGeo