spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » VBA » Page 1
How do I get the Aggregated Bodies from a CATPart in CATIA API's?
This took a while to figure out, and I'm definitely posting it on the web because it's barely documented. The problem with Bodies automation is the fact that all bodies are stored in the part level collection. I didn't see that at first, because I'm used to the Geometrical Set and OGS recursion when working with CATIA spec tree navigation. But the fact that all bodies are stored in the root l

Categories : Vba

Character Limit when using 'Line Input #1'
You can try to use input instead of line input: Dim strFileHeaders As String * 2568 Open rstFiles![Path] & rstFiles![filename] For Input As #1 Input #1, strFileHeaders Close #1 It works on Access 2010, I don't know if Access 2002 has more restrictive max length also on input.

Categories : Vba

VBA not showing any result on worksheet
Try qualifying the Range method with a specific sheet, e.g., Sheet1.Range("BB" & iCntr).NumberFormat = "0.00" or Sheets("name-of-sheet").Range("BB" & iCntr).NumberFormat = "0.00" You can save yourself some typing by using a With statement, like this: With Sheets("My Sheet") ' more code .Range("BB" & iCntr).NumberFormat = "0.00" End With

Categories : Vba

Overwrite contents of file in VB
Found this code at http://www.mrexcel.com/forum/excel-questions/325574-visual-basic-applications-check-if-folder-file-exists-create-them-if-not.html for creating a new file (unless one already exists) and then opening it (existing or new). Once you open, you can just do a Sheets( NAMEOFSHEET").Cells.Clearto clear the cells and then paste your data. Sub btncontinue_Click() Dim myFile As String, m

Categories : Vba

VBA How to clear favorites folder list in outlook 2010?
The Outlook object model provides the Delete method for the Folder class. Here is what MSDN states for the Delete method: The Delete method deletes a single folder. In general, deleting a folder does not require first deleting the items in the folder. Deleting the folder also deletes all items in the folder. An exception would be if the folder is an Outlook folder that cannot be deleted, such as

Categories : Vba

"Error 91: Object variable or With block variable not set" in code running perfectly last Friday
That is because rCategory is Nothing. The .Find is not returning anything. To test it, change your code to this. For iCategory = 1 To UBound(vCategories) Set rCategory = rPatterns.Find(What:=vCategories(iCategory)) If rCategory Is Nothing Then MsgBox vCategories(iCategory) & " Not Found" Else .Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color

Categories : Vba

Compile error with If statement on radiobutton.value
If the Radioboxes are on UserForm then if you want to check their value then 1. the UserForm must be loaded at that time and 2. you need to refer to the UserForm. Example: if UserForm1.OptionButton1.Value = true then The radiobox (OptionButton1 in my example) is member of UserForm and not of ThisWorkbook.

Categories : Vba

Splitting MS Publisher 2010 document into multiple files
Try coding something like this: Open Publisher application (CreateObject()?) Open Publisher document (doc.Open(filename)) Store the total amount of pages in a global variable (doc.Pages.Count) Close document (doc.Close()) Loop the following for each page Copy the pub file and rename it to name & "page" & X Open the new pub file Remove all Pages except page X from the pub file doc.Save(

Categories : Vba

VBA - Submit - Input tag
I would just replace this ... Set form = ie.document.getElementsbytagname("input") Set button = form(2).onsubmit form(2).submit ...with this... Set form = ie.document.getElementsbytagname("input") For Each btn In form If btn.Value = "Login >" Then btn.Click Exit For End If Next Why? Because you know that on the button there is always written "Login >", while you a

Categories : Vba

How to create dynamic label within a datarow in a Microsoft Access Tabular Form
In the form-designer open the recordsource-editor add the table you need, add the fields you need, then in the next empty field add something like this: DynLabel: Row1 & '+' & Row2 & '+' & Row3 If you need to cut the Character at the beginning you can use mid(Row1;2) which means "only use this string from position 2.

Categories : Vba

vba code to only highlight contiguous duplicates in one column
The below code will highlight all non-blank and duplicate values for all cells in columns B through F: Sub HighlightSameValues() Dim rngArea As Range Dim rngCellA As Range Dim rngCellB As Range 'Narrow the search area to only that which has been used Set rngArea = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("B:F")) For Each rngCellA In rngArea 'No point in searching

Categories : Vba

How to Add Fields (Columns) in a QueryDef Using Access VBA
The only way you can add a field to a QueryDef object is by changing the SQL statement in the .SQL property to output an additional column. Yes, the QueryDef object has a .Fields property, but it is read-only (ref: here).

Categories : Vba

Substitute Application.Getopenfilename with automation
Assuming that you still need the user to pick the folder containing the files that need to be consolidated, using the FileDialog(msoFileDialogFolderPicker) would be acceptable solution. Dim sFilePath As String With Application.FileDialog(msoFileDialogFolderPicker) .AllowMultiSelect = False .Title = "Select folder to consolidate" If .Show = -1 Then 'Get the first file listed

Categories : Vba

Excel VB module Text to Hex
Found the answer on line: Function Uni2Hex(Txt As String) As String Dim n As Long For n = 1 To Len(Txt) Uni2Hex = Uni2Hex & Right("000" & hex(AscW(Mid(Txt, n, 1))), 4) Next n End Function

Categories : Vba

Do the following methods search in hidden cells?
If you hide a row with AutoFilter, it will be hidden from FIND()If you hide a row manually, it will be hidden from FIND()If you hide a row by setting its height to zero, it will be visible to FIND()

Categories : Vba

Object Variable Not Set when Closing UserForm
if you don't want the form to close , you can use UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 's properties : Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 'before unloaded Select Case CloseMode Case 0: 'Close initiated by the little [X] button top right of the UserForm 'To prevent closing this way : cancel=true Case 1: 'close initiate

Categories : Vba

WithEvents for Toggle Button VBA
The first step in using the WithEvent process is to add all of the necessary objects to a collect to be preserved in memory for future use. Perform this step when the UserForm is Initialized. In UserForm named UF: Option Explicit Dim ToggleControl As ToggleGroup Dim Toggles As Collection Private Sub UserForm_Initialize() Dim oEach As Object Set Toggles = New Collection With UF

Categories : Vba

How do I measure a non-updated point in CATIA VBA?
After searching around, it appears you can't measure any geometry that isn't updated. You can update one object by using the UpdateObject method in the MecMod Part library. Then run the measurable methods now that you have an updated object. Like this: CurPart.UpdateObject Obj1 If the geometry cannot update, due to an issue with the geometry, you can always skip it with error checking (slo

Categories : Vba

Replace all instances of string in formulas of cells in range?
Try something like, Dim r as range, fw as string, rw as string on error resume next application.calculation = xlcalculationmanual set r = Range("A1:Z99").SpecialCells(xlCellTypeFormulas, 23) if not r is nothing then fw = "$B$2": rw = "$C$3" r.Replace what:=fw, replacement:=rw, lookat:=xlPart, matchcase:=False set r = nothing end if application.calculation = xlcalculationautomatic ED

Categories : Vba

VBA PasteSpecial operation fails?
Since you are using Cut, try Insert instead of PasteSpecial. This is synonymous with "Insert Cut Cells" which you see when you are using the Excel interface: trgtWorkSheet.Cells(lPasteRow, lPasteColumn).Insert Also - make sure the cell you are pasting/inserting to is not in the range which is being cut.

Categories : Vba

Add newline to VBA/VB6
VB have build in constants for newlines: vbCr = Char(13) = CR (Carriage-return character). Used by Mac OS and Apply II family vbLf = Char(10) = LF (line-feed character). Used by Linux and Mac OS X vbCrLf = Char(13) + Char(10). CR LF (carriage-return followed by line-feed) Used by Windows vbNewLine = the same as vbCrLf

Categories : Vba

Scraping data from website using vba
There are several ways of doing this. This is an answer that I write hoping that all the basics of Internet Explorer automation will be found when browsing for the keywords "scraping data from website", but remember that nothing's worth as your own research (if you don't want to stick to pre-written codes that you're not able to customize). Please note that this is one way, that I don't prefer in

Categories : Vba

Meaning of .Cells(.Rows.Count,"A").End(xlUp).row
The first part: .Cells(.Rows.Count,"A") Sends you to the bottom row of column A, which you knew already. The End function starts at a cell and then, depending on the direction you tell it, goes that direction until it reaches the edge of a group of cells that have text. Meaning, if you have text in cells C4:E4 and you type: Sheet1.Cells(4,"C").End(xlToRight).Select The program will select

Categories : Vba

Access VBA: Attachment image won't load in form
So, I created a brand new tiny database to test some things, and figured out my problem. Thought I'd share it, just in case somebody eventually had the same problem I did. It was actually pretty simple. When I added the attachment field to my form, I just dragged it over from the "Add Existing Fields" bar. This linked the Attachment.FileData as the control source for the field, which seemed

Categories : Vba

VBA script to insert anchor position for autoshapes in Microsoft Word 2007
You may be better off creating a macro that works on one shape at a time, or anchor all the shapes manually (which you're almost doing anyway) and then write a macro to take care of the various parameters and settings you want. If you really want to prompt the user while the macro is running you'll need to look into modeless dialogs (not possible on the Mac). See this link: http://www.bettersolut

Categories : Vba

Unable to iterate over other person's appointments with VBA
Found some code here: http://www.slipstick.com/developer/copy-new-appointments-to-another-calendar-using-vba/ which would change how you dim appm... Instead of outlook.appointmentItem it has it simply as appointmentItem. So Dim appm as appoointmentItem Edit: Their syntax also uses Set newCalFolder = GetFolderPath("display name in folder listCalendarTest") to define the cal variable, while you ar

Categories : Vba

Setting default location for a meeting in Microsoft Outlook (via VBA code)
Try this Private Sub olkIns_NewInspector(ByVal Inspector As Inspector) If Inspector.currentItem.Class = olAppointment Then Set olkApt = Inspector.currentItem If olkApt.Saved = False Then If Len(olkApt.location) > 0 Then Else olkApt.location = "(default)" End If End If End If Set olkApt = Nothing End S

Categories : Vba

Which text field should I use in word and how to fill it
You would add : textFormField. you would pass value by Me.FormFields("Text1").Result = "abc" in your controls tab, somewhere next to the formField control find the "formFieldShadding" control. when you select that control your gray shadding will disappear.

Categories : Vba

VBA Send email when cell date entered changes
If you want to check if the entered date is today just check if the target.value equals to Date. Date represents today date in VBA like =Today() in excel. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target.Column = 2 Then 'If target is in column B If Target.Value = Date Then 'If the target value is today

Categories : Vba

Outlook distribution list hanging up on contact items query
Dim Contact As ContactItem For Each Contact In ContactsFolder.Items Debug.Print Contact.CompanyName Next When you defined Contact as ContactItem you are telling VBA exactly what type of thing it should find in Items. This works great, only if all items in the ContactsFolder are actually ContactItems. In other words, you are specifically going through all items in a bag but specifically maki

Categories : Vba

Debug.Assert behavior in MS Access runtime
I don't know if it's any better for your particular use case, but there is an alternative that is better, if you're looking to do this in application agnostic VBA code. VBA has Conditional Compilation. Conditional Complication constants can be declared at the module level, but in this case, it will be better to declare it at the project level. On the menu bar, click Tools>>Project Properties and

Categories : Vba

How to Get the Column/Field Name Using Access VBA
Take a look at this excellent answer from Gord Thompson (whose name is familiar to many here): How to export a table in access to CSV with dot in field names through VBA? It's not exactly what you're doing (this person wanted to remove a "." from field names) but you're doing something pretty similar. You can probably do a DLookup from a crosswalk table to figure out what the column should be

Categories : Vba

Determine which Controls are in the Header/Footer of an Access form via VBA
Depending on which Header section you are talking about, you probably want either: For Each ctl In Me.FormHeader.Controls Or: For Each ctl In Me.PageHeaderSection.Controls However, through discussion in comments it was identified that you are looking to iterate through all of the controls in a form that is a subform in another form, in that case you would use the following: Forms![

Categories : Vba

run-time error '438' Object does'nt support this property or method
See if you get the same error with this code: Sub Retânguloarredondado1_Click() Dim Paths As String ' Set path Paths = "C:UsersNuno BonaparteDesktop" '## Open both workbooks first: Workbooks.Open(Paths & "file1.xlsm") Workbooks.Open(Paths & "file2.xlsm") 'Now, copy what you want from InputFile: Workbooks("file1.xlsm").Sheets("file2").Range("A1:A12").Copy 'Now, paste to OutputFile wo

Categories : Vba

Combining Tabs in Excel to next free Column instead of Row
Your code Selection.Copy Destination:=Sheets("Combined").Cells(Columns.Count, 1).End(xlUp)(2) finds the next free row. To find the next free column: LastCol = Sheets("Combined").Cells(1, Columns.Count).End(xlToLeft).Column Selection.Copy Destination:=Sheets("Combined"). _ Cells(1, LastCol + 1)

Categories : Vba

how do i select a paragraph based on style make changes in the para using VBA
You are running the search on the Selection, but you're not changing that selection between runs. So you just end up making the same text bold over and over again. Here's a way to do what you're doing without the Selection object: Sub ParaStyle() Dim objPara As Paragraph For Each objPara In ActiveDocument.Paragraphs If objPara.Style = "heading3" Then With objPara.Range.f

Categories : Vba

ReDim Preserve error
When using Preserve to resize an array variable (declared as such, not as Variant), you may only alter the upper boundary of the last dimension. You would need to transpose your array, or use a jagged array or other structure like a Dictionary/Collection.

Categories : Vba

VBA auto-complete / suggestion
In Excel, typing ActiveSheet is invoking a property of the default object, Excel.Application . If you are working in Access (based on your tags), the default object is Access.Application, which doesn't have an ActiveSheet property. Instead, Access, will see ActiveSheet as an undimensioned variant variable. To get the Intellisense that you seek, you must: Have a reference to the Excel library De

Categories : Vba

Access 2010 Afterupdate - set date on field
I am sure you have sorted this, but just in case anyone else is struggling: Private Sub txtStatus_AfterUpdate() If Me.txtStatus = True Then Me.CompletedDate = Date Else Me.CompletedDate = Null End If End Sub

Categories : Vba


Recently Add
Modify Chart properties in Access report via VBA (error 2771)
How do I get the Aggregated Bodies from a CATPart in CATIA API's?
Character Limit when using 'Line Input #1'
VBA not showing any result on worksheet
Overwrite contents of file in VB
VBA How to clear favorites folder list in outlook 2010?
"Error 91: Object variable or With block variable not set" in code running perfectly last Friday
Compile error with If statement on radiobutton.value
Splitting MS Publisher 2010 document into multiple files
VBA - Submit - Input tag
How to create dynamic label within a datarow in a Microsoft Access Tabular Form
vba code to only highlight contiguous duplicates in one column
How to Add Fields (Columns) in a QueryDef Using Access VBA
Substitute Application.Getopenfilename with automation
Excel VB module Text to Hex
Do the following methods search in hidden cells?
Object Variable Not Set when Closing UserForm
WithEvents for Toggle Button VBA
How do I measure a non-updated point in CATIA VBA?
Replace all instances of string in formulas of cells in range?
VBA PasteSpecial operation fails?
Add newline to VBA/VB6
Scraping data from website using vba
Meaning of .Cells(.Rows.Count,"A").End(xlUp).row
Access VBA: Attachment image won't load in form
VBA script to insert anchor position for autoshapes in Microsoft Word 2007
Unable to iterate over other person's appointments with VBA
Setting default location for a meeting in Microsoft Outlook (via VBA code)
Which text field should I use in word and how to fill it
VBA Send email when cell date entered changes
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.