TheSwamp
Code Red => VB(A) => Topic started by: Vince 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...!
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
-
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?
-
which line is throwing the error?
since you're experiencing issues while counting excel sheet rows they might arise in GetRowCnt
sub, which is likely to count the sheet rows and assign it to 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 iNumRws
is being declared
so you might want to simply declare
Dim iNumRws As long
-
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
-
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.
-
OR the Excel 2016 files are .xlsx and not .xls
-
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
-
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")
...
...
-
what is that
iNumRws = GetRowCnt
doing?
it seems to count some excel sheet rows number but it's placed before the '''''''......Gets Excel Sheet
code section