- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
CONFIRM EXCEL WORKSHEET OVERWRITE ON PARTS LIST EXPORTING
I'm using the so known code to export parts lists to an excel workbook, and it's working as expected. However my template already has a worksheet named as the same name as the table name selected to export, and for my working procees, it has to be the same, The problem here is the Inventor warning asking for confirmation to overwrite the existing worksheet in the template, is there a way to answer "yes" via coding or just supress it?
See the code attached below:
'-------------Start of ilogic ------------------------------------------------ 'get the path and name of the drawing file 'ThisApplication.SilentOperation = True path_and_name = ThisDoc.PathAndFileName(False) ' without extension 'define oDoc oDoc = ThisDoc.Document 'specify the drawing sheet 'oSheet = oDoc.Sheets("Sheet:1") ' sheet by name oSheet = oDoc.Sheets(1) ' first sheet ' say there is a Partslist on the sheet. oPartslist = oSheet.PartsLists(1) ' create a new NameValueMap object oOptions = ThisApplication.TransientObjects.CreateNameValueMap 'specify an existing template file 'to use For formatting colors, fonts, etc DESKTOP = System.Environment.GetFolderPath(Desktop) oOptions.Value("Template") = DESKTOP & "\TEMPLATE_ESTRUTURA.xlsx" 'specify the columns to export 'oOptions.Value("ExportedColumns") = "QTY;PART NUMBER;DESCRIPTION" 'specify the start cell oOptions.Value("StartingCell") = "A1" 'specify the XLS tab name 'here the file name is used oOptions.Value("TableName") = "LISTA MATERIAIS" 'ThisDoc.FileName(False) 'without extension 'choose to include the parts list title row 'in this example "Ye Old List of Parts" is written to the StartingCell oOptions.Value("IncludeTitle") = False 'choose to autofit the column width in the xls file oOptions.Value("AutoFitColumnWidth") = True oOptions.Value("OverwriteTable") = True ' export the Partslist to Excel with options oPartslist.Export(path_and_name & ".xlsx", _ PartsListFileFormatEnum.kMicrosoftExcel, oOptions) '-------------End of ilogic ------------------------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Since this appears to be an Inventor dialog, instead of an Excel dialog, I am thinking you may be able to use the last resort method of SendKeys.SendWait(). If you look at the page for its parent SendKeys Class, it shows you what to enter in the String, to simulate common keyboard interactions. The dangerous part is making sure only that specific dialog receives this interaction that you are 'sending out there', and not some other entity on your computer. There are a couple ways to help with that part. One common way I have used before is the AppActivate(ThisApplication.Caption) method. But there are other more complex ways too, using some vb.net methods, like SetForegroundWindow().
AppActivate(ThisApplication.Caption)
System.Windows.Forms.SendKeys.SendWait("{ENTER}")
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
There may be a timing issue around when that dialog shows, and when the code sends the keystrokes to it. Or it may simply not be setting the focus to that dialog correctly. You will have to test interacting with that dialog completely by manual keyboard interaction, while recording exactly what you had to do on the keyboard. Then try to simulate those actions in the SendWait method. You can also try that other method for ensuring system focus is in the right place. It may take some trial & error testing to get it right on your system.
Below is a different example code I had which manipulates the iProperties dialog using the SendKeys.SendWait method, and that other SetForegroundWindow method I mentioned. It will automatically navigate to the Physical tab for you. At least that's how it works for me. You might also need to include that DoEvents line in your solution, because I forgot about that one in the last responce.
Imports System.Windows.Forms
Imports System.Runtime.InteropServices
Sub Main
ThisApplication.CommandManager.ControlDefinitions.Item("AppiPropertiesWrapperCmd").Execute2(False)
SetForegroundWindow(ThisApplication.MainFrameHWND)
'System.Windows.Forms.Form.ActiveForm.Handle
System.Windows.Forms.SendKeys.SendWait("{RIGHT 6}")
ThisApplication.UserInterfaceManager.DoEvents()
End Sub
<DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
Private Shared Function SetForegroundWindow(hWnd As IntPtr) As Integer
End Function
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi, sorry for late response, I've tested your code, and yet, no result, i'll be busy on another projects for next weeks so this wont be an emergency today, but i'll be learning more of the options you gave, thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
The send key method did not work in my testing and I would be surprised it could work. One method to avoid this is to just delete the exported file before you begin so no overwriting error appears.
If IO.File.Exists(xlBOMPath) = True Then xlWb = xlApp.Workbooks.Open(xlBOMPath) End If
Another method is to delete the sheet in the existing exported file prior to the export method copying the named sheet from the template to the exported file. The downside is any other sheet in the template gets copied over also multiple times.
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports XL = Microsoft.Office.Interop.Excel
Sub Main
Dim path_and_name As String = ThisDoc.PathAndFileName(False) ' without extension
Dim partsListFile As String = path_and_name & ".xlsx"
Dim sheetName As String = "LISTA MATERIAIS"
DeleteWorkSheet(partsListFile, sheetName)
'define oDoc
oDoc = ThisDoc.Document
'specify the drawing sheet
'oSheet = oDoc.Sheets("Sheet:1") ' sheet by name
oSheet = oDoc.Sheets(1) ' first sheet
' say there is a Partslist on the sheet.
oPartslist = oSheet.PartsLists(1)
' create a new NameValueMap object
oOptions = ThisApplication.TransientObjects.CreateNameValueMap
'specify an existing template file
'to use For formatting colors, fonts, etc
DESKTOP = System.Environment.GetFolderPath(Desktop)
oOptions.Value("Template") = DESKTOP & "\TEMPLATE_ESTRUTURA.xlsx"
'specify the columns to export
'oOptions.Value("ExportedColumns") = "QTY;PART NUMBER;DESCRIPTION"
'specify the start cell
oOptions.Value("StartingCell") = "A1"
'specify the XLS tab name
'here the file name is used
oOptions.Value("TableName") = sheetName
'choose to include the parts list title row
'in this example "Ye Old List of Parts" is written to the StartingCell
oOptions.Value("IncludeTitle") = False
'choose to autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = True
' export the Partslist to Excel with options
oPartslist.Export(path_and_name & ".xlsx", _
PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
End Sub
Sub DeleteWorkSheet(partsListFile As String ,sheetName As String)
Dim xlApp As XL.Application = CreateObject("Excel.Application")
Dim xlWb As XL.Workbook
Dim xlWs As XL.Worksheet
'In order to not show Excel
xlApp.Visible = True
xlApp.DisplayAlerts = False
If IO.File.Exists(partsListFile) = True Then
xlWb = xlApp.Workbooks.Open(partsListFile)
For Each xlWs In xlWb.Worksheets
If xlWs.Name = sheetName Then
xlWs.Delete
End If
Next
End If
xlWb.Save
xlWb.Close(False)
xlApp.DisplayAlerts = True
xlApp.Quit
End Sub
Or if this helped you, please, click (like)
Regards
Alan