DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: VB code to export data to Excel

  1. #1
    Join Date
    Oct 2009
    Posts
    2

    VB code to export data to Excel

    I have placed a command button on my form in MS Access to export specific data to populate specific cells in Excel. I won't be pulling ALL the data from my main table, just specific fields, and auto-populating specific cells in an Excel document (template) that I have created.

    I don't know how to write code at all, but I found this on the web:
    Code:
    Dim appExcel As Excel.Application
      Dim wbk As Excel.Workbook
      Dim wks As Excel.Worksheet
      Set appExcel = GetObject("Excel.Application")
      Set wbk = appExcel.Workbook.Open("L:\Bper Spec\Cione\TEST\TEST (RSA).xls", , True)
      Set wks = wbk.Worksheet("RSA")
    With xlSheet
    .Cells(1, 1).Value = "I am in Row 1, Column A"
    .Cells(3, 3).Value = "I am in Row 3, Column C"
    'and so on, filling in the cells
    End With
    When I try to run the code, I get a compile error "Variable not defined" at the 5th line from the bottom "With xlSheet"

    I've researched and researched to find a solution, but no dice.

    Any help would be appreciated.

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    with xlSheet should be with wks

  3. #3
    Join Date
    Oct 2009
    Posts
    2
    Tried it; got a runtime error.

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Changes in red - New code should look like this:
    Code:
      Dim appExcel As Excel.Application
      Dim wbk As Excel.Workbook
      Dim wks As Excel.Worksheet
    
      Set appExcel = GetObject("Excel.Application")
      Set wbk = appExcel.Workbooks.Open("L:\Bper Spec\Cione\TEST\TEST (RSA).xls", , True)
      Set wks = wbk.Worksheets("RSA")
      With wks
        .Cells(1, 1).Value = "I am in Row 1, Column A"
        .Cells(3, 3).Value = "I am in Row 3, Column C"
        'and so on, filling in the cells
      End With
    By the way: Even the corrected code will continue to error because you open the workbook Read Only
    Code:
     Set wbk = appExcel.Workbooks.Open("L:\Bper Spec\Cione\TEST\TEST (RSA).xls", , True)

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by ChrisCione View Post
    got a runtime error.
    Ron's code will probably solve your problem for this, but I would suggest that in the future if something like this (an error) occurs that you specify what the error number is and what the error verbage is.

    There are only about a zillion differnent error possibilities. Telling us specifically which error you are getting helps enormously.

    Thanks.

Similar Threads

  1. Replies: 5
    Last Post: 07-21-2008, 09:41 PM
  2. Replies: 2
    Last Post: 12-27-2006, 11:43 PM
  3. Microsoft's C++ bigotry
    By Phil Weber in forum .NET
    Replies: 632
    Last Post: 10-01-2003, 12:00 AM
  4. Need to reduce network trips and speed up ADO VB code!!
    By Richard Lloyd in forum VB Classic
    Replies: 8
    Last Post: 06-22-2001, 04:52 AM
  5. Replies: 90
    Last Post: 04-17-2001, 12:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center