1

I am trying to find a way to allow a user to automatically have a certain table from an access database to export to an excel sheet based on the click of a command button on a form. After the table has been exported I want the excel sheet to automatically pop up. But I have other tabs that are already on the spreadsheet so is there a way to allow the table to only be exported to that one tab and have it automatically update when the user presses the command button. I have tried to make the command button using the macro so I wouldnt mess up the coding. I usually code VBA but I am stumped on this. Thanks so much!

Erik A
  • 31,639
  • 12
  • 42
  • 67
Brianna Cates
  • 337
  • 1
  • 7
  • 22
  • 2
    I believe this couldbe of interest to you http://stackoverflow.com/questions/1849580/export-ms-access-tables-through-vba-to-an-excel-spreadsheet-in-same-directory – David G Aug 12 '15 at 16:29

1 Answers1

0

You can open the book via VBA, add/modify the contents, then save it or save it elsewhere.

The below example will open an existing workbook, add some data, then save it as a new workbook and open the excel file on the screen for the user.

   'Open a workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Open("C:\Temp\ExcelFileName.xlsx")
   Set oSheet = oBook.Worksheets(1)

    'HEADINGS
    oSheet.Range("A1").Value = "Heading1"
    oSheet.Range("B1").Value = "Heading2"
    oSheet.Range("C1").Value = "Heading3"

   'Transfer the data to Excel
    oSheet.Range("A2").Value = "Text Data"
    oSheet.Range("B2").Value = 1.05
    oSheet.Range("C2").Value = Now()

   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Temp\ExcelFileName1.xlsx"

   oExcel.Quit
   Application.FollowHyperlink "C:\Temp\ExcelFileName1.xlsx"
JCro
  • 686
  • 5
  • 10