Exporting contacts to excel

[frosty13]

Hi!! I have customized a contact form with user defined fields and am having trouble exporting to excel. The fields that end up in the spreadsheet are default fields that have nothing to do with my form. Is there a way to export user defined fields? Mapping Custom Fields yeilds no results.

Thanks

 

[dianemarie]

Hi Frosty, I would suggest displaying your contact folder in a table view. Then go to View, Current View, Customize Current View and click on the Fields button. Then change the "Select available fields from" drop-down list to User Defined fields. Add those fields to the view, and remove any fields you don't want by using the Add and Remove buttons. Once your table is displaying all the fields you want, click anywhere on the table and do a CNTRL+A. This will select your entire table. Copy and paste it into Excel. There may be a better way, but this will get you what you want.

 

[DaleWatson123321]

Frosty,

I have an Access/Excel “interface” for updating an Employee List, wherein I have code to automatically export data from the Access file each time anyone loads that file. The data is exported to a SEPARATE “constant filename” (called "Contact_Data.xls&quot.

Then, with the Excel Employee List file, I have code in a “Workbook Open” event which automatically updates the data from the “Contact_Data.xls” file.

Below are the two sets of VBA code – the first for Access and the second for Excel.

I’ve included the entire code, some of which might not apply to your scenario, and some of which refers to specific worksheet names and range names – which you would naturally have to modify. Hopefully, these examples will at least serve to give you an appreciation for the process of “transferring data” from Access to Excel in an “automatic” way.

If you find the code useful (or if you don’t), please advise as to how you make out.

Regards, …Dale Watson dwatson@bsi.gov.mb.ca


ACCESS code

This is an “On Close” Event Procedure for a “Main Menu” form in Access. It gets activated every time a user uses the form/file.

Private Sub Form_Close()
' Export Employee Data to Excel file.
DoCmd.TransferSpreadsheet acExport, 8, "Contact_List_Qry", "C:\Employee Database\Contact_Data.xls", True, ""
End Sub

EXCEL code

This is code is a “Workbook Open” event in an Excel file called “Contact List”.

Dim FirstCell As String
Dim LastCell As String
Dim LastRow As String
Dim LastColumn As String

Sub Workbook_Open()
' - For importing "Contact_Data" - data exported from the Employee Database
' - last modified May 5, 2001
Application.ScreenUpdating = False
Workbooks.Open FileName:="C:\Employee Database\Contact_Data.xls"
Range("A1:AZ1000&quot.Select
Selection.Copy
ActiveWindow.ActivateNext
Sheets("BSI_Contacts&quot.Select
Range("A20&quot.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks("Contact_Data.xls&quot.Close SaveChanges:=False
Application.DisplayAlerts = True
Sheets("BSI_Contacts&quot.Select
Range("A1&quot.Select
Set_Data_Range
Format_Data
Format_Toprow
Sort_Name
Update_Lists
ActiveSheet.PageSetup.PrintArea = "data"
Range("a1&quot.Select
Application.ScreenUpdating = True
End Sub
Sub Set_Data_Range()
Range("data&quot.Select
FirstCell = ActiveCell.Address
Get_Last_Row
LastColumn = "I"
LastCell = LastColumn & LastRow
Data_Range = FirstCell & " : " & LastCell
Range(Data_Range).Name = "Data"
End Sub

Sub Get_Last_Row()
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(3, 0).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
End Sub

Sub Format_Data()
Range("data&quot.Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

With Selection.Font
.Name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub

Sub Format_Toprow()
Range("toprow&quot.Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
End Sub