Microsoft Applications

44K

Solutions

38K

Contributors

Microsoft applications include a variety of software programs, including development and digital authoring programs (Expression and Media Center), educational programs, Internet software, including Essentials, Skype and the Live family, anti-virus, productivity applications and suites like Office, Excel, Word, Outlook, Access and PowerPoint, video games and server applications such as Exchange, SharePoint, IIS and Virtual Server.

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

Sign up to Post

Experts,

I have a group of values on a spreadsheet "Sheet 3"  Column A  Rows 2 =15 that I have assigned to a name field called 'East'.  Is there a way to check column "A" on Sheet 1 equal to one of the values in my named field and assign "EAST" to column "B' on the respective row.

Microsoft Excel 2016
Named_Feild.xlsx
0
Bootstrap 4: Exploring New Features
LVL 19
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

As you can see from my screen shot, I’m using the following sub routine to link various worksheets from ExcelFile.xlsx into my database. As shown below, I’ve linked only four worksheets. However, my actually worksheet has many more tabs that I need to link. Also, the tab names are not constant. In other words, I could have three worksheets named like: 1111, 2222, 3333. Then at a later time for the exact same file, I could have four worksheets with multiple other names like: AAAA, BBBB, CCCC, DDDD.

Right now, it’s a manual process for me to update this code each time a sheet name changes or is removed. I’d like to modify this code to be more dynamic by accomplishing the following:

1.      First delete all database table objects that have a string of 4 characters in their name (e.g. 1111, 2233, AABC)
2.      Link all worksheet tabs that have a string of 4 characters in their name from ExelFile.xlsx

I'm using MS office 2016. Any Expert suggestion on this is greatly appreciated! =)

Private Sub LinkSpreadSheets()

Dim Fpath As String, _
  XLname1 As String, _
  tb1 As String, _
  tb2 As String, _
  tb3 As String, _
  tb4 As String

Fpath = Environ("USERPROFILE") & "\Documents\databases"

XLname1 = "\ExcelFile.xlsx"
                   
tb1 = "1001"
tb2 = "2001"
tb3 = "3001"
tb4 = "F008"

With DoCmd

    .DeleteObject acTable, "1001"
    .DeleteObject acTable, "2001"
    .DeleteObject acTable, "3001"
    .DeleteObject acTable, "F008"

    .TransferSpreadsheet acLink, , tb1,

Open in new window

0
Hell All,

i have a feture to be able to send an Email from Acc 2919
Customers want to be able to select an Account first (from which Account to send).

Is there a way to get the Account Names from Outlook?
thanks
0
I have a Windows desktop application that I need to be used by several people from different locations around the country.
Please advise what I can do about it.
Thanks

Luis R
Mexico
0
On click I am copying and pasting data to different pages.  On one particular page ("OrdPrep") I am capturing the OrderNumber and the Order Description. No problem.

But on the OrderPrep page, I want to paste across columns. for example:
               On first Click                            On Second Click                   On Third Click
           Col1                    Col2                 Col3                 Col4                   Col5                 Col6      
       
1.         OrdNum          Order              OrdNum         Order               OrdNum           Order
2.         MJB0001            Stuff             MJB0002        Something        MJB0003           Nothing
3.                                    Stuff1                                                                                           NADA
4.                                    Stuff2                                                                                            Nope
The data comes from sheet1 and is selected by user.. I have tried several different methods but cant get what I want..
I tried to format this message by looking at the preview. If this does not make sense, let me know and I will forward worksheets.
Thank You,
Norm
OSsendE-X.xlsm
0
Trouble adding comma to bar graph data using Windows Chart Class

I am using the library:
System.Windows.Forms.DataVisualization.Charting

and found I could format (with comma) for values > 1,000

with this line of code:
ca.AxisY.LabelStyle.Format = ("##,#");

But, I can't figure out how to get the data to display also with the comma.

Best I get is the following:
Missing Comma on Data
Here is the Microsoft reference document:
https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.datavisualization.charting.chart?view=netframework-4.0
0
Hello, so I have this Excel table (facsimile):

Initial table
I've been asked to make it so that the table can be printed using the second column as a way to split it into more tables. So, the data should be grouped depending on the second column value.

So this table should be printed on 3 pages, and we'd get these 3 tables, one on each page:

3 tables, grouped by the value in the second column
Does Excel have some quick way to do this which I don't know of? Do you have any suggestions?

Thanks.
0
Hello experts,
I have the following procedure:
Sub Add_Values_Multiple_Columns()
    Dim wsActive    As Worksheet
    Dim wsOutput    As Worksheet
    Dim strCol      As Variant
    Dim strColList  As String
    Dim lngLastRow  As Long
    Dim lngLastCol  As Long
    Dim shName      As String
    
    Application.ScreenUpdating = False
    
    Set wsActive = ActiveSheet
    
    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    For Each strCol In Split(strColList, ";")
        If Not ValidCellReference(strCol) Then
            MsgBox strCol & " is not a valid column letter.", vbExclamation
            Exit Sub
        End If
    Next strCol
    
    Set wsOutput = Worksheets.Add(after:=Worksheets(Worksheets.Count))
        
    For Each strCol In Split(strColList, ";")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
        If wsOutput.Range("A1") = "" Then
            lngLastCol = 1
        Else
            lngLastCol = wsOutput.Cells(1, Columns.Count).End(xlToLeft).Column + 1
        End If
        lngLastRow = wsActive.Range(strCol & Rows.Count).End(xlUp).Row
        wsActive.Range(strCol & "1:" & strCol & lngLastRow).Copy wsOutput.Cells(1, lngLastCol)
    Next
    
    shName = 

Open in new window

0
i am trying to save publisher file , when i save it says disk you trying to save is write protected

i am not having any issues with any other word or excel 365
0
Hello experts,
I have the following procedure which allows me to copy reported column from one sheet to another:
Sub Add_Values_Multiple_Columns()
    Dim wsActive    As Worksheet
    Dim wsOutput    As Worksheet
    Dim strCol      As Variant
    Dim strColList  As String
    Dim lngLastRow  As Long
    Dim lngLastCol  As Long
    Dim shName      As String
    
    Application.ScreenUpdating = False
    
    Set wsActive = ActiveSheet
    
    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    For Each strCol In Split(strColList, ";")
        If Not ValidCellReference(strCol) Then
            MsgBox strCol & " is not a valid column letter.", vbExclamation
            Exit Sub
        End If
    Next strCol
    
    Set wsOutput = Worksheets.Add(after:=Worksheets(Worksheets.Count))
        
    For Each strCol In Split(strColList, ";")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
        If wsOutput.Range("A1") = "" Then
            lngLastCol = 1
        Else
            lngLastCol = wsOutput.Cells(1, Columns.Count).End(xlToLeft).Column + 1
        End If
        lngLastRow = wsActive.Range(strCol & Rows.Count).End(xlUp).Row
        wsActive.Range(strCol & "1:" & strCol & 

Open in new window

0
Acronis in Gartner 2019 MQ for datacenter backup
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Hello Experts,
I have the following procedure which allows me to loop on multiple columns and add default values.
Sub Add_Values_Multiple_Columns()

    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim strToReplaceWith As String
    
    
    strToReplaceWith = InputBox("Please input the Value which should replace the values of the Reported Columns.")
    
    If strToReplaceWith = "" Then
        MsgBox "You didn't input any value.", vbExclamation
        Exit Sub
    End If
    

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    For Each strCol In Split(strColList, ";")
        If Not ValidCellReference(strCol) Then
            MsgBox strCol & " is not a valid column letter.", vbExclamation
            Exit Sub
        Else
            lngRow = Range(strCol & Rows.Count).End(xlUp).Row
            If lngRow > lngLastRow Then lngLastRow = lngRow
        End If
    Next strCol
    
    For Each strCol In Split(strColList, ";")
        If lngLastRow > 1 Then Range(strCol & "2:" & strCol & lngLastRow).Value = strToReplaceWith
    Next

    Exit Sub

Error_Routine:
    MsgBox Err.Description, vbExclamation, "Something went wrong!"
    
End Sub

Open in new window


I would like to take as a reference the loop on multiple columns part in order to cover the following requirement:
1.      Display inputbox: "Report columns that you want to copy at Output sheet: example: C;E;D
2.      Generate Ouput sheet with the the following name: Output & format(now,"yyyymmddmmss")
3.      Copy the columns reported in first inputbox in Output sheet.
I attached dummy file with Activesheet and Output sheet.
If you have questions, please contact me.
Dummy-file-copy-multiple-columns.xlsx
0
Microsoft Teams
Dashboards
Graph widget

We are using Microsoft Teams to track opportunities.  We have a field for Probability and a field for Estimated Budget.  I need to create a query (and chart) that calculates Probability * Estimated Budget but I cannot find a multiplication function in queries.

Thanks
0
hi i want to copy all worksheets data into single master sheet excluding 1st worksheet
i am using the following vba code
Sub ConsolidateData()
    Dim wrk As Workbook 'Workbook object - Always good to work with object variables
    Dim sht As Worksheet 'Object for handling worksheets in loop
    Dim trg As Worksheet 'Master Worksheet
    Dim rng As Range 'Range object
    Dim colCount As Integer 'Column count in tables in the worksheets
     
    Set wrk = ActiveWorkbook 'Working in active workbook
     
    For Each sht In wrk.Worksheets
        If sht.Name = "MasterSheet" Then
            MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
            "Please remove or rename this worksheet since 'Master' would be" & _
            "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
            Exit Sub
        End If
    Next sht
     
     'We don't want screen updating
    Application.ScreenUpdating = False
     
     'Add new worksheet as the last worksheet
    Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
     'Rename the new worksheet
    trg.Name = "Mastersheet2"
     'Get column headers from the first worksheet
     'Column count first
    Set sht = wrk.Worksheets(2)
    colCount = sht.Cells(1, 255).End(xlToLeft).Column
     'Now retrieve headers, no copy&paste needed
    With trg.Cells(1, 1).Resize(1, colCount)
        .Value = sht.Cells(1, 1).Resize(1, …
0
I have an excel table with massive data (about 60000 rows) where i need to update countif and sumif formulas which i have recorded thru a macro. its working but taking around 15-30 minutes to get updated any solution / advice will be of great help
us-500.xlsm
1
Hello experts,
I am looking for a procedure to check extra spaces in used range.
The idea is to:
Loop used range as of row 2 on activesheet and highlight values that have extra space at the end in red and in yellow at the beginning.
I can not provide dummy file as I am reporting the question from my cell.
If necessary I will do it later.
Thank you for your help.
1
I have this heat map I created in Excel.  On the data tab I have the cost and on the table tab I do a vlookup that matches up the cost to each state.

The problem I have is the paint macro code does not like the vlookup values.  So when I go to the Map tab and click on "UPDATE" it errors out.  Now if I was to just put regular numbers it works great in the table tab.

Once it works I noticed that the textboxes for each state turns to a color and I wanted each textbox to have no fill.  I recorded a macro for it but it still comes up with some sort of color.

See attachment.
EE-Heat-Map---2019.xlsm
0
Hello experts,

I have the procedure reported at: https://www.experts-exchange.com/questions/29155959/Excel-VBA-Add-specific-value-on-multiple-columns.html

Sub Add_Specific_String_Multiple_Columns()

    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim strToReplaceWith As String
    
    
    strToReplaceWith = InputBox("Please input the Value which should replace the values of the Reported Columns.")
    
    If strToReplaceWith = "" Then
        MsgBox "You didn't input any value.", vbExclamation
        Exit Sub
    End If
    

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    For Each strCol In Split(strColList, ";")
        If Not ValidCellReference(strCol) Then
            MsgBox strCol & " is not a valid column letter.", vbExclamation
            Exit Sub
        End If
    Next strCol
    
    For Each strCol In Split(strColList, ";")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
        Range(strCol & "2:" & strCol & lngLastRow).Value = strToReplaceWith
    Next
End Sub

Function 

Open in new window

1
Hello experts,
I have the following file attached.
I need to set up a procedure in order to remove lines that have empty values in column F.
The idea is the following:
1-Identify last used range related to column F
2-Remove the various lines as of last used range related to column F + 1 (to identify the first empty value of column F) till the end of sheet.
I attached dummy file with expected result in Result sheet.

Procedure should be applied in the same sheet.

If you have questions, please contact me.
Thank you for your help.
0
I have an old network diagram that has a .DSD extension.  Anyone know of a reliable program I can use to open it?
0
11/26 Forrester Webinar: Savings for Enterprise
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Hello experts,
I have the following procedure which allow me to add string on multiple columns at the beginning or at the end.
I would like to add the following requirement:
Instead of adding a single string I would like to add a numeric listing (1,2,3 till the last used range)
Example: I have:
Range A2: toto
Range A3: titi
I launch the procedure in column A with flag at the end I expect to have:
Range A2: toto1
Range A3: titi2
Note: procedure should be able to add listing on multiple columns with beginning and ending flags.
If you have questions, please contact me.
Thank you.
Sub Add_Specific_String_Multiple_Columns()

    Dim sRng As String
    Dim temp_rng As String
    Dim strSpecificChar As Variant    'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String

    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")

    If intWhich_temp = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    If IsNumeric(intWhich_temp) Then intWhich = intWhich_temp

    Select Case intWhich
    Case 1, 2
    Case Else
        MsgBox "Please enter '1' or '2'"
        Exit Sub
    End Select

    strColList = InputBox("Please report column letter(s) following by ; in which 

Open in new window

0
Hello experts,
I am trying to check if a sheet is empty.
I don't understand why I got an syntax error message related to MsgBox.
Thank you for your help.

Sub Display_Used_Range()


    Dim LRow As Long, LCol As Long

    Set oWS = ActiveSheet
     'Check if sheet is empty
     With oWS
        If Application.WorksheetFunction.CountA(oWS.UsedRange) = 0 And oWS.Shapes.Count = 0 Then
        MsgBox (oWS & " is empty. Unable to proceed", vbCritical)
        Exit Sub
        End If
    End With
   
    lRw = oWS.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    Col = oWS.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column
    Set rRng = oWS.Range("A1", oWS.Cells(lRw, Col))

    MsgBox "Last Used Column Letter is " & Col & vbNewLine & "Last Used Row Number is " & lRw & vbNewLine & "Full Used Range is " & rRng.Address

Open in new window

0
Hello experts,

I have the following procedure which allows me to add string on files:

EE  question: https://www.experts-exchange.com/questions/29155167/Excel-VBA-add-string-on-files.html

Sub Add_String_On_Files()
' Local variables
    Dim BaseFolder As String, NextFile As String, FileExt As String
    Dim I As Integer
    Dim StrToAdd As String
    Dim MsgTxt As String
    
    MsgTxt = "Before running this procedure, make sure to report following information in range A1:"
    MsgTxt = MsgTxt & vbNewLine & " 1-Path directory in which you want are located files to rename"

    Ans = MsgBox(MsgTxt & vbNewLine & vbNewLine & _
                "Do you want to continue?", vbQuestion + vbYesNo, "Confirm Please!")
    If Ans = vbNo Then Exit Sub
    
    On Error GoTo Error_Routine
    ' Stop if no base folder provided
    If ActiveSheet.Range("A1").Value = "" Then
        MsgBox "A1 doesn't contain a directory string.", vbExclamation
        Exit Sub
    End If

    ' Stop if base folder provided does not exist
    BaseFolder = ActiveSheet.Range("A1").Value
    If Right(BaseFolder, 1) <> Application.PathSeparator Then BaseFolder = BaseFolder & Application.PathSeparator

    If Len(Dir(BaseFolder, vbDirectory)) = 0 Then
        MsgBox "Directory in A1 does not exist.", vbExclamation
        Exit Sub
    End If

    ' Get current date time
        
    StrToAdd = InputBox("Enter the string that you want to add at the end of your files.")
    
    If StrToAdd

Open in new window

0
Hello experts,
I would like to take as a reference file attached in order to set up a procedure with the following requirements:
1-Inputbox: "Enter last range in which you want to apply the procedure" example V1000"
2-Inputbox "Enter 1 for 1 random numeric, 2 for random letters, 3 for random letters & numeric"
3-Create sheets  based dummy sheets, apply random formulas accordingly and rename it:
-"Numeric_" & Format(Now, "YYYYMMDD_HHMMSS")
-"Letters_" & Format(Now, "YYYYMMDD_HHMMSS")
-"NL_" & Format(Now, "YYYYMMDD_HHMMSS")

If you have questions, please contact me.
Thank you for your help.
Create_dummy-values.xlsx
0
Hello experts,
I need to copy active sheet rename it by adding "-v2" at the end.
Could you please advice and provide a smart and simple way to.
-If new sheet (Active sheet &  -v2)
exists delete it
-Copy active sheet
-Rename it by adding v2
-Set new sheet as variable as I need to continue with a with loop
If you have questions, please call contact me. Thank you for your help.
0
Hi, how does the Microsoft Volume License Center work, we purchased some licenses in 2017, do i have to "renew" these ? Products are Office, Visio, etc.

If they expire, can i still use?
0

Microsoft Applications

44K

Solutions

38K

Contributors

Microsoft applications include a variety of software programs, including development and digital authoring programs (Expression and Media Center), educational programs, Internet software, including Essentials, Skype and the Live family, anti-virus, productivity applications and suites like Office, Excel, Word, Outlook, Access and PowerPoint, video games and server applications such as Exchange, SharePoint, IIS and Virtual Server.