Cannot expand named range Office 365 (1 Viewer)

namliam

The Mailman - AWF VIP
Local time
Today, 05:47
Joined
Aug 11, 2003
Messages
11,680
We recently upgraded office from 2010 to Office 365 and now after years of proper service, after upgrading from version to version, all of sudden my export from Access to Excel is failing.

I have a number of exports to the same workbook creating worksheets.
The workbook contains some other sheets that create graphs based off the information sourced from the exported sheets.

Oddly enough, I seem to be able to "fix it" by renaming the offending sheets and having them (recreated) from my export code (just simple DoCmd.transferspreadsheet) and then fixing the inter workbook relations (back) to the old name, but new sheets.
A part of the problem seems to be that when exporting the sheet, the data is removed from the existing one for the amount of data that you are trying to add. Which in this case is LESS than what was there. The "surplus" data that was there, remains and might be causing the problem....

Once the issue has been circumvented by one of the two above solutions, I seem to no longer encounter the issue. :banghead:

Google returns hits from different fora (Mr Excel, Utteraccess and more ...) and different versions like 2003 and 2007. Most common solution seems to be "delete the file first, then export it"
Which I cannot do, due to the graphs and reports running inside the workbook...
fixing it like per above seems to have eleviated the issue for me (for now).

What kind of stupid issue is this?? Really this shouldnt be happening, totaly discrediting my databases and reports all over the place !!! :banghead:
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:47
Joined
Aug 11, 2003
Messages
11,680
Pft, seems I spoke too soon :/

Now that we crossed the weekend and another week is archived (thus the list shortened), now my error is back again... This is getting emberasing FAST, anyone have any experience with this error and how to "fix" this bug that wont replace data in a worksheet like it used to work back in office 2010/2003/97

Just a simple
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCrossAmount", "X:\Reports\Finance\StornoReport.xlsx", True
Which doesnt have a real range defined "perse" but throws this error :(
 

Rx_

Nothing In Moderation
Local time
Yesterday, 21:47
Joined
Oct 22, 2009
Messages
2,803
Sorry, not using Office 365 to help.
As somone who does all reports using Excel Object Model Programming, this is very interesting. Please be encouraged to proved more details.
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:47
Joined
Aug 11, 2003
Messages
11,680
One thing I have learned in the mean time, is that this version (perhaps earlier version too, but I have never noticed) see to export into a named range rather than into a Worksheet.

The problem seems to happen when the new recordset is deviating from the existing named range. This can be either more or less columns or more or less rows... I have a hard time researching this since once I have it working, I can do whatever I want to the Named range and the export works just fine. Add columns, remove columns, add data, remove data, no matter what I do I cant seem to "on demand" re-inact the problem.

I am really stumped at this, one other thing I have noticed is that sometimes when the error is thrown some rows at the bottom of the new range seem to remain... I.e. when the export had 100 rows and now has 95 rows, the last 5 rows remain... and the other 95 are erased before the error is thrown...

FRUSTRATING to the max :(

Perhaps I should try looking into "fixing" this by dumping the transferspreadsheet and actually opening the excel file in code then writing the data to the sheet directly... It is doing things the hard way, but theoreticaly shouldnt run into this annoying problem.

Plus I cant exclude that this may be a result of crossing 365 with Office 2010, since both are currently installed on our desktops.... So perhaps something between them is causing a problem :/ AND YES definatly never seen the problem using 2010 for years so it definatly isnt a 2010 issue, perhaps the mix of 365 and 2010 or 365 only, I dont know....

I hope I am not ranting too much, but its doing my head in :(
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:47
Joined
Aug 11, 2003
Messages
11,680
Now I havent seen the error for a few days, today I get a new one "Too many fields defined"

Again all over the place the most suggested solution is "Just delete the file" which is nonsence.

I am exporting a crosstab that can have anywhere from 5 to 15 columns, guess that the 12th column being exported today is causing an issue vs the 11 it had previously. *SIGH*
What has M$ done to my beloved access/excel combination to cause these kind of idiotic problems???

All or most of my problems seem to do with reports that vary in the number of columns that are being exported, either crosstab queries or dynamic queries exported to a single excel file that isnt getting deleted at any point.

And now, whatever I may try, this stupid thing isnt updating my sheets anymore. *GRMBL*
I will try a reboot later on see if that helps the situation anything, failing that guess I have to resort to dumping the TransferSpreadsheet and actually opening excel :(

Edit: The report seems to work just fine when exporting to a new Workbook.
Just deleting the sheets inside the existing workbook or altering the Named ranges doesnt seem to help anything :(.... *GRMBL*
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 05:47
Joined
Aug 11, 2003
Messages
11,680
FFS my stupid "cannot expand" error message is back again :(
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:47
Joined
Aug 11, 2003
Messages
11,680
I have resorted to opening the excel file and writing the recordset to a sheet "manually" using this code below. Part of which I nicked from the interwebs "someplace" and adjusted it a little bit to
- add sheets if needed
- add headers to the sheet

It doesnt yet check if the file actually exists or not, it simply assumes the file is there. May need to add some code to ensure this happens "properly", someday :p

For reference, it is called simply like so:
SaveQueriesToExcel "H:\Reports\SomeReport.xlsx", "qryReport1,qryReport2,qryReport3,qryReport4"


Code:
'  This module requires references to the
'  following object libraries:
'
'  1. Microsoft Excel X.X Object Library,
'    where X.X is the Excel Version Number.
'
'  2. One of the following:
'
'    For mdb files:
'      Microsoft DAO 3.6 Object Library
'      (DAO360.DLL)
'    For ACCDB files (Access 2007):
'      Microsoft Office 12 Access Database Engine Objects
'      (ACEDAO.DLL)
'      This reference should be set already.
'
'  To set the reference, in the VBA editor:
'    Tools > References.

Sub SaveQueriesToExcel(ExcelFile As String, Queries As String)
    ' ExcelFile is the full file including path and extension
    ' Queries, a comma seperated list of queries to export to the excel file.
    
    ' The queries are exported to the excel sheets, called exactly the same as the query name
    ' If the worksheet doesnt yet exist it is created.
    
    '  Excel constants:
    Const strCellAddress As String = "A1"
    
    Dim strWorksheetName As String
    '  Access variables:
    Dim myQRYs() As String
    Dim i As Integer
    
    '  Excel Objects:
    Dim objXL As Excel.Application
    Dim objWBK As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objX As Excel.Worksheet
    Dim objRNG As Excel.Range
    
    '  DAO objects:
    Dim objRS As DAO.Recordset
    Dim iCols As Integer
    
    
    On Error GoTo Error_Exit_SaveQueriesToExcel
    
    '  Open a DAO recordset on the query:
    myQRYs = Split(Queries, ",")
    
    '  Open Excel and point to the cell where
    Set objXL = New Excel.Application
    objXL.Visible = True
    Set objWBK = objXL.Workbooks.Open(ExcelFile)
    
    For i = LBound(myQRYs) To UBound(myQRYs)
    
        ' Open the query
        Set objRS = CurrentDb.OpenRecordset(myQRYs(i))
        ' go to the worksheeet
        For Each objX In objWBK.Worksheets
            If objX.Name = myQRYs(i) Then
                objWBK.Worksheets(myQRYs(i)).Select
                Exit For
            End If
        Next objX
        ' Create the worksheet if not selected
        If objWBK.ActiveSheet.Name <> myQRYs(i) Then
            objWBK.Worksheets().Add.Name = myQRYs(i)
        End If
        
        Set objWS = objWBK.Worksheets(myQRYs(i))
        Set objRNG = objWS.Range(strCellAddress)
        objWS.Activate ' can be commented out
        objRNG.Select  ' Can be commented out
        objWS.Cells.ClearContents
        ' Write the headers
        For iCols = 0 To objRS.Fields.Count - 1
            objWS.Cells(1, iCols + 1).Value = objRS.Fields(iCols).Name
        Next
        objWS.Cells(2, 1).CopyFromRecordset objRS
        ' close and cleanup the recordset
        objRS.Close
        Set objRS = Nothing
        
    
    Next i
    objWBK.Save
    objWBK.Close
    objXL.Quit
    '  Destroy objects:
    GoSub CleanUp
    
Exit_SaveQueriesToExcel:
    
    Exit Sub
    
CleanUp:
    
    '  Destroy Excel objects:
    Set objRNG = Nothing
    Set objWS = Nothing
    Set objWBK = Nothing
    Set objXL = Nothing
    
    '  Destroy DAO objects:
    If Not objRS Is Nothing Then
        objRS.Close
        Set objRS = Nothing
    End If
    
    Return
    
Error_Exit_SaveQueriesToExcel:
    
    MsgBox "Error " & Err.Number & vbNewLine & vbNewLine _
                    & Err.Description, _
                    vbExclamation + vbOKOnly, _
                    "Error Information"
    
    GoSub CleanUp
    Resume Exit_SaveQueriesToExcel
    
End Sub
 
Last edited:

sspreyer

Registered User.
Local time
Yesterday, 20:47
Joined
Nov 18, 2013
Messages
251
Thanks

Guys I will post back how I got on

Thanks again

Shane
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:47
Joined
Aug 11, 2003
Messages
11,680
Untested but wanted for another thread... http://www.access-programmers.co.uk/forums/showthread.php?p=1394404#post1394404

This you should be able to call with
SaveQueriesToExcel "H:\Reports\SomeReport.xlsx", "qryReport1,qryReport2,qryReport3,qryReport4", ""
if you want the sheets to have the same name as the queries
If you want specific sheet names other than the query name
SaveQueriesToExcel "H:\Reports\SomeReport.xlsx", "qryReport1,qryReport2,qryReport3,qryReport4", "Master,MasterData,Report,ReportData"

Note that this does not support partial sheet naming, if you want to name 1 out of 4 sheets to a different name all 4 must be entered in the sheets parameter.

For one query/sheet
SaveQueriesToExcel "H:\Reports\SomeReport.xlsx", "qryReport", "SomeSheet"

Code:
'  This module requires references to the
'  following object libraries:
'
'  1. Microsoft Excel X.X Object Library,
'    where X.X is the Excel Version Number.
'
'  2. One of the following:
'
'    For mdb files:
'      Microsoft DAO 3.6 Object Library
'      (DAO360.DLL)
'    For ACCDB files (Access 2007):
'      Microsoft Office 12 Access Database Engine Objects
'      (ACEDAO.DLL)
'      This reference should be set already.
'
'  To set the reference, in the VBA editor:
'    Tools > References.

Sub SaveQueriesToExcel(ExcelFile As String, Queries As String, Sheets as string)
    ' ExcelFile is the full file including path and extension
    ' Queries, a comma seperated list of queries to export to the excel file.
    
    ' The queries are exported to the excel sheets, called exactly the same as the query name
    ' If the worksheet doesnt yet exist it is created.
    
    '  Excel constants:
    Const strCellAddress As String = "A1"
    
    Dim strWorksheetName As String
    '  Access variables:
    Dim myQRYs() As String
    Dim mySheets() as string
    Dim i As Integer
    
    '  Excel Objects:
    Dim objXL As Excel.Application
    Dim objWBK As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objX As Excel.Worksheet
    Dim objRNG As Excel.Range
    
    '  DAO objects:
    Dim objRS As DAO.Recordset
    Dim iCols As Integer
    
    
    On Error GoTo Error_Exit_SaveQueriesToExcel
    
    '  Open a DAO recordset on the query:
    myQRYs = Split(Queries, ",")
    if sheets = "" then sheets = queries
    mySheets = Split(Sheets, ",")
    
    '  Open Excel and point to the cell where
    Set objXL = New Excel.Application
    objXL.Visible = True
    Set objWBK = objXL.Workbooks.Open(ExcelFile)
    
    For i = LBound(myQRYs) To UBound(myQRYs)
    
        ' Open the query
        Set objRS = CurrentDb.OpenRecordset(myQRYs(i))
        ' go to the worksheeet
        For Each objX In objWBK.Worksheets
            If objX.Name = mySheets(i) Then
                objWBK.Worksheets(mySheets(i)).Select
                Exit For
            End If
        Next objX
        ' Create the worksheet if not selected
        If objWBK.ActiveSheet.Name <> mySheets(i) Then
            objWBK.Worksheets().Add.Name = mySheets(i)
        End If
        
        Set objWS = objWBK.Worksheets(mySheets(i))
        Set objRNG = objWS.Range(strCellAddress)
        objWS.Activate ' can be commented out
        objRNG.Select  ' Can be commented out
        objWS.Cells.ClearContents
        ' Write the headers
        For iCols = 0 To objRS.Fields.Count - 1
            objWS.Cells(1, iCols + 1).Value = objRS.Fields(iCols).Name
        Next
        objWS.Cells(2, 1).CopyFromRecordset objRS
        ' close and cleanup the recordset
        objRS.Close
        Set objRS = Nothing
        
    
    Next i
    objWBK.Save
    objWBK.Close
    objXL.Quit
    '  Destroy objects:
    GoSub CleanUp
    
Exit_SaveQueriesToExcel:
    
    Exit Sub
    
CleanUp:
    
    '  Destroy Excel objects:
    Set objRNG = Nothing
    Set objWS = Nothing
    Set objWBK = Nothing
    Set objXL = Nothing
    
    '  Destroy DAO objects:
    If Not objRS Is Nothing Then
        objRS.Close
        Set objRS = Nothing
    End If
    
    Return
    
Error_Exit_SaveQueriesToExcel:
    
    MsgBox "Error " & Err.Number & vbNewLine & vbNewLine _
                    & Err.Description, _
                    vbExclamation + vbOKOnly, _
                    "Error Information"
    
    GoSub CleanUp
    Resume Exit_SaveQueriesToExcel
    
End Sub
 

Users who are viewing this thread

Top Bottom