Microsoft Excel

136K

Solutions

38K

Contributors

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

my manager is trying directly sending  excel file by opening file going to file -send
send as attachment , it attaches in outlook, but when he tries to open excel file when attached in outlook before sending it takes pretty long time in fact it doesnt open, while on the other hand if he opnes outlook and attach excel file , it opens right away, is there any reason

I disabled the add in but it didn't help
0
Become a Microsoft Certified Solutions Expert
LVL 13
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I have an excel userform tied to access dba, that users can register for classes. They select they name and class they want. They can also go in and delete entry later if they decide not to take class. This works great. I want to make some enhancements. In table that holds the classes I added fields that holds the amount of slots available for each class and another field that holds number of slots taken. Then I based a query of that table and added expression to calculate the difference. Basically number of slots remaining.TEST_FE.xlsm

The goal is so users can see whether or not class slots are available. Once the max is reached I don't want users to be able to add or signup. But would like them to see if any slots available or not per class. So if I could get the slots taken to increase each time the user registers for the a class or reduce the number when a class is deleted.

Does anyone have any suggestion on this approach? Perhaps a sample code with something similar.


Thanks in advance.
TESTDB.accdb
0
Hello experts,

I have the attached document related to my shortcuts.
I was wondering how to:
1-Set up print zone and define front related to sheet 1 and back related to sheet 2
So the result will be two sheets in a the same doc.

This will allows me to have in a single doc if I generate a pdf or if I print document sheet 1 and sheet 2.
If you have questions, please contact me.
Thank you for your help.
1-Key-shortcuts-LDI.xlsx
0
Is there a substitute for for loop in vba?

Thank you,
0
Hello experts,

I am looking for a procedure that allows me to delete blank sheets:

Sub Delete_Blank_Sheets()

Dim oWS As Worksheet
    
    On Error GoTo Error_Routine
    
    For Each oWS In Worksheets
        If WorksheetFunction.CountA(oWS.Cells) = 0 Then
            Application.DisplayAlerts = False
            oWS.Delete
            Application.DisplayAlerts = True
        End If
    Next oWS
    
Exit Sub
Error_Routine:
    MsgBox Err.Description, vbExclamation, "Something went wrong!"
    Application.ScreenUpdating = True

End Sub

Open in new window

I would like to take as a reference to cover the following:
-Msgbox: "Are you sure you want to delete all sheets except active one?" If cancel exit sub
-Delete all sheets except activesheet
If you have questions, please contact me.
Thank you for your help.
0
I had this question after viewing I Want To Extract Information from the Body of an Outlook email Into an Excel Spreadsheet.

I am trying to get an Outlook Macro that can look at the data below that is in an email and new email with the same information and take the numbers 83%, 0.424, 29.017(m), 0.058(m), 454026.136 and 156158.535 and put them into a CSV (Excel file). An example of the email that the data would be extracted from is below. Any help would be appreciated.

SOFTWARE: rsgps  1.38 RS80.prl 1.99.3            START: 2019/09/13 15:02:00
 EPHEMERIS: igr20705.eph [rapid]                    STOP: 2019/09/13 15:42:00
  NAV FILE: brdc2560.19n                        OBS USED:  3024 /  3654   :  83%
  ANT NAME: LEIAX1202GG     NONE             QUALITY IND.   6.44/ 23.23
ARP HEIGHT: 1.6540                        NORMALIZED RMS:        0.424


 REF FRAME: NAD_83(2011)(EPOCH:2010.0000)              ITRF2014 (EPOCH:2019.70038)
     
         X:       834759.906(m)   0.010(m)            834759.073(m)   0.010(m)
         Y:     -5550733.261(m)   0.051(m)          -5550731.703(m)   0.051(m)
         Z:      3018571.793(m)   0.023(m)           3018571.633(m)   0.023(m)

       LAT:   28 25 50.74741      0.007(m)        28 25 50.76858      0.007(m)
     E LON:  278 33  8.88451      0.006(m)       278 33  …
0
Is there anything you can do within an *.xlsx file (created using excel 2010) to determine each time someone has accessed it, and keep the log somewhere of access times etc. I am told the file server on which the file is hosted is not one where at the OS file level access/auditing is enabled and cannot be due to the amount of events it was caused. I was just wondering if anything could be done inside the document itself. Reason I want it enabled is we have put some xlsx files in a 'shared' area and for whatever reasons permissions cannot restrict access - but I want to ensure nobody is generally snooping on access to these files just because they have access to them.
0
Why are all cells desired to have the same green background color the same. Some are not solid.
 o
0
Starting with a string representing a range:
string sortRange = "C10:H24";

Open in new window

This then works:
worksheet.Range[sortRange].Sort(worksheet.Range[sortRange].Columns[1], xlSortOder.xlDescending);

Open in new window

I saw worksheet.Range[sortRange] was listed twice, so I decided to refactor that out:
Excel.Range r = worksheet.Range[sortRange];
Excel.Range c = r.Columns[1];
r.Sort(c, xlSortOrder.xlDescending);

Open in new window

My refactored version gives a much different result. Why is that?
0
I have a named range "UnsortedRng".
The range is in a TABLE.
The UnsortedRng is also used as a Source for Data Validation List in range I2:I33
It is unsorted due to the fact that it is part of a Database, where new entries are added on daily basis. But sorting is not done after the addition of entries due to some restrictions.

I wish I Could use a Sorted Version of the above "UnsortedRng" WITHOUT having a maintain the same List in another column (of the same TABLE or anywhere else) so that the same can be used as a source in Data Validation list.

I understand VBA can hold the data temporarily in memory for such purposes. Hope there is a way in VBA to achieve the above objective.

Regards
Kanwal
SortRange.xlsx
0
CompTIA Cloud+
LVL 13
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

I have an annual budget of $1,000. I'd like to spend $20 in January and increase spending each month evenly. By the end of the year I want to have spent the full $1,000.

I'm looking for a formula that I can type into Excel/GoogleSheets that will allow me to enter a $20 in one cell, $1,000 in another, and then automatically populate 12 monthly cells w/ what the monthly budget is for each. Each month should be more than the previous month.
0
How can I get average of numbers in array to populate another array. Eg

PressureDays( 1 to 36500)
PressureYears (1 to 100)

I want to populate PressureYears (1) = average (pressure days(1) to (365))

Any help would be highly appreciated.

Thank you.
0
How do you swap graphics with slicers? I am guessing you have to create VBA to handle the swap but also allow the user to select other options then once filter is removed the graphs swap back. See attachment video. I Tried doing this with macro but the slicer wont allow you to select other regions.
graphswap.mp4
moch-up-dashboard-sample.xlsb
0
The max row for dataseries in chart varies everytime the macro runs, is there a way to set data series to the number of rows data dynamically. Maybe a vba macro.
thank you.
0
Hi ,

i have ssrs report with many text boxes and each text box will have different size of text. My report is good when i view it in browser, no wrapping of text, can see complete text.
Problem is when i export to excel i can't see second line of text in the cell by default until unless i increase height of cell.

can some one help me how i can view complete text without wrapping or partially displaying? for some cells i tried increasing padding 2pt to 3pt. This is working fine for some cells but not for all. is there any setting or way so that all my text gets displayed properly?

Thanks in advance!
0
Hi

I am using the following code to save a worksheet as a separate file. I now want to include a second sheet "SheetX" in that new file as well. How do I create a new file with these two sheets?

For Each xWs In ActiveWorkbook.Worksheets
       
        oSheetName = xWs.Name
       
        'First check if the sheet is the copy of the tax invoice and make separate file
        If oSheetName = "Copy Tax Invoice" Then
           
            xWs.Copy
            Dim oFileAndPath As String

            oFileAndPath = xPath & "\Ejected Files\" & oDate & " " & oShaft & ".xlsx"
            ActiveWorkbook.SaveAs Filename:=oFileAndPath, FileFormat:=56

            ActiveWorkbook.Close False

        End If
       
        'Next delete the sheet if it is not numeric
        'If IsNumeric(oSheetName) = False Then
            'Set wsDelete = Sheets(oSheetName)
            'wsDelete.Delete
        'End If
       
    Next
0
How does Goal seek and changingcell work in excel macro, please explain.

Thank you,
0
Recently I have been getting .NET library errors while using Macro-enabled Excel documents. When I load the file, there is an error message that says, "Library not found." Macros are disabled, and VBA code is not viewable. I have tried uninstalling and reinstalling Office 365, doing a Quick Repair on the installation, doing a full repair on the installation, updating .NET from the Windows Updates panel, running all Windows Updates available to me, confirmed Firewall and Windows Defender (or whatever they call it now) is up to date. No change in error. I have downloaded a copy of the Excel file to my Mac and am running it with no issues using Excel for Mac 2016. Any suggestion to solve this problem would be greatly appreciated.

Thank you.

Robert
0
Using analysis services cube in Excel. I have used MDX for some calculation but this one isn't working. Here is an example of the percentage at two levels I am trying to get.
I don't use MDX often so I'm not sure what I need to add. Assuming some code for levels/leaf.
CitrisApples.xlsx
0
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

I need a correction to the SUMPRODUCT formula in the attached Excel spreadsheet to calculate the weighted average cost at a certain unit volume with tier pricing.
SumProd.xlsx
0
We have a column of data all in Column A. In column B I would like it to pull out the First and Last Name. This is always right after the time - which ends in either "am" or "pm". Is there a way to pull out the 2 words after the "am" or "pm"? This would always be the name we need.
0
In Excel VBA is there a way to know how many columns would a text string span if it is wider than the column it is in?

eg

Wordwrap is turned of.
If there is text in D5 and it is wider than column 5 which column would it end in?

This is because I have overlapping texts and I have to move the other text over so that it does not overlap.
0
I am trying to build a macro that counts the number of cross references in a Word document by cross ref type:

wdRefTypeBookmark
wdRefTypeEndnote
wdRefTypeFootnote
wdRefTypeHeading
wdRefTypeNumberedltem

as well as by the other types without wd enumeration (‘Figure’, ‘Equation’, and ‘Table’).

I came up with ‘Count_Crossrefs_by_Label’ (in the attached) for counting the ‘Figure’, ‘Equation’, and ‘Table’ types because of their standardized text, but am at a loss as to how to count the others.  The ‘GetCrossReferenceItems’ method seems to count all of the entities, not whether they have cross references or not.

It seems that if I can get the above wd types into a loop that also loop through the wdFieldRef types, through would give me a count by cross ref type. But I’m not having any success in doing this. Does someone know how to make this work?
Count-Cross-Reference-Test.docm
0
I'm using conditional formatting to change cell color, but I also want to change the sheet tab to the same color as the Cell with the conditional format as well any ideas?

thank you
0
Hello Experts,

I am a Windows Support Engineer (Not a Developer) and I have a given a task in my office to find out Subnet, Location & Purpose of Subnet details for an IP Address. The details of Subnet, Site & Purpose of Subnet are available in an excel sheet(I have extracted the data from Infoblox). I need a tool (script) which will take an IP Address(either single or multiple) as an input and retrieve that information by looking up in that excel.

Purpose 1: Initially the tool will be used within our team to tracing ip address in our office instead of going through number of files/paths.

Purpose 2: After sometime if everything goes fine, then this will be hosted in a Website(for Wider usage) where Mac Address, Vlan needs to be amended into it

Can anyone suggest me ideas & tools on the same?

Note: For easy convenience I have copied all 3 sample data into one as shown in the image.

It contains Sample Data, Input Data and Output Data.
0