Author Topic: VBA Code Has Error Defining Excel File  (Read 6843 times)

0 Members and 1 Guest are viewing this topic.

Vince

  • Newt
  • Posts: 55
VBA Code Has Error Defining Excel File
« on: September 26, 2017, 02:26:56 PM »
Hi Swamp Members,

We have VBA code that allows the user to select an Excel to process and then reads the Excel file and Draws a graphic Column Schedule in AutoCAD.  Now when we select the Excel file the code crashes and gives one of the two error messages attached below...!!

This version is using AutoCAD 2013......and Excel 2016 running on Windows 7, 64bit workstations.

Here is the area of the code where the Excel file is selected...!

Code: [Select]

Private Sub cmdDir_Click()
    Dim sProject As String
    Dim sProject1 As String
    Dim sProject2 As String
    Dim sProject3 As String
    Dim sProject4 As String
    Dim sProj As String
   
    sProject = Trim(Me.txtJobNum)
   
    sProj = Len(sProject)
    sProject1 = Left(sProject, 4)
    sProject2 = Left(sProject, 5)
    sProject3 = Left(sProject, 5)
    sProject4 = Mid(sProject, 2, 6)
   
''''''......Check For Old 4 Character number
    If sProj = 4 Then
             sDfltDir = "f:\projects\" & sProject & "\Excel\"
    End If
   
'''''''......Check For Old 5 Character Pnumber
    If sProj = 5 Then
             sDfltDir = "f:\projects\" & sProject & "\Excel\"
    End If
   
'''''''......Check For New 8 Character Pnumber
    If sProj = 8 Then
             sDfltDir = "f:\projects\" & sProject3 & "\" & sProject & "\Excel\"
    End If
   
'''''''......Check For New 9 Character Pnumber
    If sProj = 9 Then
             sDfltDir = "f:\projects\" & sProject4 & "\" & sProject & "\Excel\"
    End If
    Me.CommonDialog1.InitDir = sDfltDir
    Me.CommonDialog1.DefaultExt = ".xls"
    Me.CommonDialog1.DialogTitle = "Select the SpreadSheet"
    Me.CommonDialog1.CancelError = True
    Me.CommonDialog1.Filter = "All Files (*.xls)"

    Me.CommonDialog1.ShowOpen
    Me.lblPath = Mid(Me.CommonDialog1.FileName, 1, Len(Me.CommonDialog1.FileName) - 4)
   
    If Me.lblPath > "" Then
        cmdGetFile.Visible = True
    Else
        cmdGetFile.Visible = False
    End If
   
End Sub


Private Sub cmdGetFile_Click()

Dim MouseChange
Dim strJobNum
Dim strFile
Dim strColTag1
Dim strColTag2 As String
Dim xlWorkBook
Dim xlSheet As Object
Dim intRow
Dim intCol
Dim iFoo As Integer
Dim iNumRws As Integer
Dim intTempNumOfLevels
Dim intNumOfLevels As Integer
Dim intNumOfCols As Integer
Dim TestVar
Dim arrData()

'''''''......Changes the pointer to an hourglass

    frmGetFile.MousePointer = fmMousePointerHourGlass
    MouseChange = DoEvents

    iNumRws = GetRowCnt
    ReDim arrData(iNumRws, 8)  'Set the array to the actual size of the Spread Sheet
   
'''''''......Gets Excel Sheet

    Set xlWorkBook = GetObject(lblPath & ".xls")

    Set xlSheet = xlWorkBook.worksheets("Combined")
   
'''''''......Start at Row 2
    intRow = 1
    intNumOfLevels = 0
    intTempNumOfLevels = 1
   
'''''''......Read Spreadsheet into the array
    Do
        intRow = intRow + 1
       
        If intRow > iNumRws Then
            Exit Do
        End If
       
        For intCol = 1 To 8
            arrData(intRow, intCol) = xlSheet.Cells(intRow, intCol).Value
            If intCol = 7 Or intCol = 8 Then 'If cell is in col 7 or 8 get the integer value not the decimal value
                arrData(intRow, intCol) = Int(arrData(intRow, intCol))
            End If
            Debug.Print arrData(intRow, intCol)
        Next intCol
       

'''''''......Determine max number of levels

        strColTag1 = xlSheet.Cells(intRow, 1).Value
       
        If strColTag1 = "" Then
            strColTag1 = xlSheet.Cells(intRow, 2).Value
        Else
            strColTag1 = strColTag1 & "-" & xlSheet.Cells(intRow, 2).Value
        End If
       
        TestVar = xlSheet.Cells(intRow + 1, 2).Value
        strColTag2 = xlSheet.Cells(intRow - 1, 1).Value
       
        If strColTag2 = "" Then
            strColTag2 = xlSheet.Cells(intRow - 1, 2).Value
        Else
            strColTag2 = strColTag2 & "-" & xlSheet.Cells(intRow - 1, 2).Value
        End If
       
        If (strColTag1 = strColTag2) Then
            intTempNumOfLevels = intTempNumOfLevels + 1
        Else
            intTempNumOfLevels = 1
        End If
        If intTempNumOfLevels > intNumOfLevels Then
            If (xlSheet.Cells(intRow + 2, 1).Value <> "") Then
                intNumOfLevels = intTempNumOfLevels
            End If
        End If
         
    Loop

    Set xlWorkBook = Nothing
    Set xlSheet = Nothing
   
    intNumOfCols = intRow – 2



It would be appreciated if anyone could take a look and see if their expertise can identify the problem or make recommendations on how to resolve the difficulty.


Thank you for your cooperation,
Vince

« Last Edit: September 26, 2017, 02:33:14 PM by Vince »

57gmc

  • Bull Frog
  • Posts: 358
Re: VBA Code Has Error Defining Excel File
« Reply #1 on: September 26, 2017, 05:46:35 PM »
Now when we select the Excel file the code crashes and gives one of the two error messages attached below...!!
Was it working before? Under what conditions? Did you recently upgrade Office?

RICVBA

  • Newt
  • Posts: 62
Re: VBA Code Has Error Defining Excel File
« Reply #2 on: September 26, 2017, 06:26:59 PM »
which line is throwing the error?

since you're experiencing issues while counting excel sheet rows they might arise in
Code: [Select]
GetRowCnt sub, which is likely to count the sheet rows and assign it to
Code: [Select]
iNumRws
and since you're using Excel 2016 you might have more than 32767 rows or so which is the limit for an integer variable like
Code: [Select]
iNumRws is being declared
so you might want to simply declare

Code: [Select]
Dim iNumRws As long

Vince

  • Newt
  • Posts: 55
Re: VBA Code Has Error Defining Excel File
« Reply #3 on: September 29, 2017, 11:02:50 AM »
Yes it was working fine using Excel 2013......then the office migrated to Microsoft Office 2016 / Excel 2016 and some of the engineers started to have the problem.  I stalled as long as I could but eventually had to install Excel 2016.  In the Microsoft Visual Basic editor I added the Microsoft Excel 16.0 Object Library to the references but now I am getting the error messages attached below.

Please let me know if you have any ideas or suggestions on how to resolve the difficulties..??


Thank You in Advance,
Vince

57gmc

  • Bull Frog
  • Posts: 358
Re: VBA Code Has Error Defining Excel File
« Reply #4 on: September 29, 2017, 11:08:03 AM »
I believe the problem is that AutoCAD 2013 is still using 32 bit VBA and Excel 2016 is using 64 bit VBA. You would need to upgrade AutoCAD.

Greg B

  • Seagull
  • Posts: 12417
  • Tell me a Joke!
Re: VBA Code Has Error Defining Excel File
« Reply #5 on: September 29, 2017, 11:32:24 AM »
OR the Excel 2016 files are .xlsx and not .xls

Vince

  • Newt
  • Posts: 55
Re: VBA Code Has Error Defining Excel File
« Reply #6 on: September 29, 2017, 12:57:53 PM »
I think 80% of the Excel 2016 VBA code is the same as the Excel 2013 VBA code...!  and we are running Excel 2016 in a 32 bit shell.  We intend to upgrade in about 2 months however, I need to get the AutoCAD 2013 VBA code to work until we upgrade.  How can I accomplish that....??   Here is the code sample of how I get the Excel work book object......    Set xlWorkBook = GetObject(lblPath & ".xls")    Would this be coded differently in VBA Excel 2016....??


Thank you for your assistance..!
Vince

n.yuan

  • Bull Frog
  • Posts: 348
Re: VBA Code Has Error Defining Excel File
« Reply #7 on: September 29, 2017, 03:43:54 PM »
I do not have MS Office2016 installed, so, just guess.

Since your AutoCAD VBA code opens Excel app via COM out-process automation, it odes not matter AutoCAD 2013VBA and Excel being 32-bit or 64-bit, as one of the reply suggested.

From my eyes, I think this line is problematic (as you have already indicated):

Set xlWorkBook = GetObject(lblPath & ".xls")

because when you pass a file name to GetObject() method, it will check the OS (Windows) to determine which application is associated to the file (according to the file name's extension). Often a file type can be associated to multiple apps. When Windows determines an app, it then tries to open it. As latest Excel app associated to many different file types (*.xlsx, *.xls, *.csv, ...), it can go wrong for some reason.

You should do things in more certain way, like this:

Dim excel As Excel.Application
Dim wk As Excel.Workbook
Dim sh As Excel.Worksheet

''Get Excel application first
On Error ResumeNext
Set excel=GetObject(, "Excel.Application")
If excel Is Nothing Then
  Set excel=CreateObject("Excel.Application")
End If

If excel Is Nothing Then
  MsgBox "Cannot connect to Excel Application!"
  Exit Sub
End If

'' Now that you have Excel running, open the workbook/sheet explicitly
Set wk=excel.Workbooks.Open(lblPath & ".xls")
Set sh=wk.Sheets("xxxx")
...
...

RICVBA

  • Newt
  • Posts: 62
Re: VBA Code Has Error Defining Excel File
« Reply #8 on: September 30, 2017, 01:28:23 AM »
what is that
Code: [Select]
iNumRws = GetRowCntdoing?

it seems to count some excel sheet rows number but it's placed before the
Code: [Select]
'''''''......Gets Excel Sheet code section