Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 11th, 2005, 03:44 PM
Registered User
 
Join Date: Dec 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to VBNoob Send a message via MSN to VBNoob
Default Export Access Data to a Specific Cell in Excel.

I am really having a hard time learning what code I need to use to Export Customer Info and Address into a Contract I made in Excel. I just want a button in access that takes the customer info and puts it into a specific cell in Excel. For Example. Last name into 3,G. First name into 4,G. Etc. Please help if you can! Thanks, Matt

 
Old December 14th, 2005, 08:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Here’s a way to go that doesn’t involve reams of code. Use a few Named Cells in an Excel workbook template, then use ADO to connect to the workbook and write some values to the Named Cells. ShellExecute is an API call that makes a copy of your template, then opens the workbook after the values are inserted.

On your workbook, be sure and name the cell above the cell you want your values written to. So if you want First_Name data written to cell C:3, name cell C:2 “First_Name”.

I just hard-coded the values in the SQL statements for simplicity. You’d need to build your own SQL insert commands from values pulled from ADO recordset fields or GUI controls.

Code:
Option Compare Database

Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
  (ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) As Long

Private Const SW_NORMAL = 1

Private strContactDataTemplate As String  'Path to ContactData Template

Public Sub InsertData()

    Dim RetVal As Long

    strContactDataTemplate = Application.CurrentProject.Path & "\Template\ContactData.xlt"

    FileCopy strContactDataTemplate, Application.CurrentProject.Path & "\Result\ContactData.xls"

    'Open ADO connection to Excel workbook
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & Application.CurrentProject.Path & "\Result\ContactData.xls;" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""

    'Insert values into Named Cells in Excel workbook.
    oConn.Execute "Insert into First_Name Values ('Bob')"
    oConn.Execute "Insert into Last_Name Values ('Bedell')"
    oConn.Execute "Insert into Title Values ('Contract Manager')"

    'Close the connection
    oConn.Close

    'Open the workbook
    DoEvents

    On Error Resume Next
    RetVal = ShellExecute(0, "open", Application.CurrentProject.Path & "\Result\ContactData.xls", "", "C:\", SW_SHOWNORMAL)

End Sub
HTH,

Bob







Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Get Specific Cell Value of Data Grid in C# akumarp2p C# 1 February 1st, 2007 08:17 AM
dispaly specific data from access datbase priyankkgupta Classic ASP Basics 3 December 1st, 2006 10:35 PM
How To Export a specific Excel Sheet as a csv file mrjits Excel VBA 5 August 1st, 2006 03:04 PM
Export Mircosoft Excel Data to Access Table tiyyob Excel VBA 0 January 11th, 2006 06:19 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.