We help IT Professionals succeed at work.

Microsoft Office

70K

Solutions

42K

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.

Hi
In the following code the line     Set xlwb = xlAp.Workbooks.Open(strPath) is
causing issues. The code just seems to hang. I thought the error handler code
would have picked up any issues but it didn't. Can I add any further code or change it
to run more smoothly?
Thanks

Sub oCaller()

    Dim oSamePath As String: oSamePath = ActiveWorkbook.Path
    Dim strPath As String: strPath = oSamePath & "\" & "Freitan Props - TB - Feb 2020.XLSX"
    Dim strWorksheet As String: strWorksheet = "General Ledger Trial Balance"
    Dim oRowHeader As String: oRowHeader = "7050.00.00"
    Dim oColumnHeader As String: oColumnHeader = "CLOSING"
    Dim oRowHeaderColumnNumber As Integer: oRowHeaderColumnNumber = 6
    Dim oColumnHeaderRowNumber As Integer: oColumnHeaderRowNumber = 1
    Dim oValue As String
   
    oValue = GetIntersectValue(strPath, strWorksheet, oRowHeader, oColumnHeader, oRowHeaderColumnNumber, oColumnHeaderRowNumber)
   
    MsgBox oValue

End Sub


Function GetIntersectValue(strPath As String, strWorksheet As String, oRowHeader As String, oColumnHeader As String, _
Optional oRowHeaderColumnNumber As Integer = 1, Optional oColumnHeaderRowNumber As Integer = 1)

    On Error GoTo GetIntersectValue_error
   
    Debug.Print strPath

    Dim xlAp As Excel.Application
    Dim xlwb As Excel.Workbook
    Dim xlws As Excel.Worksheet
    Dim lngCol As Long
    Dim lngRow As Long
    Dim oNotFoundError As String: oNotFoundError = ""
   
 …
0
Experts,

How can I change the row  rCell2.Value = Date - Day(Date)  to return the end of the previous month if there is no data in the cell?
Right now, the code works as planned in all cases but returns a blank if the cell is blank.

   
 Range(Selection, Selection.End(xlDown)).Select
    Dim rCell2 As Range

    For Each rCell2 In Selection
        If IsDate(rCell2) And Not rCell2.HasFormula Then
             rCell2.Value = Date - Day(Date)
        End If
    Next rCell2

Open in new window



thank you very much
0
I am struggling with creating a dynamic bar chart that is comparing activity (Category) between visits (Aud & WAV). I have attached what I have come up with so far. I've tried the OFFSET formula method and INDEX-MATCH not to include blanks, but I cannot figure out how to get the y-axis (Category) to work dynamically.

Would appreciate the help.

Thank you in advance.
0
Hi

I am using the following custom function =PullValue(G2,G3,G4) in a cell of my Excel sheet but the result just shows #Value!
The idea is to pull a value from another workbook

Function PullValue(ByVal oSourceFileName As String, ByVal oSourceSheetName, ByVal oSourceCellRange As String) As String

        PullValue = "='[" & Range(oSourceFileName).Value & "]" & Range(oSourceSheetName).Value & "'!" & Range(oSourceCellRange).Value

End Function

1
0
Hi
I am using the following Excel VBA code to pull a value from a cell in a closed workbook.
How would I build one function that can be used in each cell to achieve the same thing?
Thanks


Sub oMain()


    Dim oSamePath As String: oSamePath = ActiveWorkbook.Path

    Call GetValuesFromAClosedWorkbook("G27", oSamePath, "Freitan Props - TB - Feb 2020.XLSX", _
            "General Ledger Trial Balance", "H35", False)
           

End Sub

Sub GetValuesFromAClosedWorkbook(ByVal oDestinationCellRange As String, ByVal oSourceFilePath As String, _
            ByVal oSourceFileName As String, ByVal oSourceSheetName, ByVal oSourceCellRange As String, ByVal blnConvertToValue As Boolean)
           
    Dim oFunction As String
           
    With ActiveSheet.Range(oDestinationCellRange)
        'Assigning the array formula to the specified range
        '.FormulaArray = "='" & oSourceFilePath & "\[" & oSourceFileName & "]" & oSourceSheetName & "'!" & cellRange
        oFunction = "='[" & oSourceFileName & "]" & oSourceSheetName & "'!" & oSourceCellRange
        Debug.Print oFunction
        .FormulaArray = oFunction
        'Removing formula from the cell and pasting the values in the cell
        If blnConvertToValue = True Then
            .Value = .Value 'Only activate this line if you want to convert the cell value
        End If
     
    End With

End Sub
0
Excel - I have a column where I am using military time -example: 0645

I have it formatted as Text but I have to constantly tell it to ignore the error to get rid of the little green flag in the corner.

Is there a way to not have the error flag show up all the time?
0
I have an Excel VB.net add in that I built in a previous version of Visual Studio. In Visual Studio 2019 where do I reference
Microsoft.Office.Interop.Word.Application?

Thanks
0
Hi,

I'm using a Office 365 account in our phone system to access all users Exchange calendar, but after Microsoft Security Defaults have been enabled, it's no longer working. I'm getting this message:

the OIP cannot successfully connect to the Exchange Server.

2020-03-16 15:06:03:142 22  >   Microsoft Exchange version:        EXCHANGE_2016
2020-03-16 15:06:03:142 22  >   Microsoft Exchange server address: outlook.office365.com
2020-03-16 15:06:03:142 22  >   Domain:                          
2020-03-16 15:06:03:142 22  >   User name:                         XXX@XXX.XX
2020-03-16 15:06:03:142 22  >   Notification interval:             5s
2020-03-16 15:06:03:142 22  >   ------------------------------------------------------------
2020-03-16 15:06:03:142 22  >   Calendar synchronization:          yes
2020-03-16 15:06:03:142 22  >   E-mail synchronization:            no
2020-03-16 15:06:03:142 22  >   Private contacts synchronization:  yes
2020-03-16 15:06:03:142 22  >   Public contacts synchronization:   no
2020-03-16 15:06:03:142 22  >   ------------------------------------------------------------
2020-03-16 15:06:03:142 22  >   Primary phone mapping:             Company Main Phone
2020-03-16 15:06:03:142 22  >   ============================================================
2020-03-16 15:06:05:783 195  >   ExchUser.DoNotification(): Connection to server still NOK (wrong user name or password?): Unauthorized !!!!!!!!!!!!!!!!!!!!
2020-03-16 15:06:05:783 195…
0
Hi

Im having trouble logging in to skype for business, hosted in Office 365. I get the following error "There was a problem acquiring a personal certificate required to sign in. If the problem continues, please contact your support team."
It works when i log in as a local admin, but not when i log in as a domain user that is not local adminit doesnt work. I can see that the local admin account that works, the user gets a user certficate for skype when connecting. The other non-working user doesnt get the certificate.

Anybody thats seen this issue before? There are a lot of threads on other sites about this, but non has helped me solve this issue

BR
Carlos
0
The following VBA solution from this forum has been working great. I just need to add the following criteria to stop the calculation from happening on blank rows:

If Column A = "" then Column J (Expiry Issue) = ""

The existing code is below. Additionally, I have attached a sample Excel file as well as a screen capture depicting what I am looking for.

Function expiryIssue(rng As Range) As String
Application.Volatile
Dim dte1 As Range
Dim dte2 As Range
Dim status As String
Set dte1 = rng.Cells(1, 1)
Set dte2 = rng.Cells(1, 2)
status = rng.Cells(1, 3)
Select Case Cells(rng.Row, "A")
    Case "AIP_ENR", "AIP_AD", "AIP_GEN"
        expiryIssue = ""
        Exit Function
End Select
If status <> "Expired" And status <> "Cancelled" And status <> "Replaced" Then
' If there is no Proposed or Approved expiry date
    If dte2 = "" And dte1 = "" Then
        expiryIssue = "Missing expiry dates"
    Else
    'If there is no Approved Expiry date and the Proposed Expiry date is within 60 days of todays date
        If dte2 = "" Then
            If dte1 < Date + 60 Then expiryIssue = "Review proposed expiry date"
        Else
        'If the Approved Expiry date is older than today's date
            If dte1 = "" Then
                If dte2 < Date Then expiryIssue = "Issue"
            Else
                If dte2 < Date Then expiryIssue = "Issue"
            End If
        End If
    End If
End If
End Function

Open in new window


Thanks!
Andrea
EE_Expiry_Add_Criteria_Blank_Rows.xlsm
Additional_criteria_Mar29.png
0
Experts,

I need VBA code to shade cells purple.
The condition is:
If column E = "Company A" or "Company B" then shade the cells in Column  K purple

How can I do that?
The conditional formatting does not work in this file.  
I think there could be some corruption so I want to test the formatting through VBA

Thank you
0
Here's a good one....
Rebuilding my PC...W10...SSD...

I made a Macrium image of the old W10 installation...

Wiped the SSD reinstalled W10....Everything is good so far...

Reinstalling my programs and I realized I did NOT pull the Office key out of the registry...

Been trying to pull it out of the Macrium image....but so far...no joy...

Any idea on how I might be able to pull the key out of the Macrium registry....

The only way I can think of is to replace the SSD with a different HD and
reload the Macrium image to the other HD......
Then pull out the Office key....

The swap drives....

Is there an easier solution...???
0
Hello Experts,

I have recorded a macro and I need to tweak it. but not sure how.
What I need to do is change the selected cells to end of previous month but the code is basing it off the value of what is in the cells.
I need the code to forget about what dates are in the cells and instead change the dates in the selected cells it to end of previous month based on current dates.
example:
if the cells contain 1/1/2020 I need them to change to end of previous month based on the current date of 3/28/20 which will change the cells to a value of 2/29/20 (leap year in this case).  The code below is changing the value to 12/31/19 (end of previous month based on the 1/1/20...the date that is in the cells. )

this is the part that I need to change:
 Range(Selection, Selection.End(xlDown)).Select
   
     Dim rCell As Range

    For Each rCell In Selection
        If IsDate(rCell) And Not rCell.HasFormula Then
            rCell.Value = WorksheetFunction.EoMonth(rCell.Value, -1)
        End If
    Next rCell

Below is the entire code:

Sub Closing_Banco_Change()
'
' Closing_Banco_Change Macro
'

'
    ActiveSheet.Range("$A$1:$AB$2211").AutoFilter Field:=7, Criteria1:= _
        "BANCO BOLIVARIANO"
    Range("B1320").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "11/1/2018"
    Range("D1320").Select
    Range(Selection, Selection.End(xlDown)).Select
    
     Dim rCell As Range

    For Each rCell In 

Open in new window

0
Hi Experts!

Praying & Hoping u guys dealing with this pandemic with the best of spirits! Experts-Exchange has to stay up and running!  

I just got a great help from Tom in a PowerBI question and wanted to ask if PowerBI have some sort of templates that by setting up the data accordingly I can use said template? or PowerBI is just create & execute not re-use designs?

let me know please and thank u guys!
0
I need to loop through a folder of excel files and delete the drawing objects  on specified sheets within each workbook..  I have found the following code to delete
shapes on an active worksheet,

Sub DeleteShapes()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next Shp
End Sub

but I need to loop through an entire directory of excel files and delete the shapes on two specified named tabs. Each  file has the same two named tabs. Is there a way to  automate this process? I cannot get the workbook open Method to work without an error.
Thanks!
0
We're looking at the reports from Office 365 admin panel and want to get more detailed information.
So far, all I can see is a 7 day/30/90/120 day report. Ideally I'd like hourly reports to see which
time of day the business communicates the most, and which app (teams,sharepoint, mail etc).
Is there a way of doing this?
0
Excel - I need to be able to automatically sort by either Month or Location.  I was thinking a button for each sort.

Also I need it accommodate adding additional columns and rows. The user will be adding new columns and new rows on a continual basis and will need to sort as new information is added.

The first sort button should sort by  MONTH > START DATE > LOCATION

The second sort button should sort by LOCATION > MONTH > START DATE
Events.xlsx
0
I have a spreadsheet where I have multiple rows of different data where I would like to consolidate and summarize on a single line for each ID.  It would be great to have some sort of macro that can take the data from the source tab and generate the data in the output tab (see attached spreadsheet).
ee_help.xlsx
0
I am trying to compare two separate sheets in Excel.  The problem is, each row does not match to the adjacent row.  I want to select a row and compare it to another row displaying what is missing in in either sheet.
0
I want to populate two cells in a table as rows are added, The  first column is each row has a drop down which references all the staff records on the pr tab. e.g.if Staff number 33 is selected from the dropdown on the absence table, the name will then populate from the PR table.
Employee-PR.xlsx
0
Hi

I am trying to help someone who has access to their office via a VPN connection. Even though they stay connected the drives keep disappearing in Windows explorer.

What might cause this?
0
Brand new laptop that I placed into the domain and connect to an on-premise exchange server. Everything worked fine until I left the office now on a different network (outside the LAN) MS Office prompts for a Microsoft account username and password. The version of MS Office was purchased stand-alone with a product key. I don't need nor want to have any MS account associated with this installation. All other devices on my network log into Outlook after the license key was entered and do not have a Microsoft Account connected to them or have a MS account prompt for the password. This has to be something on the computer. Likely a setting where the idiots at MS have to "F" with everything.

Sorry for the attitude, already spent over 2 hours with idiots from MS basically clueless so I'm asking the Experts.
0
We just upgraded from Office 2007 to Office 2019. We are using installed software, not the cloud.  Is there a way to make Outlook look more like the 2007 version?  Not really liking the look and feel of Outlook 2019.  Staff is complaining, too.
0
Fix Win10 VBA Excel 365 loop / variable logic for conditional format.
If the row is a Total row and either (current month and current %, prior month and prior %, current ytd and current ytd%)  are higher than standard, highlight entire row
In test example line 34 should highlight, but none other

 I tried a few things with the conditional formatting variables and this compiles, but something is not working.
previous versions have highlighted every total row and the below one does not highlight any.
lastrow appears working, logic for is it is a total line  appears working.
cr (current row is probably working

Sub ConFormat()

Dim LastRow As Double
Dim myCell As Range

Dim cr As Long
' sv - startvar  sp startperc
Dim sv As Range
Dim sp As Range
' Set sv = Range("F2")
' Set sp = Range("G2")
Set sv = Sheets("Chk").Range("$F$2")
Set sp = Sheets("Chk").Range("$G$2")
cr = ActiveCell.Row
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

For Each myCell In Range("C11:C" & LastRow)
    If Left(myCell.Value, 5) = "Total" Then
'   If OR(AND(ABS(F & cr)>sv,ABS(G & cr)>sp), AND(ABS(J & cr)>sv,ABS(K & cr)>sp), AND(ABS(O & cr)>sv,ABS(P & cr)>sp)) Then
' If (Abs(F & cr) > sv And Abs(G & cr) > sp) Or (Abs(J & cr) > sv And Abs(K & cr) > sp) Or (Abs(O & cr) > sv And Abs(P & cr) > sp) Then
     
      myCell.EntireRow.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
 "=OR(AND(ABS(F & cr)>sv,ABS(G & cr)>sp), AND(ABS(J & cr)>sv,ABS(K & …
0
Running WinServ2012R2 with Access and Outlook 2016.

Using job scheduler and macros/VBA to send out automated reports via email.  Messages are going out fine....but now I want to see who's actually reading them and who's ignoring them.

Outlook profile on the machine has read-receipt-requested as a default.  Access is sending the messages out via EmailDatabaseObject and/or SendObject....and it's not picking up that profile setting.  Instead, the sent messages have the tracking options (delivery/read receipt) greyed out.

Any way to fix/enable it?  If not, we'll live....but it would have been nice.  

The Access/Outlook "break" might even be by design.  I noticed that Access macros will still run even if Outlook is NOT open.  The messages will just queue up in the Outbox and will go out whenever Outlook gets re-opened.
0

Microsoft Office

70K

Solutions

42K

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.