Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 11th, 2005, 02:44 PM
Registered User
 
Join Date: Dec 2005
Location: , , .
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

Reply With Quote
  #2 (permalink)  
Old December 14th, 2005, 07:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 10 Times in 9 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


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 07:17 AM
dispaly specific data from access datbase priyankkgupta Classic ASP Basics 3 December 1st, 2006 09: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 05:19 AM



All times are GMT -4. The time now is 02:13 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.