VBA (Visual Basic for Applications) is the dominant programming language for Microsoft Office Applications [Word, Excel, Access...].
0
votes
0answers
7 views
Consolidating data from 3 different workbooks into a new sheet on the master workbook and then delete the sheet
Public Sub CombineAllOpenWorkbooks()
Dim parentWb As Workbook, childWb As Workbook
Dim destinationWs As Worksheet, sourceWs As Worksheet
Dim highestRowCount As Integer
Dim ...
-1
votes
0answers
13 views
How to hide the SQL Server Login prompt
Is it possible to hide the SQL Server Login dialog box? because every time I run my program, the SQL Server Login prompts that asks for a user name and password when the server i entered is invalid. I ...
0
votes
1answer
12 views
How do I set shape.fill.userpicture from one shape to another
I was hoping to set the fill property of one shape (where shape 3 contains a user picture) to the fill property of another shape but it doesn't seem possible. Here is my code.
...
1
vote
1answer
15 views
How to test if some of the optional parameters of an excel-VBA procedure is supplied?
I am trying to write a VBA macro to AutoFilter a table using the users input. I need the user to indicate the columns to filter and also the Criteria for that field. Therefore I thought to define a ...
0
votes
0answers
19 views
Destroying Custom Objects in VBA for Excel
I am making a mock trading program in which I have created a Class called "Order." When a user in the program decides to cancel an order, I need the instance of the "Order" class that they are ...
3
votes
1answer
18 views
How to make WinHttpCrackUrl work in 64-bit
I have Visual Basic for Applications code that uses WinHttp and works flawlessly with 32-bit Office 2010 running on 32-bit Windows XP. The same code fails to run properly on 64-bit Office 2013 on ...
0
votes
1answer
21 views
Excel 2010 VBA: Save file using value from cell to determine path and filename
I am trying to write some code that will save several tabs as a pdf document in folder specified by files within excell. I would like for cells within the document to dictate where this file is saved. ...
0
votes
0answers
20 views
Excel VBA: Automation error- Remote procedure call failed
I am extracting certain data from a website. I have to do this task for at least million rows from a table. I am using excel VBA to connect with MySQL.
Using MySQL to connect with excel VBA, I am ...
-1
votes
0answers
33 views
Best way to import data in excel from SQL Server db
What is more secure way of importing data to excel from SQL server? By Importing it From SQL Server(Which creates connection to SQL Server table) or From Analysis Services(Which creates connection to ...
0
votes
1answer
28 views
VBA Macro to automate histogram throwing error 400
I wrote a macro to produce a histogram, given a certain selection. The code for the macro looks like this
Sub HistogramHelper(M As Range)
Dim src_sheet As Worksheet
Dim new_sheet As Worksheet
Dim ...
0
votes
3answers
22 views
How to Loop Through 5 Cells in a Row Using Excel VBA
I want to loop through 5 cells, Q5 - U5.
With each cell I want to check if the value is equal to "Y", and if yes, highlight the cell to make it green.
How may I do so? Can't seem to figure it out.
...
0
votes
1answer
53 views
Working with Access from Excel? (DoCmd.TransferSpreadsheet, recordsheets)
'Create the recordset
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:="Table1", _
ActiveConnection:=dbConnection, _
CursorType:=adOpenDynamic, _
...
0
votes
1answer
19 views
Excel VBA - Import or copy columns to another worbook in a different order
I am trying to import an excel file having headers as A1, B1, C1, D1, A2, B2, C2, D2, A3, B3, ...D4 and each of the headers having data for 150 rows. I have to import this data set to another workbook ...
1
vote
1answer
19 views
Equivalent of “GoTo” in Google Apps Script (equivalent VBA-GAS )
<h2>
</h2>
When writing my VBA macros I often used "GoTo" so as to jump to a previous part of the macro without leaving the Sub. Now that I’m converting ...
google-apps-script
0
votes
0answers
9 views
Importing a range from workbooks in a file
I need a VBA code to Copy range A5:G16 in a sheet called Daily from 10 files with different names that are in a folder named import. I am new to VBA and can't wrap this around my head yet.
0
votes
2answers
27 views
Looking up values in cells from 2 different spreadsheets and returning a Y or N value
In Excel, if 1 cell contains value (IP Address), and 1 cell in another spreadsheet has that value, but not JUST that value (example: RSServer IP Address Location), I would like to return a response of ...
0
votes
1answer
23 views
Worksheet_Change not working correctly in VBA
Function Censor(ByVal str As String) As String
str = Replace(str,"Wazzle","Wa**le")
Censor = str
End Function
Private Sub Worksheet_Change(ByVal Target as Range)
For each cl. In Target.Cells
...
0
votes
1answer
18 views
VBA script in Excel to populate the .from field from a cell in the worksheet
I have sucessfully configured a VBA script based on an example from http://www.rondebruin.nl to send a copy of an active worsheet in excel as an attachment using a gmail account and CDO.
What I would ...
0
votes
1answer
27 views
Update Data in Other Excel Sheets of a Workbook
This is a direct reference to a previously asked and answered question located here: Automatically Update Data in Other Excel Sheets of a Workbook
That script works fine, but for some reason, it ...
0
votes
1answer
29 views
Creating an array of checkbox names
I'm having some trouble with creating an array of activex checkbox names. I want to create this array so I can use a For loop instead of having to type each checkbox code out separately. Here is what ...
0
votes
1answer
23 views
Passing a global variable in vba to a function
So i have this number of different two dimensional arrays that contain different physical attributes of a flow (mach number, temperature, etc). i need to plot these values in excel and calculate ...
2
votes
3answers
24 views
Having an MS Office UserForm detect which subroutine called it
In a VBA project of mine I am/will be using a series of reasonably complex userforms, many of which are visually identical but have different subroutines attached to the buttons. As a result I'm not ...
0
votes
3answers
21 views
vba range (cells(), cells()) copy past to other worksheet with out activating the other worksheet
I have a cell in a worksheet named A and I have to copy it then paste it to Range(Cells(23, 60), cells(23, 78)) of worksheet called B.
How should I do ?
I thought about using dynamic cell references ...
1
vote
2answers
13 views
Making a pop up window whenever there is a change in cell value
I wrote a simple macro
=IF(C2=H2,1,))
so whenver value of C2 equals value of H2, pop up window saying "HI" appears
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
...
0
votes
1answer
24 views
Scanning worksheet in Excel and rounding in .5 increments
I have a large collection of data points in Excel that go to the first decimal place, and I need to scan through the data and have it round all values to .5 increments. ie: if its 10.1 it should be ...
0
votes
0answers
24 views
find value in a column
I have a excel sheet with following data:
ColumnA5=Name
ColumnB6=Amount
ColumnC6= description
I want to find specific value in column C6 and fill the correspondent cell with "Not Present"
I'm using ...
0
votes
1answer
12 views
Reload data on worksheet change
I have a Excel workbook with two worksheet (sheet1 and sheet2). I've some cell of sheet1 referenced from sheet2.
I've done this using formula like this...
=sheet1!C2
This should change the derived ...
0
votes
0answers
29 views
There is a way to sort columns without using the built in sort function?
I pretty much want VBA to do the following
1) Cut the selected row
2) Insert the row in the correct spot, in alphabetic order (based on col C)
The reason I cant use sort is because I have tons of ...
0
votes
0answers
15 views
How to filter pivot table based on cells range?
i have a pivot table in a worksheet
in the worksheet i also have a range of cell
what i want to do is to filter the pivot table based on the values in the range
of course that if there is blank cells ...
-1
votes
1answer
25 views
Search for string in all sheets except first sheet and write corresponding sheet names to first sheet of excel file using macros
Let us say our first sheet's name is 'A'. How can we write data's sheet names in same column(B1) to sheet A. And again we search for SheetA's C1,D1 so on..
i found this code here this is useful but ...
0
votes
1answer
17 views
excel error: invalid or unqualified error
Sub Macro5()
'
' Macro5 Macro
'
'
Dim wbk As Workbook
Application.ScreenUpdating = False
Set wbk = Workbooks.Open("g:\Work\EU Personal Assignment.xlsx")
Range("O2:R2").Select
...
0
votes
1answer
16 views
printing text in multiple sheets in excel VBA
Hi I have this excel table. The table is in A1:D4 range in a 'Master' sheet
Item Price 1 Price 2 Price 3
Apple 12 25 30
Orange 15 12 90
Berry 55 ...
3
votes
2answers
34 views
Range in VBA excel
Can someone please help me understand the below code.
Set items = Range("A2:A" & Range("A1").End(xlDown).Row)
What I dont understand is how the range is set up? Which area is covered by A2:A ...
1
vote
1answer
18 views
Deleting content in multiple sheets - Excel VBA
Private Sub CommandButton2_Click()
sheetNo = 1
With Worksheets("Sheet" & sheetNo)
.Range("A1:B12").ClearContents
End With
sheetNo = sheetNo + 1
End Sub
Assume I have 10 sheets (Sheet1, ...
0
votes
2answers
31 views
Batch convert Excel to text-delimited files
Hi I'm facing a problem on dealing with converting Excel spreadsheets to txt files.
What I want to do is to create a Macro which can takes all the xls files in one folder and convert them to txt ...
0
votes
1answer
21 views
looping and processing through all the excel files stored in the sub-directory which is further stored in a directory
I have more than 3000 excel files stored in different sub-directories according to dates, for example '1st January 2013' directory has about 20 files. File names are stored according to the time. I ...
0
votes
2answers
15 views
How to permute an Excel column of values?
Let's say we have a column of cells value in column A and need to permute this column randomly into column B as in this simple sample.
How can I do that?
I tried with my VBA code but it seems not ...
0
votes
0answers
4 views
macro for creating scatterplotts for each variable with each variable looping through an excel table
I have to make approximately 1500 scatter plots from a single data sheet and I am new to macro programming. I had a look on your site and found an explanation how to create scatter plots but it does ...
0
votes
1answer
19 views
How to prompt a msgbox if connection in the server fails
I have here the connection string:
sConn = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=VQPBOS;Data Source=" & "GHSI" & strcode
How do I ...
1
vote
1answer
66 views
Protecting Excel Worksheet Data From Savvy User
I'm creating a excel application that will be distributed to my associates. Based permissions I need to disable access to certain sheets and features. I've been able to protect and hide sheets but a ...
0
votes
1answer
16 views
Select String within Cell and Paste to Another Sheet
In Sheet1 cell A1 is the string "the cat sat on the mat". I can click into the cell and select "the cat" with my mouse and Ctr+C to copy it to the clipboard however...
I would like to be able to ...
1
vote
1answer
28 views
VBA - Compile Error, Expected: line number or label or statement or end of statement
I want to use Excel VBA to set up Task Reminders in Outlook, so I found this code from here:
http://www.jpsoftwaretech.com/using-excel-vba-to-set-up-task-reminders-in-outlook/
Dim bWeStartedOutlook ...
0
votes
3answers
49 views
Find cell location if blank
I have a Sud that tells me if a column has a blank cell.
Is there a way to also get the cell location if it is blank, There can be thousands of row and maybe one or two blank cells, they are easy to ...
0
votes
1answer
16 views
Clear cells that are dependents and have validation lists
I need some help. I'm setting up a spread sheet that has multiple validation lists. Each validation list has multiple validation lists linked to them (dependents). When I change the first ...
0
votes
1answer
20 views
Copy last row in a column in Excel with VBA
I would like to copy the value of the last row in a specific Column in to another cell,
for example this is the code which I a suing to find which is the last used rown in the column G
Dim LastRow ...
1
vote
1answer
33 views
Having problems with .InitialFileName view with FileDialog command
So I am using the FileDialog to select a folder full of images that I am putting into a PowerPoint presentation and I am having problems getting the right initial view to come up.
Here is what I have
...
0
votes
1answer
42 views
Pasting column data to a specific location in a series of sheets in Excel via Macro
Hi I have an issue to solve. Appreciate if someone can help me on the following.
In Master Sheet I've got something similar
Item Price 1 Price 2 Price 3
Apple 12 25 ...
0
votes
1answer
28 views
VBA Send an Email from Excel with Follow-Up Reminder
This question is about VBA capabilities. I've been sporatically working on a VBA project for a few months. The macro replaces a current manual process of copy/pasting data from a workbook into a new ...
2
votes
1answer
37 views
Excel vba prevent dragging worksheet order
is there a vba way to prevent rearranging of worksheets? For example: Sheet1, Sheet2, Sheet3 can't be drag to a new order of Sheet3, Sheet2, Sheet1.
For example: I have code Sheet1.cells(1, 1).value ...
1
vote
3answers
31 views
Custom VBA UserForm Hotkey inside Form
I want to have my userform close (or other commands) without using accelerator keys.
For example, the following form is opened from Excel when the user presses F4. I would also like it to close ...
