Output query to excel multiple times (1 Viewer)

wikusp

New member
Local time
Today, 14:26
Joined
Sep 15, 2016
Messages
3
New to this forum. I need assistance in the following:

I have Access query transferred to Excel. It executes fine the first time, then after it is as if it just give me the same info.

See below, I know it is simple, but thats the only way I can make sense of it.

Private Sub Command37_Click()

Dim outputFileName As String
outputFileName = "C:\Temp"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Full POB for Date entered", "C:\Temp\POB Template" & ".xlsx", True

MsgBox "Data exported to POB Template.xlsx"

Dim sPath As String
sPath = "C:\Temp\POB Template"
Shell "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE """ & sPath & "", vbNormalFocus

End Sub
 

Cronk

Registered User.
Local time
Today, 17:26
Joined
Jul 4, 2013
Messages
2,770
I don't use DoCmd.TransferSpreadsheet very much, but I do recall experiencing behaviour similar to that you describe.

I never did get around to figuring out the cause - it was easier to delete the file first.

If Dir("C:\Temp\POB Template.xlsx") >"" then
Kill "C:\Temp\POB Template.xlsx"
endif
 

wikusp

New member
Local time
Today, 14:26
Joined
Sep 15, 2016
Messages
3
In doing that, I will delete my file with all the calculations. I transfer the data to a sheet inside this file.
 

Cronk

Registered User.
Local time
Today, 17:26
Joined
Jul 4, 2013
Messages
2,770
If you only want to update a certain part of an Excel spreadsheet, you cannot do this with docmd.transferspreadsheet. It's all or nothing.

You can however use Automation in Access to loop through a recordset and place data into specified cells in a worksheet (search Excel automation on this site).

Alternatively, use your current method to export to a spreadsheet and link to it for your calculations.
 

Anakardian

Registered User.
Local time
Today, 07:26
Joined
Mar 14, 2010
Messages
173
How about using this method?

I had it in function for exporting the various things I needed to a data sheet in an excel file. I could then run the few calculations needed in excel from that. The majority of the calculations were done outside excel.

Code:
Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object

strSheetName = "Data"
strPath = "ThePathToYourFileWithFilenameAndExtension"


Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets(strSheetName)
xlWSh.Activate

'Gather your data in an export table containing precisely what you need. 

            'Move data to excel
            Set rst = CurrentDb.OpenRecordset("ThatExportTable")
            xlWSh.Range("AA3").CopyFromRecordset rst 'Change the reference to the top left cell where you want your data
            rst.Close
            Set rst = Nothing

'Clear your export table so it is ready for next time.
 

Users who are viewing this thread

Top Bottom