Microsoft Office

64K

Solutions

41K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

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

Sign up to Post

In the attached spreadsheet I am trying to complete the following tasks and need assistance.

1. I understand the VERY basics of macros and need to add a macro sheet 'assessment' and 'trend assessment' to reset the drop downs once an individual has completed the answering the questions.
2. I tried adding an error alert in the drop down so that if someone tried entering another number it wouldn't let them but then when I tried to tell it to apply to the other cells so that I wouldn't have to do it one at a time it didn't work, so not sure what I was doing wrong.
3. When I protected the entire sheet except for the drop down cells, need to ensure I am still able to run the macro.

If you can also let me know how you completed the macro for the drop down that would be helpful so that I can update when necessary.
Yosemite-Assoc-Exit-Optimization-MM.xlsm
0
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

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

Should I use distribution list instead of Office 365 group when we only want members of a group to receive emails sent to this group by anyone else within the organization?  The group is private because we don't allow other users to add themselves to this group.  This group has been created for the users who want to send an email to the administration people.  We usually create a distribution list for that purpose.  But Microsoft suggests to use Office 365 group instead distribution list.

This is the statement published on the Microsoft web site.

Distribution list

It's better to create an Office 365 group than a distribution list. The only reason to create a DL now is if your organization has Groups disabled.

But it looks like if the group is private users who are not members of the group are not allowed to send an email to this group.

Thanks

Edit: I just found out that it's a normal behavior

Office 365 Group member who sends email to the group will not receive that email in their individual inbox
An Office 365 Group member who sends email to the group will no longer receive that email in their individual inbox.Previously, when a member of an Office 365 group that was subscribed to the group - i.e., they receive emails to the group in their individual inbox as well as in the group's shared inbox - sent an email to that group, the member would receive a copy of their email in their individual inbox.
More info
Feature ID: 76175
0
I am trying to schedule  a conference room but scheduler  had grey slashes for all rooms and said the information is not available

Conf room is synced through AAD sync to office 365
0
Hi,

I am currently using the following code to display a form with rounded windows. Issue is how to make it run in 64-bit.

Thanks
Option Explicit

' Region API functins Requires Windows NT 3.1 or later; Requires Windows 95 or later

Private Declare Function CreateEllipticRgn Lib "gdi32" (ByVal X1 As Long, ByVal Y1 As Long, ByVal X2 As Long, ByVal Y2 As Long) As Long
Private Declare Function CreatePolygonRgn Lib "gdi32" (lpPoint As POINTAPI, ByVal nCount As Long, ByVal nPolyFillMode As Long) As Long
Private Declare Function CreateRoundRectRgn Lib "gdi32" (ByVal X1 As Long, ByVal Y1 As Long, ByVal X2 As Long, ByVal Y2 As Long, ByVal X3 As Long, ByVal Y3 As Long) As Long
Private Declare Function GetWindowRect Lib "user32" (ByVal hWnd As Long, lpRect As RECT) As Long
Private Declare Function SetWindowRgn Lib "user32" (ByVal hWnd As Long, ByVal hRgn As Long, ByVal bRedraw As Long) As Long
Private Declare Function DeleteObject Lib "gdi32" (ByVal hObject As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Type POINTAPI
    X As Long
    Y As Long
End Type

Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Dim FrmWndh  As Long
Sub SetWindowShape(ByVal hWnd As Long)

    Dim lpRect As RECT
    Dim lFrmWidth As Long, lFrmHeight As Long
    Dim hRgn As Long
    GetWindowRect hWnd, lpRect
    lFrmWidth = lpRect.Right -

Open in new window

0
Hello,

In the attachment, I would like to combine column A and column D in column E but keep the entry in column D if field in column D is not blank.  

Please advise how to do that.

Many thanks.
mytestdocument.xlsx
0
The attached spreadsheet shows a pricing lines for customers, but only the first line has all the pertinent data.  Trying to find an easy way to capture the first line and eliminate the others.  I think the example is self explanatory.  Thanks for any thoughts here..
EE-Question.xlsx
0
VLOOKUP stops returning correct value past row 941?

there are 471 filled rows, and my formula was looking at A2:E800, and it seemed to work fine.
I needed to increase that range to E10,000 and I noticed that returned values didn't match once I modified formula.
I was able to work it out that it errors at row 942 and on.

Spreadsheet with table array has formulas in column A (A2:A10000), but current data is filled up to row 471.
Not sure why it works only up to row 941?
File attached...Sheet4, cell D2 is the one I've been trying to change.

Thanks!
takeoffs-1.xlsx
0
Hi,
I just want to display an SQL Server table content onto an Excel sheet. I use standard Excel menu. Data -> From SQL Server -> Do the connection with SQL Server credentials -> Click the table.
All data are loaded successfully. But I have a problem.
Numeric data type (numeric(19,2) was rounded up in Excel.
For example: 3005581000009879 in SQL table will be displayed as 3005581000009880 in Excel. This happen to all the the records.
I tried to change the cell format to text (in Excel) without success.
How to fix this?
0
Office 365: When I choose to create a new document, how can I get it to default to Personal, not Featured templates?

I have Office 365. I have 18 years of Personal (or Custom) templates. I can now choose them when I want to create a new document.
My irritation is that when I create a new document, Word, Excel, or PowerPoint default to Featured and I need to click the Personal link to get to my templates.
In the last 10 years, I have created thousands of documents and have probably searched for Microsoft-supplied templates less than 5 times.
Template-Collection-Option.PNG
0
adjust  find in N5 to select data ti be show in  OUT-3N  sheet
2345656788_fix_to_n5.xlsm
0
The Ultimate Tool Kit for Technolgy Solution Provi
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Hello,
I have code which saves the excel sheet to .csv.
It works well with 1 sheet but how do  address multiple sheets and save the seperate csv for every sheet.
Please find the code for 1  sheet below:

  Private Sub SurroundingSub()
        Dim officeType As Type = Type.GetTypeFromProgID("Excel.Application")

        If officeType Is Nothing Then
        Else
            Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
            app.DisplayAlerts = False
            Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open("C:\test.xlsx")
            Dim newFileName As String = System.IO.Directory.GetCurrentDirectory() & "\DataMigration.csv"
            excelWorkbook.SaveAs(newFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
            excelWorkbook.Close()
            app.Quit()
        End If
    End Sub

Open in new window

0
I'm using an older version of Microsoft One-Note (from Office 2007) - mainly to copy parts of my screen to file. Has worked great. Activated by pressing Windows key + S. After upgrading from Win7 Pro to Win 10 on my PC, this hot-key doesn't work anymore. Is it possible to get this (or another key combination) to activate the screen capture mode?

-Olaf-
0
I am looking for a SW tool like Excel or AirTable that can handle records/lines that are subordinate to another record/line, Parent/Child.  Or even a database tool, if an average End-User can handle it by themselves.
For Example, like this in Excel:
  1    Car      Red      Mustangs        Convertible
  2                2015    11000 miles    $24,500      
  3                2011     37,500 miles  $21,750
  4                2013     22,000 miles  $19,000
  5   Truck   White   Tacomas         Ext Cab      
  6                2017    12,500 miles   $18,750
  7                2014    43,000 miles   $21,950
So in this example I have 3 Cars and 2 Trucks ... the issue is that I want to be able to SORT on Car or Truck and have those sub-items (like "2015  11000 miles  $24,500" stay together under each parent item.  And the option to show or hide those sub-items would be awesome, so I can print a Report either way !!!
Solution can be on Mac, Windows, Online, any method.
Thanks all in advance for any help!
- B
0
I want to use these three queries:

First query:

SELECT 
    AMICOS.PNRREG.PN, Max(AMICOS.HISTORYSTOCKFLOAT.HISTORYDATE) AS lastissue 
FROM 
    AMICOS.HISTORYSTOCKFLOAT 
INNER JOIN 
    AMICOS.PNRREG ON AMICOS.HISTORYSTOCKFLOAT.PARTID = AMICOS.PNRREG.PARTID 
WHERE 
    (((AMICOS.HISTORYSTOCKFLOAT.ACTION)='ISSUE') AND    
    ((AMICOS.HISTORYSTOCKFLOAT.HISTORYDATE)<to_date('31032019','DDMMYYYY')+1)) 
GROUP BY 
    AMICOS.PNRREG.PN;

Open in new window


Second query:

SELECT 
    AMICOS.PNRREG.PN, Max(AMICOS.HISTORY.HISTORYDATE) AS lastissuehistory
FROM
    AMICOS.HISTORY 
INNER JOIN 
    AMICOS.PNRREG ON AMICOS.HISTORY.PARTID = AMICOS.PNRREG.PARTID
WHERE 
   (((AMICOS.HISTORY.HISTORYACTION)='ISSUE') AND 
    ((AMICOS.HISTORY.HISTORYDATE)<to_date('31032019','DDMMYYYY')+1))
GROUP BY 
    AMICOS.PNRREG.PN;

Open in new window

Third query:

SELECT 
    AMICOS.PNRREG.PN, Max(AMICOS.HISTORYSTOCKFLOAT.HISTORYDATE) AS lastpurchase
FROM
    AMICOS.HISTORYSTOCKFLOAT 
INNER JOIN 
    AMICOS.PNRREG ON AMICOS.HISTORYSTOCKFLOAT.PARTID = AMICOS.PNRREG.PARTID
WHERE 
    (((AMICOS.HISTORYSTOCKFLOAT.HISTORYDATE)<to_date('31032019','DDMMYYYY')+1))
GROUP BY 
    AMICOS.PNRREG.PN, AMICOS.HISTORYSTOCKFLOAT.ACTION
HAVING 
    (((AMICOS.HISTORYSTOCKFLOAT.ACTION)='PURCHASE'));

Open in new window


And this last query which put those together:

SELECT [6301_2018_03].PN, [6301_2018_03].ABC_CATEGORY, [6301_2018_03].PRIMUTILISATION, [6301_2018_03].DESCRIPTION, [6301_2018_03].ACCOUNTNO, [6301_2018_03].HISTORYDATE, [6301_2018_03].STOCK_FLOAT, [6301_2018_03].STOCK_PRICE, [6301_2018_03].STOCKVALUE, lastpurchase.lastpurchase, lastissue.lastissue, lastissuehistory.lastissuehistory
FROM ((6301_2018_03 LEFT JOIN lastpurchase ON [6301_2018_03].PN = lastpurchase.PN) LEFT JOIN lastissue ON [6301_2018_03].PN = lastissue.PN) LEFT JOIN lastissuehistory ON [6301_2018_03].PN = lastissuehistory.PN;

Open in new window


The querie from  from 6301_2018_03 (Which is generated to MS Excel file before joining the other three queries are:

select p.pn, c.abc_category, i.primutilisation, p.description, hsf.accountno,  hsf.historydate, hsf.stock_float, hsf.stock_price, hsf.stock_float*hsf.stock_price stockvalue from amicos.pnrreg p, amicos.irc i, amicos.ircabccat c, amicos.historystockfloat hsf, (select max(stockfloatid) stockfloatid from amicos.historystockfloat hsf  where historydate = (select max(hsf1.historydate) from amicos.historystockfloat hsf1  where hsf1.historydate<to_date('31032019','DDMMYYYY')+1 and hsf1.partid=hsf.partid) group by partid) x  where x.stockfloatid = hsf.stockfloatid and p.partid = hsf.partid and p.irc = i.irc and p.irc = c.irc and hsf.accountno like '6301%' and hsf.stock_float <> 0

Open in new window


These codes are used on MS Access Database which works and now i will make the same thing on Microsoft SQL Server Report Builder but i don't have much experience in SQL Server Report Builder.

I have tried UNION, JOIN to put those Queries together to get one result from this last queries which is dependending of those three other queries.

How can I achieve this?

Any help will be much appreciated

Regards

Erneeraq
0
I have an array (100 x 200) that is filled with a variable amount of data based on a query.  I am copying the data from the 100 x 200 array, and pasting the values and source formatting in a separate sheet.  For example, there may be text in range of 10 x 50 with the rest of the cells in the 100 x 200 range being blank.  However, the cells are not actually blank.  I have macros to find the last row and column in order to select only the data, but they always select the full range of 100 x 200.  I have a macro to clear the contents of all of the blank cells in the 100 x 200 range, but it is very slow.

I need the code to find the last cell in Row 1 that has text, and then clear the contents in the next cell to the right.  I also need similar code to find the last cell in Column A and then clear the contents in the next cell down.  Clearing the contents out of these two cells would allow me to use code to only select the data in the 10 x 50 range.
0
I have a user who has outlook 2010 on premise

 his mail box is on office 365.

 whenever he exits out of his citrix desktop his email signature goes away

 when he is logged in the outlook and desktop , his signature stays

 build his outlook profile
 build his citrix profile

 didnt help

I checked his signature folder under appdata , all files are there.

 anything we can do in registry
how can I test with outllok.exe/ safe mode ?

also I don't see any issues with add ins.
0
user has created local distribution group but cant edit it

I am trying to get more info.

he uses outlook 2010 and mailbox is in office 365

In the meantime any input would be appreciated
0
Dear Experts:

I need to extract a substring  from the right, i.e. the substring that is located between the third and fourth underscore character (from the right).

That is, '6s' is to be extracted.

\\172.20.10.68\DATEN$\KLS_Allgemein\DMD\PM_1\IFU_Aquajet-Wasserstrahlreinigungspistole_90-816-58-21_REV_2_RD_2017-11_6s_A4_S60_bw.pdf
 
Could somebody please help me to extract this information using a formula.

Thank you very much in advance.

Regards, Andreas
0
Experts:

I need some assistance with creating a dynamic graph (histogram chart in Excel).   Please find attached XLS with includes a) example data b) dynamic example graph based on example data, and c) the actual data for which I need to replicate the concept of "being dynamic".  

The attached XLS includes all relevant information, so I won't repeat the whole process here... "picture is worth a thousand words".  

I thank you for your help in advance.

VR,
EEH
Dynamic-Graph.xlsx
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

When I use ActiveWorkbook.FullName for me the result is T:/SourceFiles/Students. xlsx.  However. when others run the macro ActiveWorkbook.FullName displays as //ServerMain/SourceFiles/Student.xlsx.

Why for some people does ActiveWorkbook.FullName display a relative path ?  When I go into Folder Explorer and investigate for all users of the macro the only difference I see is some have the server (ServerMain) mapped to T: and others have it mapped to V:

Note the file called Students.xlsx is found in one path only.  The path where the file is found for all users is //ServerMain/SourceFiles/Student.xlsx.  I realize some people will map there T drive to //ServerMain/SourceFiles/Student.xlsx and others may map there V drive to //ServerMain/SourceFiles/Student.xlsx.  Regarless of mapping I would expect consistency in how ActiveWorkbook.FullName displays the path ?

Expected
User 1
//ServerMain/SourceFiles/Student.xlsx
User 2
//ServerMain/SourceFiles/Student.xlsx

OR

User 1
T:/SourceFiles/Student.xlsx
User 2
V:/SourceFiles/Student.xlsx

DID NOT EXPECT
User 1
//ServerMain/SourceFiles/Student.xlsx
User 2
T:/SourceFiles/Student.xlsx
0
I am using Excel 2016 and have a large workbook with 110 sheets.  I am only interested in the data on 72 on these sheets.  We are currently formatting the sheets to look identical in the column headings and the column placement.  
Our goal is to be able to create a sheet that we can choose a name from the Owner column and have it return
 Owner
 ITM
Application
Sheet Name

We want to then copy and paste this into another workbook that we can send to the Owner so they can verify that the application is valid.  If they say it's not valid we need the sheet name so we can go and either update to indicate that it is no longer valid or delete the line.

I am attaching the file as reference.  Can anyone help me and I am willing to discuss via a phone call.

Thanks

Tiffany
9K-Tracker-7.23.18.xlsx
0
Compare 2 columns for unique value.  I have columns A  B   and they all contain the same information I think.  I would like to highlight the rows where A and B don't match.

Row     Column A      Column B
 1            3/27/18            3/27/18
 2             7/9/18               0
 3           No Match         Critical
 4           1082455            1082455
In this example rows 2 and 3 would be highlighted or the Cells that don't match would be highlighted
Additionally, if I wanted to compare 3 columns how would I Highlight the odd cell?
0
My client has a set of images of their product and they want to show them alongside their prices in the price list which is in Excel.

If the Excel workbook has a list of URLs to those images is it possible to display the image in Excel.
0
How to combine duplicate rows, if values in two columns are identical....and sum qty from third column:
Sample file attached, but here's the jist of it:
          B             D           F
      Frame    White    100
      Frame    White    200

        to show one line:
      Frame    White    300

Can this be done with formula or VBA?
Rows are not adjacent...can be spread out throughout the spreadsheet (if it matters, it's fairly small sheet...never has more than 50 rows)

Thanks!
sample.xlsx
0
Hi Experts

Hope you can help

I have a spreadsheet that extracts data from UPS and places it in a worksheet from which the data is used to code it for finance purposes (yellow section – columns AG to AK)
In column AG, I have a (long) formula that looks for certain criteria in the sheet and delivers the necessary result.

For some of these entries we have goods going to our stores and I have placed within the nested formula, to look at the first three letters of the store name in column W and return the result ‘Store’ & the first three letter of the store name.

Its fine for the SECOND entry it finds but for the first it returns N/A and I have no idea why?

I have attached the file – the sheet in question is labelled Paste UPS LITE Inv Data Here

If you could help, I would be very grateful

J
EE_Example_Formula_Issue.xlsm
0

Microsoft Office

64K

Solutions

41K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.