Posts by elsuji

Dear Team,

I need code for edit the existing data filtering by dropdown and save it again on the same .

I am having four sheets ( Inward_New, Inward_Edit, Inward_View & Inward_print).

I am entering all the data on sheet "Inward_New". After entered all the data's if select the Save button it will save all the details to sheet "Inward_View" then convert & save pdf file to folder through sheet "Inward_print'.

In sheet "Inward_Edit" if I select the JOB No from Cell "D7" (dropdown), it should display all the fields as per the JOB No which is available on the sheet "Inward_View".

After I edit the required field once I click the "Update and Save" button, it should over write the data in sheet "Inward_View" and save to pdf file also and all the files should go to blank except JOB No.

I am not getting any idea how to do this. Can any one please help me how t do this.

The same was posted on

https://www.excelforum.com/exc…ve-the-existing-data.html

Till now not received any reply . So any one please help me how t do this.

I am attaching my file here for your reference.

I am extracting this datas to another sheet and converting pdf with name of JOB no. Now once I saved and extract the datas the Job No automatically changing it self without doing any thing.

Pls find my file here for your reference

Dear Roy,

I modified the code as per your instruction.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Inward_View")
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Inward_New")
Dim Last_Row As Long

Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

If sh.Range("AX" & Last_Row).Value = "" Then
ws.Range("D7").Value = "JOB-" & ws.Range("D5").Value & "-" & Format(Date, "yyyy") & "-" & Format(sh.Range("AX" & Last_Row).Value + 1, "0000")
Else
ws.Range("D7").Value = "JOB-" & ws.Range("D5").Value & "-" & Format(Date, "yyyy") & "-" & Format(sh.Range("AX" & Last_Row).Value + 1, "0000")
End If
End Sub

Now it is working

Yes.

I tried this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Inward")

ws.Range("D7").Value = "JOB-" & ws.Range("D5").Value & "-" & Format(Date, "yyyy") & "-" & Format(ws.Range("D7").Value + 1, "0000")

End Sub

But it is not working

Yes . I want to change 001 to 002

can any one please reply me for my query

Dear Team,

I want to generate Reference no automatically while opening the sheet from command buttion.

Let me explain.

I am having command button on Sheet "Inward Master" for opening Sheet "Inward".

In sheet "Inward" cell "D7" Job no is there. Once i opened the sheet the Job no should update automatically next no of previously updated no.

And in sheet "Inward_View" values to be display on userform listbox with filtering by selecting JOB No, Customer Name, or Inward Date.

Can any one please help me how to do this.

I am attaching my file here for your reference

Dear Roy,

It is not working properly. The user form is not updating automatically.

When I select drop down it is displaying only one time. If i add any new values it is not displaying.

And the branch name should create only one time. If i edit anything on the branch it should update on the same row where it is created already.

Dear Team,

I had created excel work for update details. In these i am having two problems.

Problem 1:

This work book is Login protected while opening. Un & Pw is admin. Once opened by default Sheet "Main Menu" should open.

Problem 2:

Main Menu --> Service Center Master --> Add Service Center Details --> "Scent2_mstr" user form will open.

In this user form once i saved details,

If i select branch name all the details should display on the user form

I want to delete the details by selecting the branch name of that particular branch row.

I am attaching my file here for reference.

Please help me to solve this issue

Dear Mr.Roy,

Thanks for your reply. It is working perfectly.

Dear Team,

I am using this bellow code for sending email through excel.

Sub Weekly_Update_Mail()
 Dim olApp As Object
 Dim olMail As Object
 Dim olInsp As Object
 Dim wdDoc As Object
 Dim oRng As Object
 Dim sPath As String
 Dim sMessage As String
 Dim sFile As String
 Dim cell As Range
 Dim sBCC As String
 Dim lngCount As Long
     Dim diaFolder As FileDialog
    Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
 
 sMessage = "<br>" & "<font size=""3"" face=""Cambria"" color=""Blue"">" & "Dear Valuable Customer, <br><br>" _
 & "Greetings From " & "<b> SCHWING STETTER !!</b>" & "<br><br>" _
 & " This is regarding " & "<b> SSI EQUIPMENT UPDATES </b>" & " which includes Product Details, Training programs which clearly reflects the Model Designs and changes that we have renovated in our equipment for the convenience Of the customer to get oriented about the Products. <br><br>" _
 & " Kindly find the " & "<b> SCHWING STETTER PRODUCT UPDATES.</b>" & "<br><br>" _
 & " Your feedback is very important as we are constantly looking for ways to improve our services and products.<br><br>" _
 & " Stay Safe! Stay Healthy!!<br><br>" _

 
 'create BCC list
 For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeConstants)
 If cell.Value Like "?*@?*.?*" Then
 sBCC = sBCC & ";" & cell.Value
  Set olApp = CreateObject("Outlook.Application")
 End If
 Next cell
 
 On Error Resume Next
 Set olMail = olApp.CreateItem(0)
 With olMail
 .BCC = sBCC
 .Subject = "SSI PRODUCT UPDATES AND PROGRAMS!!!!!!"
 .HTMLBody = sMessage
 Set olInsp = .GetInspector
 Set wdDoc = olInsp.WordEditor 'access the message body for editing
 .Display 'required to edit message body
 End With
 
 With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show

        ' Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
            attFilePath = Application.FileDialog(msoFileDialogOpen).SelectedItems(lngCount)
            olMail.Attachments.Add (attFilePath)
        Next lngCount

    End With
 
 Set olApp = Nothing
 Set olMail = Nothing
 Set olInsp = Nothing
 Set wdDoc = Nothing
 Set oRng = Nothing
 
End Sub

Now my code is open temp folder directly. But f i want to open ( Environ("USERPROFILE") & "\Desktop\Schwing product catalogue\" ) and from this folder the PDF file only to be attached by selecting manually file name. Other file format are not attach on my mail

Can any one help me how to do this

Dear dangelor,

Now all the email address are updating on BCC and Column "I" also it is printing values.

Thanks for your support

Dear Team,

I am having code for send email with all the email address on the column I

My code is

Private Sub SendMail()
Dim olApp As Object
Dim olMail As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim sPath As String
Dim sMessage As String
Dim sFile As String
Dim cell As Range


    sMessage = "<br>" & "<font size=""3"" face=""Cambria"" color=""Blue"">" & "Dear Valuable Customer, <br><br>" _
                & "Greetings!! </b><br><br>" _
                & " On behalf of everyone from " & "<b> SCHWING STETTER INDIA PVT LTD,</b>" & " We would like to thank you for being a Customer/A Guest/An Investor.<br><br>" _
                & " We value the trust you have put In our products, services and would like to thank you for that. It is always a pleasure serving you and we certainly look forward to doing that in the future.<br><br>" _
                & " And We are happy to inform that we have designed a new catalogue of" & "<b> SCHWING STETTER </b>" & "products for the convenience of the customers to know about the products in detail for your requirement.<br><br>" _
                & " Kindly find the " & "<b> SCHWING STETTER PRODUCT CATALOGUE.</b>" & "<br><br>" _
                & " Your feedback is very important as we are constantly looking for ways to improve our services and products.<br><br>" _
                & " Stay Safe! Stay Healthy!!<br><br>" _
               
    sPath = Environ("USERPROFILE") & "\Desktop\Schwing product catalogue\"
    Set olApp = CreateObject("Outlook.Application")


 For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "J").Value) = "" Then
        On Error Resume Next
    Set olMail = olApp.CreateItem(0)
    With olMail
        .BCC = cell.Value
        .Subject = "GREETINGS FROM SCHWING STETTER!!!"
        .HTMLBody = sMessage
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor    'access the message body for editing
        .Display    'required to edit message body
        sFile = Dir$(sPath & "*.pdf")
        While sFile <> ""
            .Attachments.Add sPath & sFile
            sFile = Dir$()
        Wend
        
    End With
        End If

    Next cell
    
    Set olApp = Nothing
    Set olMail = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing

End Sub

I am having email Id column "I". when i run this code it will trigger n number of emails as per the email address available on column "I".

My condition is when i run this command only one mail should trigger and all the email address in column "I" should go to BCC.

And I want print "Mail Sent on " & Date" on column "J" for that particular mail ID.

For example ,

the mail triggered for "I4". So the "Mail Sent on " & Date" should print on "J4".

If the mail is triggered for "I6, I7 & I10". Then "Mail Sent on " & Date" should Print for "J6, J7 & J10".

i am attaching my sample file here for your reference.

Can any one help me to solve this.

Hi,

Can you please do it. I am waiting for your reply.

Dear Roy,

When i check your code i am getting "Invalid Procedure Call or Argument" message and once i select the Debug it is highlighting bellow code

        Worksheets(mySheet).Range("B2:F24").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=FileFullPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Can you please check and clear the error

Is it possible to download data.csv from https://www1.nseindia.com/live…equities_stock_watch.htm# website and save to desktop using vba code

i am not getting any data. getting error on adding the particular table

I tried. But it is not updating.

If i download the CSV file i will get the data at one time only. i want to update real time data

Dear all,

I want to get the stock historical data from https://www1.nseindia.com/live…equities_stock_watch.htm# website to my excel sheet and refresh every 1min automatically.

Note: In this website the data.csv file is available. all the stock datas are available on this file. I want to fetch data from this file and update on my local excel file.

Can any one help me how to get the datas