Import / Export with VBA to Excel spreadsheet

[LSA-skan]

Hi

Im struggeling with creating a code that opens a certain Excel spreadsheet and retrieves / inserts info.

the spreadsheet is a list containing part numbers for purchased components, therefore the list must be filled with information from iproperty. But it has to be where the next available number is. i had this working in iLogic, but have to move it to VBA for better use. the code that worked in iLogic is this one.:

http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/Get-new-part-number-from-Excel-with-iL...

If possible it would be nice if the code at the same time retrieves the new partnumber and saves the active part as this part numeber...

/LSA

[jdkriek]

Hey LSA,

Did you get this resolved or do you still need help?

[LSA-skan]

Hi Jonathan

This is not resovled yet.. but i have been working on getting closer to a soultion, working with both iLogic, and VBA... but i have met a dead end i think... 

I have this Code in iLogic, that is suposed to insert iproperties title in the next empty row in excel, but i can't get it working correctly... as you can see i have tried many different things.. 🙂

This particular atempt is suposed to insert the title in the cell next the the cell with the number 1 in it... when the cell is filled the number 1 is replaced with the actual new partnumber, and the below cell now contains 1 (all this number changing is happening within the excel spreadsheet)... it works ok, but since my code keeps on going, it inserts the title in every cell all the way to the end (100) which of course is not how it´s suposed to be..

I know it is possible to get the code to find the next empty cell, and the do something and then stop running untill next time it is activated.. but i haven't found out how to stop it... 

Also im sure there is a much easier way to approach this, it doesn't realy matter it it´s VBA or iLogic, cause i plan to get the VBA macro to run the iLogic rule anyways... 

GoExcel.Open("I:\TEGNINGSNUMRE SKEMAER\Standard nummer udtagning-test.xlsx", "80000")
'Get new partnumber from Excel
'NewPN = GoExcel.CellValue("J1")
'MsgBox(NewPN)
'Find next empty row in Column B
'MsgBox(RowPN)
For RowPN = 2 To 100
'RowPN = RowPN + 1
'MsgBox(RowPN)
If GoExcel.CellValue("B" & RowPN) = 1 Then
'MsgBox(RowPN)
'If (GoExcel.CellValue(myXLS,"80000", "B" & RowPN) = ("")) Then
'Insert title from open Document
GoExcel.CellValue("C" & RowPN) = iProperties.Value("Summary", "Title")
'Save File with the new partnumber NewPN
'ThisDoc.Document.SaveAs(ThisDoc.WorkspacePath()&NewPN, True)
'MsgBox(RowPN)
End If
Next

/LSA

[philippe.leefsma]

Hi LSA,

Your query doesn't appear to be directly related to the Inventor API, it is rather Excel specific, so you may find more help on an Excel dedicated forum.

When you mention "I know it is possible to get the code to find the next empty cell, and the do something and then stop running untill next time it is activated.. but i haven't found out how to stop it..."

My suggestion would be to check if the cell content returns an empty string or not. However, since I'm not an Excel API expert, there might be a better way or a specific method in the Excel API to check a cell is empty.

Below is a C# code sample I was writting in order to export some list to Excel, you may find it useful:

private void ExcelExport() 
{
    try
    {
        SaveFileDialog sfd = new SaveFileDialog();

        sfd.Filter = "Excel Files (*.xls; *xlsx)|*.xlsx;*.xlsx";
        sfd.Title = "Export to Excel format";

        if (sfd.ShowDialog() == DialogResult.Cancel)
            return;

        Excel.Application app = new Excel.Application();
        Excel.Workbook wb = app.Workbooks.Add(1);
        Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

        ws.Cells[1, 1] = _listViewEx.Columns[0].Text;
        ws.Cells[1, 2] = _listViewEx.Columns[1].Text;
        ws.Cells[1, 3] = _listViewEx.Columns[2].Text;
        ws.Cells[1, 4] = _listViewEx.Columns[3].Text;
        ws.Cells[1, 5] = _listViewEx.Columns[4].Text;

        TreeNode rootNode = _treeView.Nodes[0];

        int rowIdx = 2;

        foreach (ListViewItem lvi in _listViewEx.Items)
        {
            ListViewItemTag tag = lvi.Tag as ListViewItemTag;

            if (tag.TreeNode.Parent == rootNode)
            {
                ++rowIdx;
            }

            int columIdx = 0;

            foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
            {
                ws.Cells[rowIdx, ++columIdx] = lvs.Text;
            }

            CustomControls.LabeledProgressBar ctrl =
                _listViewEx.GetEmbeddedControl(columIdx, lvi.Index) as
                    CustomControls.LabeledProgressBar;

            if (ctrl != null)
                ws.Cells[rowIdx, ++columIdx] = ctrl.LabelText;

            ++rowIdx;
        }

        object objMissing = System.Reflection.Missing.Value;

        wb.SaveAs(sfd.FileName,
            objMissing, objMissing, objMissing, objMissing, objMissing,
            Excel.XlSaveAsAccessMode.xlExclusive,
            objMissing, objMissing, objMissing, objMissing, objMissing);

        wb.Close(objMissing, objMissing, objMissing);

        app.Quit();
    }
    catch(Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(
            "Error during Excel export: " + ex.Message,
            "Excel export error",
            MessageBoxButtons.OK,
            MessageBoxIcon.Exclamation);
    }
}

Regards,

Philippe.

[LSA-skan · Accepted solution]

Hi Philippe

Thanks for you reply 🙂

Sadly im rookie with a Cap R to programming, so your code didn't help me much. 😕

... BUT I worked it out..! I actually found an Excel VBA code that did the trick, and i converted it to iLogic, and it works perfect..!

Navn = iProperties.Value("Summary", "Title")& " " & iProperties.Value("Custom", "Raavare")& " " & iProperties.Value("Project", "Description")
GoExcel.Open("I:\TEGNINGSNUMRE SKEMAER\Standard nummer udtagning-test.xlsx", "80000")

RowN = 2

Do
'DoEvents
RowN = RowN + 1
Tomcelle = GoExcel.CellValue("C" & RowN)
Loop Until Tomcelle = ""

GoExcel.CellValue("C" & RowN) = Navn

GoExcel.Save

 Thanks for all your replys.. 🙂

/LSA

[HIREN90]

I have some results that are displayed through a message box. Can anybody help me to export those results to excel in forms of rows and columns?

[philippe.leefsma]

Here is a short example:

Sub CreateExcelDoc()
    
    Dim AppXls As Excel.Application
    Dim wb As Excel.workbook
    Dim ws As Excel.WorkSheet
    
    Set AppXls = CreateObject("Excel.Application")
    Set wb = AppXls.Workbooks.Add
    
    Set ws = wb.Worksheets.item(1)
    
    'Range is The Cell Address
    ws.Range("A1").value = "Cell A1"
    ws.Range("A2").value = "Cell A2"
    ws.Range("A3").value = "Cell A3"
    
    wb.SaveAs ("C:\Temp\TestCreate.xls")
    
    Call wb.Close

End Sub

 As Excel is not an Autodesk-specific technology, I invite you to seek more information if you need to directly from Microsoft resources or Excel programming forums:

Getting Started with VBA in Excel 2010

Excel 2010

Hope that helps,

Philippe.