Posts by Ashu2019

Shweta,

There are multiple ways of doing it see example code below, what i have done here is assign values of a year i.e. 12 and loop it into pivot slicers with an if condition.

you can also use for each option to loop pivot slicers too and then use the below code to loop again into the slicers fields.

For i = 1 To 12

j = Choose(i, "Jan", "Feb", "Mar")


With ActiveWorkbook.SlicerCaches("Slicer_Assigned_Month1")

If j = "Jan" Or j = "Feb" Then

.SlicerItems(j).Selected = True

Else

.SlicerItems(j).Selected = False

End If

End With


Next i

Mika, you can use conditional formatting to highlight the duplicate values and then use filter on date column and color filter on the number column which will give you duplicate values with no dates.

Hi Andy,

if the events ends at midnight i.e. next day, you cannot change the date to today as it will not be correct, however if you still want to make the date as same day , simply replace the end time with 11:59 PM so that it ends the same day.

Also the below code is collecting the events from the calendar, loading it into array and then pasting it into excel file.

 For Each olApt In olfolder.items
 myArr(0, NextRow) = olApt.Subject ' copying subject
 myArr(1, NextRow) = olApt.Start  'copying start date / time
 myArr(2, NextRow) = replace(olApt.End,"00:00","11:59") ' replacing end time from 00:00 to 11:59
 myArr(3, NextRow) = olApt.Categories
 NextRow = NextRow + 1
 Next
 On Error GoTo 0
 'Write all records to a worksheet from an array, this is much faster
 ws.Range("A2:D" & NextRow + 1).Value = WorksheetFunction.Transpose(myArr) ' pasting in excel sheet

Tom, Welcome to the Forum

like to confirm few things , does after longing into website it opens a new page?

if it does not then the instance you set to ie is already active you can use the reference to manipulate further data.

if no you need to loop through all ie instance to get hold of your new page example below.

sub test

Set ie = FindIEObject("Title of the ie window")

end sub


Public Function FindIEObject(target As String) As InternetExplorerMedium
 
    Set objshell = CreateObject("Shell.Application")
    IE_count = objshell.Windows.Count
    
    For x = 0 To (IE_count - 1)
        On Error Resume Next    ' sometimes more web pages are counted than are open
        my_url = objshell.Windows(x).document.Location
        my_title = objshell.Windows(x).document.Title
        If my_title = target Then 'compare to find if the desired web page is already open
            Set FindIEObject = objshell.Windows(x)
            Exit For
        End If
    Next
End Function

did not understand what you want to achieve and what count you need.

dear all

any other alternative method as send click used is creating chaos among end user

Quote from cs22

Minus the folder path which is just "G:\\WA3223\WA\Inventory Archive\2019\Dec\" The code posted is exact.

please share the screen shot of the error

Ashish, the loop which you are using has an inner loop which makes it difficult to achieve what you desire, because for each statement runs at first and not correspondingly with another for each statement, hence you need to use do while loop statement.

 Sub test()
Dim i As Long

i = 2 ' row number to start


Do While Range("D" & i).Value <> ""

If Range("D" & i).Value >= #5/1/2019# And Range("D" & i).Value <= #4/30/2020# Then 'MM/DD/YY format

    If Range("C" & i).Value = "2019-20" Then
    Range("I" & i).Value = Range("C" & i).Value
    Range("J" & i).Value = Range("J" & i).Value + Range("H" & i).Value
    Else
    Range("I" & i).Value = "other years"
    Range("J" & i).Value = Range("J" & i).Value + Range("H" & i).Value
    End If

ElseIf Range("D" & i).Value >= #5/1/2021# And Range("D" & i).Value <= #4/30/2022# Then 'MM/DD/YY format
Range("M3").Value = "step 1 clear"
End If

i = i + 1
Loop




End Sub

Mate, Unless you provide the actual code or an sample workbook it will be difficult for us to come to any conclusion.

Cs you may want to throughly check ur code.

myFileName = mySaveFolder & "Inventory Balance" & " " & DateFormat

with

myFileName = mySaveFolder & "\Inventory Balance" & " " & DateFormat

This means you need the code at first and not later why don't you use boolen expression stored in workbook hidden and use an if condition to bypass the code you want to delete, also if you are using some kind of loop use doevents to manage other events triggered by the macro

Have to look at the below line.

replace this
myWB.SaveAs saveFolder & DateFormat & " " & objAtt.FileName & "x", FileFormat:=51
with
myWB.SaveAs saveFolder & DateFormat & " " & objAtt.FileName & ".xlsx", FileFormat:=51

my suggestion is to disable all macros from options settings in workbook, open the macro workbook and delete the code manually, give it a try.

Try to loop for the correct id example below, worked for me when unable to directly click on the link / button.

dim itm as object, itm1 as object
set itm = objIE.document.getElementsByClassName("names")

for each itm1 in itm
debug.print itm1.id
itm1.focus
itm1.click
next itm1

Dear Guru's,

I need some help with an IE automation where in I have to click on a popup dialog box as "yes" and then proceed to other procedures.

I am stuck upon the dialog box where I am unable to get the element of the button as there is no ID of the button, as per my knowledge the popup dialog box is warped inside an iFrame > table > span > form > table > dialogbox.

I tried every possible ways to get at the dialogbox level but failing to do so, I tried looping through all button elements of iframe still it does not gives me the button element handle.

I do not want to use sendkeys as its not full proof, I am listing done the html code for refrence and the code I am using so far.

Any help would be really appreciated.

I want to click on this button.

button title="Yes" class="x80" onclick="submitForm('DefaultFormName',1,{'_FORM_SUBMIT_BUTTON':'RemovePsYesButtonlxUkUkni'});return false" type="submit">Yes</button>

<span id="OADialogPopupRN"><div><div class="x6d"><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr class="xbw" valign="bottom"><td><img src="/OA_HTML/cabo/images/skyros/warnl.gif" width="18" height="18" border="0" alt=""></td><td><script>var _tURL="\x2FOA_HTML\x2Fcabo\x2Fimages\x2Fskyros\x2Ft.gif";var _axm;t(5,0)</script></td><td width="100%"><table cellpadding="0" cellspacing="0" border="0" width="100%"><tr><td width="100%"><h1 class="x61" type="OraHeader">Warning</h1></td></tr></table></td></tr></table></div></div><table border="0" cellspacing="0" cellpadding="0"><tr><td align="left" width="580"><span class="x1">Are you sure you want to remove 1 documents?</span></td></tr><tr><td width="580" height="20"></td></tr><tr><td align="left" width="580"><span class="x0">Documents you remove from this request will need to be resubmitted for payment.</span></td></tr></table><span style="float:left" ></span><span style="float:right" ><table cellpadding="0" cellspacing="0" border="0"><tr><td><script>t(8,0)</script></td><td><button title="No" class="x80" onclick="submitForm('DefaultFormName',1,{'_FORM_SUBMIT_BUTTON':'RemovePsNoButtonvaNLJ0xM'});return false" type="submit">No</button></td><td><button title="Yes" class="x80" onclick="submitForm('DefaultFormName',1,{'_FORM_SUBMIT_BUTTON':'RemovePsYesButtonlxUkUkni'});return false" type="submit">Yes</button></td></tr></table></span></span>