Questions tagged [vba]
45838 questions
1
votes
0
answer
178
Views
Add Comments to Excel pivot slicer options
I have been trying out searching multiple forums before posting this question here.
Issue: In one Excel pivot table slicer I have almost 50 options. I have given codes to each slicer option rather than writing entire description about the slicer option.
I am looking for some VBA help where in I can...
1
votes
0
answer
31
Views
How to fetch data in R by executing command in excel?
I have to fetch data from Reuters and they provide an excel plugin for that. The problem is that my excel is crashing if I try to fetch too many variables at the same time. I was wondering whether I can do it from R via some excel connection.
In general I want to give the command to excel (from R),...
1
votes
1
answer
1.1k
Views
How to reverse search in Excel?
I have a text in a worksheet like:
The girl is very beautiful
I want a formula to perform a search from right to left for the word 'very', and if found then extract it to some other region of the sheet.
Note: Purpose of doing reverse search is because I want to implement it in my workbook which re...
1
votes
1
answer
94
Views
Object variable not set (Error 91) when using AHK to close an Access Application
I have an Access Form named 'Form1', and in the form it has a couple of subs like, Form_Load, Form_Unload, etc.
I want to use an AutoHotKey script to control the opening and closing of the form.
Form opening code:
acc:= ComObjCreate('Access.Application')
acc.OpenCurrentDatabase('\\...\target_databa...
1
votes
1
answer
45
Views
How to skip specific columns in a Row copy
So, I'm looking for a way to skip specific columns in a row copy. I'm working with doing a bunch of loops to copy/paste from a bunch of sheets of variable sized reports and I want a way to simply skip a column or columns in a row copy since I can't just do a entirecolumn.delete to deal with the exce...
1
votes
2
answer
53
Views
Excel VBA to Remove Opt-Outs
I am a military recruiter and am trying to use autofilter to filter out a range from another range. I got this from another stackoverflow page but can not figure out how to change that string strSearch to a range like [email protected], [email protected], [email protected], etc.
We get lists of leads but I'd like to keep t...
1
votes
0
answer
94
Views
Creating a save as and send command button in word
I am trying to minimize a daily task by creating a form that will have a save as/send command button. After searching similar posts, I found the code below:
How do I define the 'TagNum', 'Date' and 'NTID'?
strTagNum = ActiveDocument.SelectContentControlsByTitle('TagNum')(1).Range.Text
strNTID = Acti...
1
votes
0
answer
38
Views
I am using a cell value in Excel to look up an array based on that cell value
I am using =VLOOKUP(C$2;INDIRECT(C$2);6;FALSE)
One of the inputs (a serial number) is MK05391. When I create the range for this row and name it the same as the serial number it won't let me. Says that The name either does not start with letter/underscore, or there's something else namned the same th...
1
votes
1
answer
74
Views
How to get dynamic node and element values in xml using vb?
I am very new to vb. In my XML file all the attributes, nodes and elements are dynamic(Healthcare.xml). I am trying to get the values in the below manner.
page: login, method: login, args: ad59090,GA124444
In some cases, there will be multiple methods and arguments.
page: benefits, method: tagbenef...
1
votes
1
answer
63
Views
Unable to Click Submit button in webpage using .net
Im creating a automation using VB in microsoft Visual Studio like a app
the project path is Visual Basic>windows classic Desktop>windows form
app(.NET framework).
Code is written using VBA and it is a form which gets
user input and paste it into a webform and click a submit button and create a T...
1
votes
0
answer
326
Views
Trouble with sending a left click
I have a series of clicks and cursor movements I have in a macro. Everything works fine until the end. All the clicks work until the last click. I have tried to put various amounts of sleeps in between the clicks and before the clicks.
Whats odd is that when i click for real it works! ahh
Whats also...
1
votes
1
answer
210
Views
VBA Run-Time Error 1004 on ActiveCell.FormulaR1C1
The formula in Excel cells '=DATEVALUE(IF(ISERROR(INT(LEFT(MID(D2,FIND('/',D2)+1,LEN(D2)),FIND('/',MID(D2,FIND('/',D2)+1,LEN(D2)))-1))),DAY(D2),INT(LEFT(MID(D2,FIND('/',D2)+1,LEN(D2)),FIND('/',MID(D2,FIND('/',D2)+1,LEN(D2)))-1)))&'/'&IF(MID(D2,LEN(D2)-4,1)='/',INT(LEFT(D2,FIND('/',D2)-1)),MONTH(D2))...
1
votes
3
answer
541
Views
Excel VBA ByVal Target As Range Does not update Target Worksheet when excuting from a function cell
I'm having a issues with copying a value from a cell that is using a function to get its value from another cell. Insert value into A1, A3=A1 and when ever the value of A3 changes the changes need to transfer automatically to column H. The issues i having is that if i type the value into A1 then A3...
0
votes
1
answer
14
Views
How to read multiple text files (with multiple lines of data) into a two dimensional array?
DESCRIPTION:
I have 10 text files. And I want to read them into a two dimensional array. Each file looks like this and the number of lines vary. There are 5 lines in this text file but other text files may have more or less than 5 lines.
No. Location(ft) Mix Near Far Comp. Height(ft) B(in) D(in) An...
1
votes
0
answer
35
Views
How to access the same session used by WinHttpRequest in a new browser instance?
I would like to automate a currently manual process in retrieving multiple cookie data (e.g. both 'sessionToken', and 'sessionID' from cookie data that's been sent out in a GET request header, which is triggered from browsing to the URL page) as well as parsing a value from the page's inner HTML (e....
1
votes
2
answer
278
Views
Excel VBA Changing Combobox list from static range to dynamic
This should be pretty simple, but I am struggling.
Right now, this code works:
cboCategoryEdit1.List = Sheets(2).Range('A2:A40').Value
I am trying to 'clean up' my project by changing how the combobox is populated. I'd like it to be a combobox with a range that only takes populated cells. Meaning...
1
votes
1
answer
221
Views
Access VBA: Invalid field data type
With an Access VBA, I am trying to load a file from a directory to the table [table name] in the field [field name] which Data Type is Attachment.
The problem is that I get the error Invalid field data type.
The code is the following.
Dim SQL As String
SQL = ' SELECT * FROM [table name] WHERE ID = '...
1
votes
1
answer
37
Views
Folder Explorer is Not Work well in Excel VBA
Private Sub ButtonPath_Click()
Cells(1, 32).Value = FolderExplorer()
End Sub
Above is my UserForm's Code.
Below Code is My Module's FolderExplorer() Function.
'//Source : http://software-solutions-online.com/vba-folder-dialog/ , edited by me.
Public Function FolderExplorer()
Dim intResult As Integer...
1
votes
2
answer
1.8k
Views
Run Macro without opening Excel
This is my first time using VBs. I used to it to automate Excel macro but I failed due to the spacing between filenames. It works fine if the space was removed. I read this post and used the double quote method but did not work.
This is how I used it objExcel.Application.Run '''C:\Users\account\Desk...
1
votes
0
answer
112
Views
Batch Print email attachments in order by date
Issue:
Hello, I have a macro, which saves me for a lot of clicking, but in the end not so much time, when it all comes down to it. The code allows me to mark everything within any mail folder. It will print the attachments within the email.
After this the error starts. Whenever I press this button,...
1
votes
0
answer
414
Views
Highlight Cell in one Column BASED on Conditional Formatting of Other Columns
I am a beginner with Macros. I slightly changed my code below to include conditional formatting based on an Input Value. The code below applies a conditional format (orange highlight) to 6 specific columns based on whether those columns contain a specific text. I would like to add another step afte...
1
votes
2
answer
238
Views
How to add dynamic comment to column with validation list?
Given an excel column with a validation list (dropdown).
How do I show a dynamic comment based on the selected option?
Thanks
1
votes
0
answer
93
Views
Copy Range of cells to Visio (2013) using VBA code
I have an Excel range of cells (let's call it 'Table A') and I would like to copy that Range/Table into Visio using VBA.
Without VBA - I can copy the range in Excel, go to Visio - Insert - Chart - go to Sheet1 tab - Paste
Using VBA - I am a novice so this is what I think it should look something li...
1
votes
0
answer
54
Views
Combining vba filters
Does anyone know how to combine these two lines so that when I run the code rows that contain Oranges do not appear. At the moment, only the first filter is working. So I get all of the Apples, but the second filter does not.
Thanks in advance!
ActiveSheet.Range('B4', Range('BT4').End(xlDown)).Auto...
1
votes
0
answer
44
Views
How can I compare and change cells in Excel
I have two sheets. In both sheets I have a column (B) where each row is a different name:
Sheet 'A' has names in the column, these names are 'leading'.
Sheet 'B' also has names in this column, these names CAN BE outdated.
What I want: A macro which can check the names.
delete the names in Sheet B...
1
votes
1
answer
52
Views
Save Specific Attachment File Types
I have VBA code to automatically download (save) received mail attached files.
I need to make a condition to only download (save) .xlsx or .jpg files.
Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem)
Dim oAttachment As Outlook.Attachment
Dim sSaveFolder As String
sSaveFolder = 'C:\Users\D...
1
votes
0
answer
531
Views
Excel slicer multi-select functionality turns off when workbook is closed?
I have a workbook that contains a report with tables and slicers.
Currently, when the user opens the workbook, they must manually click the 'multi-select' option on the slicer. Is there a way to keep this permanently selected (even when the workbook is closed and re-opened)?
Thanks!
1
votes
0
answer
285
Views
Excel VBA - Pictures do not display when I add Private Sub macro
I have images pasted (not linked) into my excel workbook. When I try to add any Private Sub macro code, the next time I open my workbook, the images come up with the message 'This image can not be displayed'. However, images on other sheets display correctly, and if I save the workbook on a differen...
1
votes
0
answer
293
Views
Excel VBA Find Date - Run Time Error
I have a problem with my VBA code and I hope someone can help me. What I'm trying to do here is the following:
I have a worksheet (Sheet 1) with data that gets updated every day. In order to preserve this data, I want to copy and store the source data on another worksheet (Sheet 2). Sheet 2 has colu...
1
votes
0
answer
58
Views
Searching and sorting ranges in excel with toggle buttons
I have a spreadsheet containing ±100 named ranges (5 rows, 15 colums) which are sorted based on time.
My plan was to make toggle buttons to hide or unhide named ranges when they contain a certain value.
So when I select the toggle button for 'Opel' for instance, all the named ranges containing 'Ope...
1
votes
0
answer
379
Views
Enabling Run-a-Script option in Rules Wizard (Windows 7, Outlook 2016)
The 'Run a script' option is disabled for security by a Windows update.
Here https://www.slipstick.com/outlook/rules/outlook-2016-run-a-script-rules/ you can see the difference before and after the update: 'start a application' is missing. If you want to look at a screenshot from me (after Microsoft...
1
votes
2
answer
1.9k
Views
Run SQL Update Query in MS Access VBA
I am trying to run this code to fire an update query from VBA. Access is giving me a syntax error. I suspect this has to do with the fact that I'm trying to run an update query using an INNER JOIN with a form. Is what I'm trying to do at all possible?
Private Sub Btn_Edit_Data_Click()
Dim db As DAO...
1
votes
0
answer
136
Views
EXCEL VBA: Testing for non-numeric input data
I am developing a macro program in EXCEL 2016 VBA. I am currently experiencing a problem with my code for one of the initial pre-processing steps: I am trying to check for non-numeric data selected by the user.
I have the following example data:
My code takes the selection specified by the user and...
1
votes
0
answer
148
Views
Error Message Occurs in Saving Excel Files when I use Excel VBA Function
When I try to save the below code it gives an error message and I can't save it.
below are my error message's image file link.
I'm using Korean Excel 2007, So I don't know what exactly this message is in English but I can give you the meaning of this error message.
(this error message means... could...
1
votes
0
answer
30
Views
Conditional colorscale - programmatically
I am struggling to recreate logic behind Excel's three-colors scale for conditional formatting. This is the second option displayed when you choose Home-->Conditional Formatting -->Color Scale (red-yellow-green). I have started with this subroutine:
Sub Tricolore(Rng As Range)
Dim cl As Range
Dim d...
1
votes
0
answer
62
Views
Embed excel into word using vba does not always display the top of the embedded sheet
I have a Word document with VBA code to embed multiple excel sheets using the following process i.e. addOLEObject
Set IllSheet = _
ActiveDocument.Bookmarks('example1').Range.InlineShapes.AddOLEObject(ClassType:='Excel.Sheet.12', filename:=filename1, LinkToFile:=False)
however on occasion the embedde...
1
votes
0
answer
28
Views
Maintain source formatting with column reorder Excel VBA
I have been importing data from a downloaded report into a working-copy excel workbook for some time now, but someone higher up than me recently decided to change the report that I download for my information. The easiest solution seems to be to re-order the information in the report workbook befor...
1
votes
1
answer
246
Views
Excel VBA loop through cells to find Conditional Formatted cells and copy paste on other sheet
My issue:
I want to loop through a range, and whenever it finds a conditional formatted cell, it should copy the cell to the left to the cell to the right of it. And then afterwards paste it into an other worksheet.
My code loops through the cells easily, but doesn't copy the information, and paste...
1
votes
1
answer
30
Views
Unable to Iterate Columns of a Selection — error '424' Object Required [closed]
I'd like to be able to loop through the columns of a selection and do arbitrary things such as set column widths through some calculation. However, all of the variations I've tried that run-time error '424', Object Required.
I've dug through the documentation, these forums, and Google, yet the cl...
1
votes
0
answer
149
Views
How to extend or merge interfaces in VBA
I am creating a custom collection class in VBA, whose members must implement two interfaces. One is a library interface IComparable which allows me to sort my collection, the other is a home-made interface ImyObject which guarantees certain properties of the members of the class. I ensure only the c...