CODEWITHSUNDEEP
excelms-accessvbams-access-2010excel-2010
user525966
user525966

Reputation: 499

Refreshing Excel data from an MS Access query without wrecking references?

We have an Excel workbook that has a lot of summary sheets and such that all reference a core "Data" tab that we copy-and-pasted from an MS Access query. This data is referenced via several SUMIFS functions, a few PivotTables, and we have a Name associated with the columns of this tab, etc.

But we'd like to refresh this data regularly without wrecking everything. Is there VBA code that would accomplish this from MS Access? Ideally what it would do is clear the cells of the Data tab and then paste the query output there instead. I initially considered transferspreadsheet but this seems to replace entire sheets at a time and would destroy all these references.

Is there a "safer" way to refresh/export this data to the Excel worksheet?

Upvotes: 0

Views: 551

Answers (2)

Erik A
Erik A

Reputation: 32682

You can easily copy data to Excel by using the Range.CopyFromRecordset method:

Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.Open strPathToExcelFile
'excelApp.Workbooks(1).Worksheets("Data").UsedRange.ClearContents 'To clear existing content
excelApp.Workbooks(1).Worksheets("Data").Cells(1,1).CopyFromRecordset CurrentDb.QueryDefs("MyQuery").OpenRecordset
excelApp.Workbooks(1).Close
excelApp.Quit

Upvotes: 2

Jason
Jason

Reputation: 1

Do your users directly interact with the DATA tab in Excel, or does it only exist for the purposes of feeding data to the other tabs that do the calculations and tables and such? Because Access isn't great (that I know of) at feeding data into a particular Excel tab, but if you run an OutputTo command to feed the query data to a SEPARATE Excel sheet (different filename than your current Excel file) and then, instead of linking all the data in your current workbook's tabs to the DATA tab of that same workbook, link the data to the external sheet that Access created.

Or, if your users DO interact with the DATA tab directly, then link the data on the DATA tab to your external sheet created by Access.

An example function is below:

Function ExportDataAndOpenExcel()

'1: Delete the old Data sheet if it exists 
   If FileExists("C:\User\Desktop\Data.xlsx") Then
      SetAttr "C:\User\Desktop\Data.xlsx", vbNormal
      Kill "C:\User\Desktop\Data.xlsx"
   End If

'2: Export the data in query to Data.xlsx
    DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLSX, _ 
          "C:\User\Desktop\Data.xlsx"

'3: Open the Excel sheet with the tabs and calculations on it
    Dim oApp As Object
       Set oApp = CreateObject("Excel.Application")
       oApp.Visible = True
       oApp.workbooks.Open ("C:\User\Desktop\MyWorkbook.xlsx")

End Function

Upvotes: 0

Related Questions