hello,
i’m asking if there is a possibility to merge two tables in excel together, so i can just put the topic on the top and two columns but the rest can be inserted very easily,
i would be thankful for any help
hello,
i’m asking if there is a possibility to merge two tables in excel together, so i can just put the topic on the top and two columns but the rest can be inserted very easily,
i would be thankful for any help
Is this using MBS Xojo Plugin?
https://www.monkeybreadsoftware.de/xojo/plugin-xls.shtml
Or via scripting Excel?
Hello Dante,
One solution is to use the Microsoft Office plugin that comes with Xojo. Copy MSOfficeAutomation.rbx from C:\Program Files (x86)\Xojo\Xojo 2017r1.1\Extras\Office Automation\ and paste it in the folder: C:\Program Files (x86)\Xojo\Xojo 2017r1.1\Plugins\
Start Xojo and then add the following code to a pushbutton action event:
[code]Dim excel as new ExcelApplication
excel.Workbooks.add //Make Sheet1
excel.Visible = true
Dim XString as String
Dim i as Integer
//Add data to sheet 1 column A
For i = 1 to 9
excel.Worksheets(“Sheet1”).Cells(i,1).Value = CStr(i*2)
Next i
MsgBox “Sheet 1 is populated with data”
excel.Worksheets.add //Make sheet 2
//Add data to sheet 2 column A
For i = 1 to 9
excel.Worksheets(“Sheet2”).Cells(i,2).Value = CStr(i*3)
Next i
MsgBox “Sheet 2 is populated with data”
//Select and copy entire row on Sheet 1
excel.Worksheets(“Sheet1”).Select_
excel.Columns(“A:A”).Select_
excel.Selection.Copy
//Paste Row A into sheet 2
excel.Worksheets(“Sheet2”).Select_
excel.Columns(“A:A”).Select_
excel.ActiveSheet.Paste
Exception err as OLEException
MsgBox err.message[/code]
This example copies Column A from Sheet 1 and pastes the data into Column A on Sheet 2.
Does this work for you?
Edit: Copies column A from sheet 1 to column a on sheet 2
My mistake, “SVERWEIS” means “VLOOKUP”. Here is an example for VLOOKUP.
[code]Sub Action() Handles Action
Dim excel as new ExcelApplication
excel.Workbooks.add //Make Sheet1
excel.Visible = true
Dim XString as String
Dim i as Integer
//Add data to sheet 1 column A
excel.Worksheets(“Sheet1”).Cells(1,1).Value = “Carrots”
excel.Worksheets(“Sheet1”).Cells(1,2).Value = “4.59”
excel.Worksheets(“Sheet1”).Cells(2,1).Value = “Potatoe”
excel.Worksheets(“Sheet1”).Cells(2,2).Value = “3.45”
excel.Worksheets(“Sheet1”).Cells(3,1).Value = “Cabbage”
excel.Worksheets(“Sheet1”).Cells(3,2).Value = “2.45”
MsgBox “Sheet 1 is populated with data”
excel.Worksheets.add //Make sheet 2
//Add data to sheet 2 cell A1
excel.Worksheets(“Sheet2”).Cells(1,1).Value = “Cabbage”
MsgBox “The word ‘Cabbage’ is added to sheet 2 for VLOOKUP”
excel.Range(“Sheet2!B1”).Formula = “=VLOOKUP(A1,Sheet1!A1:B3,2,FALSE)”
MsgBox “The lookup value for Sheet1 Cabbage is placed in B1 of Sheet2”
Exception err as OLEException
MsgBox err.message
End Sub
[/code]
This example creates a table with costs for food on Sheet 1. A value to look-up is placed in cell A1, and the VLOOKUP value is placed in cell B1 of Sheet2.