locked
'cannot expand named range' error when exporting to excel from vs 2012 RRS feed

  • Question

  • I have a process in a vs 2012 project that exports data to a spreadsheet. First it drops a sheet (i.e. table) in the spreadsheet, creates the sheet, and then inserts data into it. The first time the process is run it works fine. But if I try it again I get an error that says 'cannot expand named range'. We recently converted to Office 2012 so I am assuming it is some type of issue related to that but have no clue what it could be. I think something is not working correctly when it drops the  table because if I go in and manually clear the data out of htat sheet and then re-run it is fine. Here is my code:

            Dim SQL_DeleteSheet = "DROP TABLE [FallsDataRaw]"
            
    
            Dim ExcelCommand_DeleteSheet As New System.Data.OleDb.OleDbCommand(SQL_DeleteSheet, ExcelConnection)
            ExcelCommand_DeleteSheet.ExecuteNonQuery()
    
     
    
    
            '   Create FallsDataRaw sheet 
            Dim SQL_CreateSheet = "CREATE TABLE [FallsDataRaw]([Unit] varchar(3), [RoomBed] varchar(11), [Name] varchar(34), [CareType] char(3), [AdmitDate] char(10), [AssessFall] char(1), " & _
                                      "[PostFallAsmntComp] char(1), [DateofFall] char(10), [FallWithin30Days] char(1), [Dayofweek] char(1), [ShiftCode] char(1), " & _
                                      "[FallTime] char(8), [TimeCode] char(2), [LocationCode] char(2), [ActPriortoFall] char(1), [Injury] char(1), [InjuryType] char(1)," & _
                                      "[TXLocation] char(2), [InjuryDetail] varchar(100), [HistoryofFalls] char(1), [AcuteMedProb]  char(1), [ChronicMedProb] char(1), " & _
                                      "[Medications] char(1), [FunctionalStat] char(1), [SensoryStat] char(1), [PsycStat] char(1), [EnvironmentIssues] char(1), [Restraint] char(1), [Comments] varchar(200))"
    
            Dim ExcelCommand_CreateSheet As New System.Data.OleDb.OleDbCommand(SQL_CreateSheet, ExcelConnection)
            ExcelCommand_CreateSheet.ExecuteNonQuery()
    
     
    
            '   Insert data into FallsDataRaw sheet 
    
            Dim SQL_Insert = "INSERT INTO [FallsDataRaw] (Unit, RoomBed, Name, CareType, AdmitDate, AssessFall, PostFallAsmntComp, DateofFall, " & _
                       "FallWithin30Days, Dayofweek, ShiftCode, FallTime, TimeCode, LocationCode, ActPriortoFall, Injury, InjuryType, " & _
                       "TXLocation, InjuryDetail, HistoryofFalls, AcuteMedProb, ChronicMedProb, Medications, FunctionalStat, SensoryStat," & _
                       "PsycStat, EnvironmentIssues, Restraint, Comments) SELECT Unit, RoomBed, Name, CareType," & _
                       "Format(AdmitDate, 'mm/dd/yyyy') as AdmitDate, AssessFall, PostFallAsmntComp, Format(DateofFall, 'mm/dd/yyyy') as DateofFall," & _
                       "FallWithin30Days, Dayofweek, ShiftCode, FallTime, TimeCode, LocationCode, ActPriortoFall, Injury, InjuryType," & _
                       "InjuryOutcome as TXLocation, InjuryDetail, HistoryofFalls, AcuteMedProb, ChronicMedProb, Medications, FunctionalStat," & _
                       "SensoryStat, PsycStat, EnvironmentIssues, Restraint, Comments FROM [QryDataforSpreadsheet]" & _
                       "IN '' [ODBC;Driver={SQL Server};Server=server1;Database=FallsData;Trusted_Connection=yes]" & _
                       "WHERE (FacN ='" & FacNum & "') and (DateofFallMonth = " & ParMonth & ") and (DateofFallYear = " & ParYear & ") and (FallTime > '""');"
    
    
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQL_Insert, ExcelConnection)
            ExcelCommand.Parameters.AddWithValue("Param1", ParFacNum)
            ExcelCommand.Parameters.AddWithValue("Param2", ParMonth)
            ExcelCommand.Parameters.AddWithValue("Param3", ParYear)
            ExcelCommand.ExecuteNonQuery()

    Friday, July 19, 2013 4:18 PM

Answers

  • Found an alternative way to delete the sheet. Code is below for future reference.

    Imports Excel = Microsoft.Office.Interop.Excel
    
            Dim excel As Microsoft.Office.Interop.Excel.Application
            Dim wb As Microsoft.Office.Interop.Excel.Workbook
            Dim ws As Microsoft.Office.Interop.Excel.Worksheet
    
            Try
                excel = New Microsoft.Office.Interop.Excel.Application
                excel.DisplayAlerts = False
                wb = excel.Workbooks.Open(strFileAll)
                For Each ws In wb.Sheets
                    If ws.Name = "Sheet_to_delete" Then
                        ws = wb.Sheets("Sheet_to_delete")
                        ws.Delete()
                    End If
                Next
                wb.Save() 'Save the excel
            Catch ex As Exception
                MsgBox("Error in exporting data.")
                Me.Cursor = Cursors.Default
                wb.Close() 'Close the excel
                Exit Sub
            Finally
                wb.Close() 'Close the excel
            End Try
    • Marked as answer by J-Bal Monday, July 29, 2013 7:10 PM
    Monday, July 29, 2013 7:10 PM

All replies

  • Forgot to include my connection string:

    Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.15.0;" & _
            "Data Source=" & strFileAll & ";" & _
            "Extended Properties=""Excel 12.0 Macro;HDR=YES""")

    Friday, July 19, 2013 4:24 PM
  • Hello,

    Are you sure the drop table works since prior versions of ACE used thru OleDb did not support this. So the string below work execute but not do anything.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=File1.xlsx;Extended Properties="Excel 12.0;IMEX=0;HDR=No;"
    I wonder if the drop and create messed something up with the underlying XML structure.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, July 19, 2013 6:01 PM
  • Well, this has been working fine for over 2 years until now. So it appears it was working fine.
    Tuesday, July 23, 2013 8:26 PM
  • I'd ask in the Excel for Developers forum.

    Karl


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer



    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})

    Wednesday, July 24, 2013 2:17 PM
  • Found an alternative way to delete the sheet. Code is below for future reference.

    Imports Excel = Microsoft.Office.Interop.Excel
    
            Dim excel As Microsoft.Office.Interop.Excel.Application
            Dim wb As Microsoft.Office.Interop.Excel.Workbook
            Dim ws As Microsoft.Office.Interop.Excel.Worksheet
    
            Try
                excel = New Microsoft.Office.Interop.Excel.Application
                excel.DisplayAlerts = False
                wb = excel.Workbooks.Open(strFileAll)
                For Each ws In wb.Sheets
                    If ws.Name = "Sheet_to_delete" Then
                        ws = wb.Sheets("Sheet_to_delete")
                        ws.Delete()
                    End If
                Next
                wb.Save() 'Save the excel
            Catch ex As Exception
                MsgBox("Error in exporting data.")
                Me.Cursor = Cursors.Default
                wb.Close() 'Close the excel
                Exit Sub
            Finally
                wb.Close() 'Close the excel
            End Try
    • Marked as answer by J-Bal Monday, July 29, 2013 7:10 PM
    Monday, July 29, 2013 7:10 PM
  • Hello,

    Good to hear you found a solution. Did not realize you were open to automation, otherwise I would have presented the following

    http://www.siddharthrout.com/2012/09/10/addingdeleting-sheets-to-the-excel-file/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Monday, July 29, 2013 7:51 PM