[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Office

66K

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

I am trying to use a concatenate function to merge multiple cells into a single string of text. e.g.

=CONCATENATE(B2,"\",C2,"\",D2,"\",E2,"\",A2)

the problem is, some of the values which show in column D and column E, (they should all represent a 3 number combination, e.g. 018, but when I use concatenate, it only pulls in the end oart, or if the combination is 000, it only brings in a single 0.

So for example

B2-C2-D2-E2-A2
server-000-999-019-file.xls

once I use the formala above, returns something like

server\000\999\19\file.xls

whereas it needs to return

server\000\999\019\file.xls

I can format the cells as text, but when I do that for any which start with 0 it loses the zero and I am back to square one. How can I preserve the number columns to preserve the 3 numbers, regardless of if they start with 0, so the concatenate pulls through all numbers, not just those that dont begin with 0, or where there are 3 0's, prevent it from only bringing through a single 0 into the string, and not all 3.
0
Become a Certified Penetration Testing Engineer
LVL 12
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Hi Experts,

I have this question after resolving the following

https://www.experts-exchange.com/questions/29127578/script-to-convert-all-characters-in-excel-sheet-to-A.html?headerLink=workspace_answered_questions

How to modify the script in order to accomplish

1- Select which columns to get obfuscated.
2- letters should be changed with a logic, A should become B, B should become C and so on...Z should become A.
3- Same to numbers, 1 should become 2, 2 should become 3 and so on, then 9 should become 0.

Thanks
0
Hi ,

 I get vba runtime 1004 Application-defined or object-defined error  while trying to insert a formula!

 .Range("BG2:BG" & Lastrow).Formula = "=IF(OR(ISNUMBER(AC2)=FALSE;AC2=0);'N/A';AX2/AC2)"  ' This line gives the error but works if I put directly into cell!
  .Range("BG2:BG" & Lastrow).NumberFormat = "0.00" ' this line works fine!

Thanks

Pete
0
I have set up an O365 group primarily for use of the shared calendar and its utility on our office SharePoint site.  I have set members of this group who are allowed to submit events to the calendar.  Nobody in the group needs to receive invitations when an event is submitted.  I know that individually each user can adjust their settings for the group, but is there a way for me as group owner (and O365 admin) to centrally set "Don't receive any group messages" for all members of the group?  To be clear, "Send all group conversations and events to members' inboxes" is unchecked.  Desired O365 settings for all members of the groupThanks!
0
In my Office 365 environment, I have a user that was "Soft-Deleted" in the Recycle Bin, date given it was soft deleted was exactly 30 days ago.

When I try to Restore it, it gives me this message:

You cannot restore this user because it is been more than 30 days since you deleted them. You can create a new user, but the data associated with the user you are trying to restore has been permanently deleted and cannot be restored.

Does this mean that is it now unrecoverable and all data has been deleted?  Or will recreating the account first be needed?  What is the pur pose of the soft deletion if it can't be restored?

Thanks Experts!
0
Hi,

Is there any way that I can guarantee that our Office 365 profile pictures are internal to our organisation only?

I have looked this up but the answers seems to be inconsistent.

I have heard that if you forward a colleagues Office 365 details via email the image may appear to the external recipient?

Any help would be appreciated, thanks.
0
I wish to extract the hours, minutes and seconds from a duration time stamp for example: 114:35:10

If such a duration is inserted into a cell, excel places a date stamp prior to it.

When I attempt to use:

Hour(string) or format(string,  “[h]:mm:ss”) the result is incorrect.
I also want to be able to extract the mm and ss values.

Can an expert please help.
0
Dear Experts:

below macro, courtesy of Martin Liss retrieves the full path of a file name. The macro works great.

I need to tweak it a little bit, especially the hardcoded ranges (it was my initial requirement) should be replaced by inputboxes, where the user can choose which column the file name is in.

So the tweaking should occur on line 14: The user should be prompted to enter a column number (A To Z) where the filenames reside. Now it is hardcoded to Column A
The next tweaking should occur on line 28: It could be any column number (A-Z) actually.
The next tweaking is to occur on line 30 and 32: this is always the immediate column to the right of the initial column chosen (input box prompt)

I hope I could make myself clear.

Thank you very much for your great and professional help in advance.

Regards, Andreas

Sub GetFullPath()
'the best one
Dim lngRow As Long
Dim lngLastRow As Long
Dim strFile As String
Dim strRoot As String
Dim strCurrPath As String

Application.ScreenUpdating = False



With ThisWorkbook.ActiveSheet
    lngLastRow = .Range("A1048576").End(xlUp).Row
    strRoot = "G:\KLS_Allgemein\DMD\"
    
    ' Collect the subdirectories of the root
    strCurrPath = Dir(strRoot, vbDirectory)
    Do Until strCurrPath = vbNullString
        If Left(strCurrPath, 1) <> "." And _
            (GetAttr(strRoot & strCurrPath) And vbDirectory) = vbDirectory Then
            mcolDirectories.Add strCurrPath
        End If
        strCurrPath = 

Open in new window

0
My question is around determining the exact difference (in months) between two dates, then (with formulas) generating the correct payment for each month (fixed amount) until the last month where an allocation needs to be made based on the partial month.  I believe the attached spreadsheet shows what the question is about.  Thanks for your help.  - Tom
EE-QUESTION.xlsx
0
I have Excel 2010 but am trying to help a coworker who has been using Excel 2016  for 5 months (Windows 10 pro computer.)

Today, she suddenly complained that her mouse is "jumpy". I sat at her computer and immediately noticed 3 things.

1) When i clicked on a cell with a formula, it often (but not always) acts as if F2 was pressed.  For instance
EXAMPLE

2) The "Excel 2016 Current Selection Temporary Border(see cell B1 in example)" is green instead of black.

3) Something else was very subtle but weird. It happened so quickly that I had to avoid blinking to notice it.

I clicked on a1 then z26  then a1 then z26 repeatedly 10 times.  

The green border does not instantly switch. Instead it "swooshes" through intermediate cells. For instance from A1 to H8 then Q17 then Z26.  

Clearly this is Excel's 2016 intentional behavior. Similar swooshes are useful in other Windows 7 & 10 applications, but in Excel this 'swooshing" causes the mouse to be "Jumpy" which my coworker dislikes.

How can we disable these functions?

We applied windows updates and rebooted the computer then changed to the" Windows 10 Theme", but none of that worked.
0
Exploring SQL Server 2016: Fundamentals
LVL 12
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hello Experts,

is there any way we can create multiple tabs in excel and should take the input as name ?

say I have an excel and I want to create 3 tabs or more based on my main sheet1 column.
0
Hi

I am using the following code to automatically save a certain range as a pdf file for each sheet.
I am trying to find a way to automatically email each range as a pdf attachment or in the body of the mail

Sub DaysSixToTen_With_Headers()

    Dim oSheet As String
    Dim oDays As String
    oDays = "6 to 10"
 
    For i = 1 To Sheets.Count
    
        Sheets(i).Select
        oSheet = Sheets(i).Name
        
        Rows("5:9").EntireRow.Hidden = True
        
        Range("A1:G14").Select
             
        Call oSave_Selection_PDF(oSheet & " " & oDays)
        
        Rows("5:9").EntireRow.Hidden = False
    
    Next i
 
End Sub

Sub oSave_Selection_PDF(ByVal sFileName As String)

    'Save Active Sheet(s) as PDF
    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet
    'get active workbook folder, if saved
    strPath = wbA.Path
    If strPath = "" Then
      strPath = Application.DefaultFilePath
    End If
    strPath = strPath & "\"
    strPathFile = strPath & sFileName & ".pdf"
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPathFile
    
End Sub

Open in new window

0
With voice calls and conference calling with Microsoft Teams - is there anything
different about the IP pathing or about the protocols that are used? With Skype
the RTP gots to an Anycast address and then into the MS network. Is all that
the same with teams? Any other differences for voice? For video?
0
Outlook Web Client (OWA) - is it possible to restrict attaching documents as a file in OWA by policy or through OneDrive policies in Office 365?

Thanks Experts!
0
What is needed in order to use Skype For Business?
Basically, we'd like to use Skype For Business to share screens and chat between two persons in our company but our company is NOT using Office 365.
So we downloaded and installed Skype For Business. On the Sign In page, we see the message "Use the sign-in address for your organization - not a Skype Name or Microsoft account".
Please see attached screen shot.

What is the "sign-in address for your origanization"? How can we get it? Does this mean that we have to open an O365 account and be a subscriber?

Thanks
Skype-For-Business.png
0
We have dozens of Excel workbooks that need to be shared with an external group for litigation purposes.  We are trying to find a way to make the Excel files uneditable, or Read Only.  We do not want them printing altered versions or saving another version they could edit.  

I have tried using the advanced save as read only option, but that still allows the user to write in and edit cells and then just save as another name.  

I know one way to lock down a sheet, by formatting all the cells as locked and then restricting the document in the view tab, but this requires us to go in to every sheet and lock all cells and lock each sheet separately.  There are about 20 sheets in each of these workbooks, so repeating this process x the dozens of workbooks would be far too time consuming.  Is there a better way?

We are using Excel Office 365 ProPlus.

Edit:  Why we can't just PDF:  
"when these documents were created, none but the summary sheets were ever meant to be printed, and so the print area is all messed up in all of them, so you get tons of blank pages or pages with the data cut up in very awkward ways.  Trying to go back and fix all of the print areas now would be even more time consuming."
0
I would like to goto the last character in a cell when a user clicks on the cell.I know we can use  Application.SendKeys "{F2}", True is there an alternative
0
I have excel files in a single folder.  each of those excel files has multiple sheets with data, the data are in form of simple data and "Excel Tables" like shown in the screenshot.

asfas.jpg
I need to marge all of the Tables only into a single Table in a master Sheet.  

So, I need help with a code that loops through all workbooks in a folder and searches for Tables and then combines all of the table into one giant table and puts this giant table into a new workbook. all of the tables has the identical number of columns and column headers.  the column headers should be duplicated in the merged Table.

I have also attached the two example excel files that have tables in them.

I am doing this manually. I got some codes from searching, but they are all combining everything and I could not find something particular that combines only Tables  i would greatly appreciate any help to automate this using VBA.
Book1.xlsx
Book2.xlsx
0
Need a Microsoft Access VBA function that returns number of Fridays in any given month of any given year.
Would like the answer based on the working solutions Tikas A Planck or Aladin that provided at this link:

https://www.mrexcel.com/forum/excel-questions/10553-how-many-fridays-any-given-month.html

I know it's just a matter of converting Excel VBA to a function like so:  but I don't understand the syntax of the formula for Excel..

Public Function NumberOfFridaysInDate(dNow as Date) as Long


End Function

Open in new window

0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

What formula do I need to find the Key in column B that has specific values in column A.

Example:
ColumnA      ColumnB
200.              100
300.              20
400.              30
50.                20
600.              20

Find value in column B that has 600, 50 and 300 in column A. Answer = 20.
0
In the Office 365 Exchange admin portal I am forwarding several users' mail to an outside address, which are mail contacts? Is it possible to also set up Out Of Office (Auto Reply) Messages for the users?  It looks like if I forward without delivering to the mailbox as well, the auto reply is not sent.  Thanks.
1
What VBA do I need that will look at two strings and show a message box stating which is larger?

Example:
1) String1 = "1-668!a"       String2 = "1-668!b"
2) String1 = "288$4$22"  String2 = "287$4$22"
3) String1 = "100(4)(57)" String2 = "100(4)(577)"

1) String 2 is larger
2) String 1 is larger
3) String 2 is larger

By larger I mean which will be listed last if sorted in  ascending order.
1
Hi guys

I've got an Excel sheet with around 12 columns and around 34 rows of content. I want to be able to do a mail merge, so that the content of 4 out of 12 of these columns and all of the rows can be sent to our ticketing system to create a separate ticket for each row.  

Is there a way to do this?

Thanks for helping
Yash
0
I need to be able to pull everything between the second and third dash in a list of items.

0-AP-LP-6001-CB-MB  >  result = LP
0-AP-MCC-6000           >  result = MCC
0-AP-XFMR-6007         >  result = XFMR
0-FA-5001A-B-6           >  result = 5001A
0
I once uploaded an MSWord file in EE & one expert commented he could
see my name, the company I worked for & even the previous person who
edited the file was identified.

Was told in MS Office there's this metadata.  Where/how can I check for
this metadata & how can we securely remove it?   I'm using MS Ofc
2000 & 2016

Does PDF files also have such metadata?
0

Microsoft Office

66K

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.