Exporting tables from Access to existing Excel worksheets that contain formulas

[LexpectoPatronum]

I need to export data from tables in Access to existing worksheets in an excel document. Each worksheet contains various formulas that are used to analyze the data being imported.

Previously, my co-workers and I have been copying and pasting the data from access into the excel worksheets, but in some cases this becomes quite cumbersome and leaves an extraordinary amount of room for human error.

I've been able to export the data ... but the problem is it keeps creating a new worksheet. I don't want a new worksheet OR to completely overwrite the existing worksheet.

For example, I will have columns "ISSUE_ID", "APPL_ID", and "EFF_DT". Ideally, I would see "ISSUE_ID" go to column A in excel, "APPL_ID" to column B, and "EFF_DT" to column C. Columns D through F contain calculations, the results of which are displayed in column G for review.

Hoping someone can help!

By the way ... although in the end I know what I'm doing, I have no formal training in programming, so you might need to "dumb it down" a bit for me and please be patient with me! Much appreciated

 

[namliam]

Look into the Docmd.Transferspreadsheet, it allows you to export data into specific tabs and specific ranges...

 

[noboffinme]

Hi LexpectoPatronum,

Here's a VBA script that is run from Access to a nominated Excel Workbook/Worksheet.

In VBA, an apostrophe (') is used to add comments to the code to help a reader understand what the code is doing. Use these comments to follow what the code is doing & help you understand VBA a little better.

You'll need to add the source path for your Excel Worksheet & adjust the number of headings in your data.

This code needs to be pasted into Access by going to the Design View of the Access Form you want to run this from & add a Button to the form, right click on the Button >> go to Properties >> Event tab >> Click on the Ellipsis ( ... ) & select Code Builder. This will open the Visual Basic editor & you can paste the below Code there. - Note only copy the code between the dotted lines.

Alternately, you can post the Access database file & I'll do this for you.

Sub export_to_excel() 
 
'------------------------------- Copy code below from this line 
 
'Copies the selected projects from the db to Excel
 
'Declare variables
Dim startrow, a, b, c As Integer
Dim mypath As String
Dim myvalue As String
Dim rec As Recordset
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim row As Integer
 
'Turn off pop up warnings
DoCmd.SetWarnings False
 
'Initialise the database
Set db = CurrentDb
 
'Add the path to the Excel file
mypath = ("C:\\Users/Peter/Desktop/Book1.xlsm")
 
Set wb = Excel.Application.Workbooks.Open(mypath)
 
'Open the Excel file
Excel.Application.Visible = True
 
'Set the source table from Access to be copied to Excel
Set rec = db.OpenRecordset("Your_Access_Table_Name")
 
'Set the Worksheet that will accept the copied data
Set ws = wb.Worksheets("excel_export_wsheet")
 
Sheets("excel_export_wsheet").Select
 
'Count the field headings for the Loop to use
c = CurrentDb.TableDefs("Your_Access_Table_Name").Fields.Count
 
'Paste the headings onto the Excel worksheet - adjust the below to suit your headings
ws.Cells(1, 1).Value = rec.Fields(0).Name
ws.Cells(1, 2).Value = rec.Fields(1).Name
ws.Cells(1, 3).Value = rec.Fields(2).Name
ws.Cells(1, 4).Value = rec.Fields(3).Name
ws.Cells(1, 5).Value = rec.Fields(4).Name
ws.Cells(1, 6).Value = rec.Fields(5).Name
ws.Cells(1, 7).Value = rec.Fields(6).Name
ws.Cells(1, 8).Value = rec.Fields(7).Name
ws.Cells(1, 9).Value = rec.Fields(8).Name
ws.Cells(1, 10).Value = rec.Fields(9).Name
ws.Cells(1, 11).Value = rec.Fields(10).Name
ws.Cells(1, 12).Value = rec.Fields(11).Name
ws.Cells(1, 13).Value = rec.Fields(12).Name
ws.Cells(1, 14).Value = rec.Fields(13).Name
ws.Cells(1, 15).Value = rec.Fields(14).Name
ws.Cells(1, 16).Value = rec.Fields(15).Name
ws.Cells(1, 17).Value = rec.Fields(16).Name
 
'Start pasting on Row 2
startrow = 2 'Cell Row number
 
'Start pasting in Column 'A'
a = 1 'Cell Column Column number
b = Nz(0, "") 'Table Field number
'b = "" if the field is blank
'c = count of table fields
 
'Loop through the code until End of File (EOF)
Do Until rec.EOF
 
Do Until b = c
 
ws.Cells(startrow, a) = rec.Fields(b).Value
a = a + 1
b = b + 1
 
'Loop through the data & Paste into Excel until finished
Loop
 
Cells.Select
With Selection
.WrapText = False
End With
startrow = startrow + 1
rec.MoveNext
a = 1
b = 0
Loop
 
'MsgBox "Data Exported!", vbCritical, "Export Successful"
 
'Save workbook & Unset variables
wb.Save
wb.Close
Set ws = Nothing
Set rec = Nothing
Set wb = Nothing
Set db = Nothing
 
'Turn on pop up warnings
DoCmd.SetWarnings True
 
'-----------------------------------------
'Copy code to the above line
 
End Sub