I have added code to copy the cell value. I think I am lost when taking the cell value into the USERS1 variable.
Again, I hope I am heading the right direction on this. lol.
Option Explicit
Public Sub PasteCurrentCell()
Dim sh As Excel.Worksheet
Dim rng As Excel.Range
Set sh = GetExcelSheet()
If sh Is Nothing Then
MsgBox "Excel is not running, or" & vbCrLf & _
"opened Excel file does not have ""SHEET1""."
Exit Sub
End If
Set rng = sh.Range("A1") '<<<<<---- How can I make this select the current active cell?? it could be in any column or row.
rng.Copy
InsertCurrentCellValue
End Sub
Private Function GetExcelSheet() As Excel.Worksheet
Dim theSheet As Excel.Worksheet
Dim sh As Excel.Worksheet
Dim xls As Excel.Application
On Error Resume Next
Set xls = GetObject(, "Excel.Application")
If Not xls Is Nothing Then
For Each sh In xls.ActiveWorkbook.Worksheets
If UCase(sh.Name) = "SHEET1" Then
Set xls.ActiveSheet = sh
Set theSheet = sh
Exit For
End If
Next
End If
Set GetExcelSheet = theSheet
End Function
Private Sub InsertCurrentCellValue()
On Error Resume Next
Set AcadApp = GetObject(, "AutoCAD.Application")
If Err Then
Err.Clear
Set AcadApp = CreateObject("AutoCAD.Application")
End If
AppActivate AcadApp.Caption
AcadApp.Visible = True
AcadApp.Application.WindowState = acNorm
AcadApp.ActiveSpace = acModelSpace
If AcadApp.Documents.Count = 0 Then
AcadApp.Documents.Add
End If
ThisDrawing.SetVariable "USERS1", strcname
AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
End Sub