Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.

CONFIRM EXCEL WORKSHEET OVERWRITE ON PARTS LIST EXPORTING

jonathan.victor
Advocate

CONFIRM EXCEL WORKSHEET OVERWRITE ON PARTS LIST EXPORTING

jonathan.victor
Advocate
Advocate

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? 

 

jonathanvictor_0-1681310737486.png

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 ------------------------------------------------
0 Likes
Reply
476 Views
5 Replies
Replies (5)

WCrihfield
Mentor
Mentor

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

EESignature

(Not an Autodesk Employee)

0 Likes

jonathan.victor
Advocate
Advocate
I have teste this same sendwait method and yet doen't solved.
0 Likes

WCrihfield
Mentor
Mentor

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

EESignature

(Not an Autodesk Employee)

0 Likes

jonathan.victor
Advocate
Advocate

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.

0 Likes

A.Acheson
Mentor
Mentor

 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

 

If this solved a problem, please click (accept) as solution.‌‌‌‌
Or if this helped you, please, click (like)‌‌
Regards
Alan
0 Likes