Unnecessary Filter in Exported Query Result

[khwaja]

I used to produce an excel file without a hitch. But lately, when I run the same code, the code works fine and produces the excel file but funny enough when I open the spreadheet, it already has a filter on a field. I can still live with that but after I turn off the filter, the spreasheet does not seem normal as I can scroll up or down. My excdel and other files work fine. I have attched a copy of the spreadsheet produced by code. I amusing Excel 2002.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryInit", sPathFile, True

Some help will be appreciated.

 

[Kafrin]

Your problem scrolling is because, as well as adding a filter, you're also getting frozen panes. This means that the top set of rows are all frozen and you can only scroll the rows below them. However once you remove the filter, the number of frozen rows is too big to fit on the screen, so you can't see anything scrolling any more.

Your temporary fix is to open the spreadsheet and go to Window, Unfreeze panes.

Long term, I can't see why you're getting the filter and frozen panes in the first place. Is there any more to the code than just the TransferSpreadsheet action? Alternatively, have you been recording macros that have ended up in your 'personal workbook', meaning they may run every time you open any Excel file?

 

[khwaja]

Thanks. Yes I can make changes to Excel file as you described but I am mystified why should this be happening. As this is happening to all users of the database, it has nothing to do with the Excel set up. I have re produced the full code in case this may give you some clues.

Sub ExportInitExcel()

Dim sPath As String
Dim sFile As String
Dim sDateTime As String
Dim sExtension As String
Dim sPathFile As String

On Error GoTo E_Handle

sPath = "\\Nnorsogfas031\NSP\Future Store\"
sFile = "FutureStoresByInit"
'sDateTime = Format(Now(), "yyyymmddhhnn")
sExtension = ".xls"
sPathFile = sPath & sFile & sExtension

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryInit", sPathFile, True


MsgBox "Data exported to" & sPathFile & "successfully"
sExit:
Exit Sub
E_Handle:
Select Case Err.Number
Case 3044 ' The database that we are transferring the table to does not exist
MsgBox "'" & sPathFile & "' does not exist.", vbOKOnly, "Export cancelled"

Case Else
MsgBox Err.Description, vbOKOnly + vbCritical, Err.Number
End Select
Resume sExit

End Sub

 

[Kafrin]

Nope, there's nothing in the code to cause the problem.

A couple of ideas, but no guarantees...

1) Check the query itself, make sure it hasn't been saved with a filter on it. I don't think this would cause your problems, but it might be worth checking.

2) Try a different Excel format in the TransferSpreadsheet line, maybe acSpreadsheetTypeExcel8, and see if you still get the problem.

After that I can't think of anything else. If neither of those help, is it possible for you to upload a copy of your Access file (obviously removing any confidential data first)?

 

[khwaja]

Thanks. I just checked it but the issue seems to have fixed by itself. I no longer have the filters on. But there is something very interesting that has happended. The excel file now has an additional sheet in it with a pivot table on it (using status field) and that pivot table has a chart based on it on a seperate sheet. I find it amazing that such additional taks could take place without being asked. I am using exactly the same code to produce a seperate excel file by dept and that was prev showing the same issues and this seems to be working fine too but no additional sheets. I will see if I can send you a cut down version for your interest.