Running Excel using VBA from another program

Question

I am using vba in a CAD program to export data, sort the data, and add data. The following macro is exactly what I want excel to do. However I believe I am limited to having the CAD program tell Excel what to do through VBA. This macro copyies a formula and pastes it to all the populated cells below it in the column.

MACRO CODE:

Range("B1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

iLogic Version of Code:

oBook.WorkSheets(1).Name = "Order List"
oBook.WorkSheets(2).Name = "Cut List"
wSheet1 = oBook.WorkSheets("Order List")
wSheet2 = oBook.WorkSheets("Cut List")

wSheet2.Activate
wSheet2.Range("B1").Select
wSheet2.Selection.Copy
wSheet2.Range(Selection, Selection.End(xlDown)).Select
wSheet2.Selection.Paste

Unfortunately I seem to be missing something to translate between Inventor and Excel, but I don't know enough to even know if that's the issue. Any advice is very much appreciated as I am still very new to VBA.

See this post. You want to avoid Select and Activate. Also Selection probably means nothing to Inventor, and if it does then it doesn't mean the same thing as Selection does when hosted in Excel. You'll want to make that wSheet2.Application.Selection, or if you have a reference to Excel.Application (you should), then use that object variable to qualify everything that would be global in Excel. — Mathieu Guindon
– Mathieu Guindon, Commented Nov 8, 2019 at 19:27

DisGruntledDraftsman · Accepted Answer · 2019-11-09 19:08:33Z

0

Ok. I had some code that I had copied off of a forum but didn't understand the functions going on. I believe this is what you are referring to "Application"?

wSheet2.Columns("G:G").select()
oExcelApp.Selection.cut()
wSheet2.Columns("B:B").Select()
oExcelApp.Selection.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)

Although I don't understand what the "(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)" refers to. I understand the part about the direction and where it inserts just not whats before it.

edited Nov 9, 2019 at 19:08

answered Nov 9, 2019 at 18:43

DisGruntledDraftsman

11 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Blake Daniel · Accepted Answer · 2019-11-08 19:58:12Z

-1

I do agree with @DisGruntledDraftsman about not using Select and Activate. However, if you are just trying to get some working code that gets the job done, some simple tweaks can be done. Of course this isn't ideal but, it should work though.

row_Count = wSheet2.Range("B1048576").End(xlUp).Row
wSheet2.Activate
wSheet2.Range("B1").Select
Selection.AutoFill Destination:=wSheet2.Range("B1:B" & row_Count), Type:=xlFillDefault

answered Nov 8, 2019 at 19:58

Blake Daniel

1007 bronze badges

1 Comment

Mathieu Guindon Over a year ago

wSheet2.Range("B1").AutoFill ... would "get the job done" just as well, without Select and Activate. There's hardly ever any reason whatsoever to work off the Selection.. which as my comment said above, means nothing to OP's host application. If a Selection exists in OP's host app, it does not refer to Excel's Selection. This code will not work.

Collectives™ on Stack Overflow

Running Excel using VBA from another program

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related