[Webinar] Streamline your web hosting managementRegister Today


Microsoft Excel





Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

What VBA code would I need to auto accept digital certificates. At the link below it looks like someone possibly found a way to skip digital certificates all together in VBScript. I didn't know if there was a way to do the same in VBA. If this isn't possible, I am also interested in a way to automatically identify any certificate window that opens and somehow navigate it using VBA. (i.e., in effect automatically accepting it.)

Hire Technology Freelancers with Gigs
LVL 11
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

We have created a a .ppt and linked it to an excel file and uploaded both to SharePoint. we would like the .ppt to get it's data from the excel file in sharepoint rather than going back to the users drive. We have tried all sorts of different ways including:

- updating the source with the copied link location from sharepoint
- copying and pasting the data from the exel workbook in sharepoint to the .ppt

... and more with no positive results.

looking forward to getting this resolved.

I have an Excel sheet showing lease details for 20 leases.
The main details are the payment amount each quarter and the dates of those payments.

Below each lease I have rows for their payment date.
I want the amount of each lease's quarterly amount (in row 6) to be shown in the relevant row (from 1/1/2017 below row 32) and in the column of the respective lease.

The file is attached.
How do I achieve this?

I am creating an XML file on a web server using php; technique is perfect. I save the file to a folder on the web server, then download it using ftp & open in Excel, works perfect.

The real intent is to run a chron job weekly at a predetermined time that will create the xml file & email it to recipients as an attachment. I am using the class phpmailer. It sends the email, with attachment correctly, but it cannot be opened using Excel (at least in Windows).

How can I force it to recognize that I want it opened in Excel without having each recipient have to screw with their own associations of extensions.

Here is my code for the mailing:

// send_urpt.php
function date_conv($x) {
 $r = substr($x,5,2) . "/" . substr($x,8,2) . "/" . substr($x,0,4);
 return $r;
$mail             = new PHPMailer(); // defaults to using php "mail()"

$mail->isSMTP(); // telling the class to use SMTP transport
$body             = "";
$mail->SMTPSecure = $GLOBALS['smtp_security'];
$mail->Port       = $GLOBALS['smtp_port'];
$mail->AddReplyTo("lit@lakos.com", "replyto");
$body = "Attached is the usage report for the hvac configurator for the period " . date_conv($_SESSION['psat']) . " through 5PM Pacific Time on " . date_conv($_SESSION['currfri']) . ".";

Open in new window

Have a user that has an excel file emailed to them from another user.  They open the file and add some formulas to it and save it to a folder and then email it back to the other user.  That user (the one that emailed it in the first place) edits some of the formulas and creates new ones and types some info in the file and when they email it back to the user that created the formulas, the formulas are not there.  I know it sounds weird but that is what THEY say is happeing.  I have not witnessed this with my own eyes.  Any ideas?
button to provide the range  for CE (CF the count)

as  show here


this is an range count


here the file
Need to add .128  on IP 10.7.177.* in excel
Format Phone Numbers in column to display as (999) 999-9999.
Currently some ae correct but others display as (999)999-9999
I'd like to do the correction to a new column
Hi, I am trying to update a macro to refresh a file with data from a SharePoint site, but my employer wants to change to a different project file within the SharePoint location, can anyone help as I have no idea! The code is very long....any pointers greatly received!

Option Explicit
'Public Variables
Public intDaysMonth As Integer          'Used to pass number of days in a defined month
Public intColumnLeft As Integer         'Used to pass the left value of a column
Public intColumnWidth As Integer        'Used to pass the width value of a column
Public lngRowTop As Long             'Used to pass the top value of a row
Public intRowHeight As Integer          'Used to pass the height value of a row
Public intGenericCounter As Integer     'Used within arrays and loops
Public varShapeArray(10)                'Variant array used to hold the required shapes for a single milestone entry
Public intShapeCount As Integer         'Used to hold number of shapes for a single milestone entry
Public blnTickTrue As Boolean           'Used to indicate if a tick was added
Public blnContinue As Boolean           'Used to determine if chart build should continue beyond the user preferences dialog
Dim blnOrder As Boolean                 'Used to determine whether to perform send to back operation on shapes
Dim intItemRowCounter As Integer

Public Sub GenerateChart()
    Dim rngTaskHeader As Range
    Set rngTaskHeader = Sheets("Plan").Range("B3")
    intItemRowCounter = 

Open in new window

I am fairly new to VBA, but I would like to create a macro that pulls weekly updated data from a folder with several worksheets (up to 36) and pastes them into corresponding excel database worksheets.  For example, my database worksheets are labeled AA, BB, CC,  etc, so the macro needs to copy the data from the "weekly data" worksheet with the corresponding "AA" and paste in into the database labeled AA.  I have the same number of Excel databases as I do "weekly data" worksheets.  Do I need to hard code a directory list or is there a better way to do this?
2018 Annual Membership Survey
LVL 11
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)


I am at my wits end but getting stuck. Pl. refer to the attached excel file. I want a formula in Col H that will calculate the cost value of the respective stocks on FIFO basis automatically. In FIFO basis, the stock purchased first will be sold first and according to that the cost value of the respective stock has to be calculated after multiple buy and sell transactions for different stocks.

For eg. in the attached excel sheet, the sale value of 225 shares of CCC on 13-09-2017 is 39375 and the cost value on FIFO basis, as calculated manually is 33125 (200 shares purchased on 04-06-2017 @ 150 per share + 25 shares out of 50 shares purchased on 08-08-2017 @ 125).

I need the formula to find the cost value in Col H automatically. The formula should be scalable so that I can add more stocks and transactions and just copy paste the formula.

In Col J & K, I have shown the calculation and the cost value as obtained through manual calculation so that the Cost Value as obtained through a suggested formula in Col H can be checked.

I am happy to clarify further and look forward to the help.

Thanks and regards,

need to gather all the zip files inside the all folder then move the all zip files to another drive in bat.
so can u help me with the code???

i used /s its copying folder also. i dont need to copy folder. just files inside the folder i need to copy to destination.
i tried *-*. its copying all files. i need specific zip files in all folder.

Mkdir "%BackupPath%\%programName%\%date:~4,2%%date:~7,2%%date:~12,2%"
xCopy "%ProgramPath%\%programName%\%datefile%\*.zip" "%BackupPath%\%programName%\%datefile%"
 I need help for this ... it has to copy the details of pass and fail in sheet "newCorp"
It has to take details( from sheets like ww31 , ww33 and more) of last column and paste in newCorp sheet.

I tried this but its not working...

Sub Copy()
Dim str As String
str = Range("C4").Value
With Sheets("newCorp")
    With .Range("C5")
    .Formula = "=Iferror(VLOOKUP(B:B," & str & "!A:O,15,FALSE),"""")"
    End With
   End With
    Selection.AutoFill Destination:=Range("C5:C10")
End Sub
Wanted to email the active workbook based on the email address highlighted in blue whenever I select the supplier name highlighted in yellow by clicking the green "email supplier" button, is this possible?
Need a utility or script to password protect 300 excel files which are located in 300 separate folders on a network drive.
The reason being if someone accidentally emails any of these files they would be protected.

We do have email encryption already, but this would be for the case when a file is accidentally thought to be non-sensitive and emailed in the clear, but actually it is sensitive.
This way we have peace of mind that those files are protected.

Someone can sit and do all 300 using Excel, but I was thinking somebody out there had to make a utility by now for this... like Folder Guard (which didn't work).
The protection has to stay with the file and the files are stored on a Microsoft 2012 server.


 as show here


here the file
button in BX2

to pick up the pair from BF ( 1 set )

repeat from 3 or more only

as  show here

MID PAIR TOP    ( button  in column  FE1  to FI1)

source EO  (1 set  = 13 rows)
How to convert a QRP (Quick Report) to XLS (Excel)
Free Tool: SSL Checker
LVL 11
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi Experts using excel 2016

I have six workbooks each with one worksheet. I want to combine ie create a query where I link each workbook to the other using a primary key..how do I do it..
Working with customised excel ribbons, I can save some ribbon.control objects as user-defined object variables, by recognising their control.IDs during CallBacks, and setting my own corresponding object variables. This works ok: new object variables yield the same ID values as the original ribbon.control objects they were derived from. These user defined variables help to coordinate Button property changes as task context changes occur within the task that the buttons control.

To improve the ease with which I select the appropriate object variables, I would like to save the object variables, or a reference to each object variable, on an excel sheet (for choice) or an array. The 2-D positioning of the objects on a sheet would place them in logical relations to other data, and hence simplify coding.

If Collections are the way forward, I have used collections to create and evaluate responses to ctls in UserForms that have been created on the fly. But I'd rather read the variables from excel cells!! And arrays have the same dimensions as sheets.

I realise that the object variables will not persist when the excel file is closed.
I've read this: https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/creating-object-variables

I know nothing about the possibilities, practicalities and risks of what I think I'd like to do.
Could you give me some advice, or guide me to resources, please.

Excel - My vlookup is not working unless I Double Click on every cell.  Is there an easy way to fix this??
I have a requirement to calculate the lowest price excluding zero, based on an inconsistent range of rows.  When calculating these rows I need to exclude the first row of each (grey row) from the range.   These scenarios as per the attached file, will occur in multiple workbooks.  I have to somehow calculate based on the range of one item code and at the same time exclude the first row of that item code, or if possible base the formula on the color of the cells.
What I want to be able to do is iterate through a spreadsheet.  At this point the code just puts all the results into just the same three 3 rows instead of from row 10 to row 32.
It has something to do with the line in the code where it says rw = 10.  How do you turn this into a range from rw 10 to rw 32?
Does anyone know of a simple Macro to alternate the row colour in Excel when the value in column A changes?

The attached file shows an example of what I want to achieve.

Microsoft Excel





Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.