Exporting from Access to New Excel Spreadsheet

Results 1 to 2 of 2

Thread: Exporting from Access to New Excel Spreadsheet

  1. #1
    Join Date
    Dec 1969

    Default Exporting from Access to New Excel Spreadsheet

    Can anyone tell me how to script a function that will allow a user to export the contents of a table to a new spreadsheet? It would be a file that could be saved to their terminal. Any help would be greatly appreciated.<BR><BR>James

  2. #2
    Join Date
    Dec 1969

    Default RE: Exporting from Access to New Excel Spreadsheet

    James,<BR>There are lots of variations depending on exactly what you want to do.<BR>You can connect directly to an XL Worksheet in a Workbook and manipulate data using SQL. <BR>Or with the code below you push blocks of data into the worksheet.<BR>Another way is to loop through a row at a time. <BR><BR>You can even create XL Formula as strings in your SQL and put them into the Worksheet columns.<BR><BR>Anyway this is a start, I have used DAO so you will need to add it as a reference and you will also need to add Excel 9.0 Object Library (or whatever version)<BR><BR>Sub dacCreateWrkBk()<BR>Dim db As DAO.Database, rs As DAO.Recordset<BR>Dim stSQL As String<BR>Dim xlAp As Excel.Application, xlWkBk As Excel.Workbook<BR>Dim xlWkSht As Excel.Worksheet<BR>Dim xlRng As Excel.Range<BR>Dim varACs As Variant<BR>Dim intYlst As Integer, intRows As Integer<BR>Dim intY As Integer, intX As Integer<BR><BR> Set xlAp = New Excel.Application<BR>&#039;&#039;To use a Template stFile=Path & file of template<BR> &#039;Set xlWkBk = xlAp.Workbooks.Add(stFile)<BR>&#039;&#039;Or to open a new blank Workbook<BR> Set xlWkBk = xlAp.Workbooks.Add<BR>&#039;&#039;Again if Template open Desired Work Sheet eg NextYrBudget<BR> &#039;Set xlWkSht = xlWkBk.Worksheets("NextYrBudget") &#039;Open Budget<BR>&#039;&#039;Or if blank<BR> Set xlWkSht = xlWkBk.Worksheets(1) &#039;Open 1st Worksheet<BR><BR> stSQL = "Select * From tblTable1;"<BR><BR> With xlAp<BR> xlWkBk.Windows(1).Visible = True<BR> .WindowState = xlMaximized<BR> .Visible = True<BR> End With<BR><BR> Set db = CurrentDb()<BR> Set rs = db.OpenRecordset(stSQL, dbOpenSnapshot)<BR> With rs<BR> .MoveFirst<BR> intRows = 110 &#039;You can adjust this up until it errors<BR> &#039;size depends on how many colums and size of data in them<BR> intYlst = 1<BR> Do While Not .EOF<BR> &#039;Copy rs rows into Array<BR> varACs = .GetRows(intRows)<BR> &#039;Get Array boundaries ie size of the array<BR> intX = UBound(varACs, 1)<BR> intY = UBound(varACs, 2)<BR> &#039;Set Range for pasting the DataSet<BR> With xlWkSht<BR> .Activate &#039;Set selected Worksheet (xlWkSht as Active WkSht<BR> Set xlRng = .Range(.Cells(intYlst, 1), _<BR> .Cells(intYlst + intY, intX + 1))<BR> &#039;Paste Array into XLWkSht<BR> xlRng.FormulaArray = .Application.Transpose(varACs)<BR> intYlst = intYlst + intY + 1<BR> End With<BR> Loop<BR> End With<BR> MsgBox "A New Sales Budget Workbook has been with " & intYlst & " rows.", vbInformation<BR><BR> <BR> Set rs = Nothing<BR> Set db = Nothing<BR> Set xlWkSht = Nothing<BR> Set xlWkBk = Nothing<BR> Set xlAp = Nothing<BR><BR><BR>End Sub<BR><BR><BR>Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts