I have the following VBA code. It's called by a macro from Inventor. I don't know exactly why but when "Microsoft Excel 16.0 Object Library" is active everything works perfectly. However, when "Microsoft Excel 14.0 Object Library" is active didn't copy "rngNumber". Could it be Microsoft excel object related to the problem?
Private Sub TEST()
Dim excelApp As Excel.Application
' Try to connect to a running instance of Excel.
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
' You can make it visible if you want. This is especially
' helpful when debugging.
excelApp.Visible = True
Dim wb as Workbook
Set wb = Application.ActiveWorkbook
Set ws = wb.Worksheets(1)
ws.Activate
'Set Worksheet
Dim wsData As WorkSheet
Set wsData = wb.Worksheets(2)
'Write column titles
With wsData
.Cells(1, "A").Value = "Number"
End With
'Get column letter for each column whose first row starts with an specific string
ws.Activate
Dim sNumber as String
sNumber= Find_Column("Number")
'Define variables
Dim rngNumber As Range
' Copy and paste data from "Number" column to Column "A" in Worksheets "Data"
ws.Activate
'Find which is the last row with data in "Number" column and set range
With ws.Columns(sNumber)
Set rngNumber = Range(.Cells(2), .Cells(.Rows.Count).End(xlUp))
End With
'Copy and paste data from "Number" column
rngNumber.Copy wsData.Range("A2")
End Sub
Private Function Find_Column(Name As String) As String
Dim rngName As Range
Dim Column As String
With ws.Rows(1)
On Error Resume Next
Set rngName = .Find(Name, .Cells(.Cells.Count), xlValues, xlWhole)
' Calculate Name Column Letter.
Find_Column = Split(rngName.Address, "$")(1)
End With
End Function
