Posts by nmkhan3010

Am having two excel file ‘s (“A” & “B”)

“A” is updated till now. (Taken from the server not having the comments or resolution filed)

“B” is not updated till now. (Local Drive file with comments or status of incidents update)

My intention is copying the fields from A to B which are not available in the B file. (only copy the fields which are not in B & It shouldn’t be disturbed the match cases of both A & B fields)

After copying the not matched fields from A to B file and we will work on that and add comments or status filed to that unmatched cases everyday. So, it shouldn’t be disturbed the match cases in B file.

Work fields columns from A to S

In two files common field is “INCIDENT NO” which is in “B” Column.

Please find sample sheets & do the needful.

hi,

I need a macro for removing a immediate duplicate words in a documents.

Example :

Input: financial income income Input : interest income (here no repeated word income so remain same anywhere in the doc)
Output: financial income Output: interest income (here no repeated word income so remain same anywhere in the doc)

Input: unit: unit: INR
Output: unit: INR

Input : gain gain from sales
Output : gain from sales


In the above cases immediate repeated words removed manully like income, unit: & gain
If there is no same repeated word it remains constant and it has not to be changed in anywhere.

Ex: income from sales, unit: INR, gain from investments are replaced above words & will not be reflect anywhere in a document if it is having a single words income, unit:, gain.

Wildcard Find/Replace : Please check and make this is in macro... Wild card is not working, please check and review once.

Find = (<*>?@)\1

Replace = \1


please do the needful ...

Hi

Am having code of batch replacing strings and getting error for char more than 250, can any one help to fix this issue...

My code is shown below:

Public Sub batchReplace_test()
Dim Directory As String
Dim FType As String
Dim FName As String


Directory = InputBox("PLEASE ENTER PATH", "SELECT  THE TARGET FOLDER") & "\"
FType = "*.docx"


ChDir Directory
FName = Dir(FType)
' for each file you find, run this loop
Do While FName <> ""
' open the file
Documents.Open FileName:=FName


' search and replace the company name
Selection.find.ClearFormatting
Selection.find.Replacement.ClearFormatting
Selection.find.Replacement.Font.Bold = True
Selection.find.Replacement.Font.Color = wdColorRed


With Selection.find
.Text = "OldCompanyName"
.MatchCase = True
.Replacement.Text = "NewCompanyName"
End With
Selection.find.Execute Replace:=wdReplaceAll


' replace street address
With Selection.find
.Text = "OldStreetAddress"
.Replacement.Text = "NewStreetAddress"
End With
Selection.find.Execute Replace:=wdReplaceAll


' replace the comment
' With Selection.find
' .Text = "Government subsidies that are included in the current profit and loss, but are closely related to the company's normal business operations, except for government subsidies that meet national policy requirements and are continuously enjoyed by a fixed amount or amount according to a certain standard"
' .Replacement.Text = "Government subsidy accounted as gain or loss for the period, other than those closely related to the company's normal business operations, complying with the national policy, and continuing to be enjoyed at a fixed rate or amount"
'  End With
' Selection.find.Execute Replace:=wdReplaceAll


' save and close the current document
ActiveDocument.Close wdSaveChanges


' look for next matching file
FName = Dir
Loop
End Sub
 

Thanks in advance ....

Hi,

Thank you for replying ....

Sorry for inconvenience caused to you, now am attaching the same files in the ZIP folder.

Please review once and code was running successfully but copying the files into the folder and showing message as "Does not exits" even if it having a same file in the folder.

Thanks in advance for your consideration.................

Hi,

Thanks for replying and folder has created successfully but files are not moving into that and it could be a better to add source path as input box because path need to be changed everytime and everyday

INPUTBOX("PLEASE ENTER PATH", "SOURCE PATH")

Destination path is the same as input path....

Please find the zip files for testing and thanks a lot for considering this......

Hi,

Thanks for replying and still it is not copying the filed from source to destination even if it is avialable in that folder and commenting as "Does not exists"

Please check the attachment and thank you so much for reviewing once

This is totally different previous post was regarding copying specific files based on the excel sheet value, if it is available give a comment as "Copied" and i f not available "Does not exits" and now i want to seperate the files with the common name and create a folder with the common name and copying all the files into that.

i hope you understand my objective.....

Hi,

Am having number of documents in folder with common name like "FinancialTables" & "Taggedtables"

I want to create a folder with the name financial tables and copied all the files with the name having financial tables in to that folder.

I want to create a another folder with the name tagged tables and copied all the files with the name having tagged tables in to that folder.

File formats .rtf, .docx , .pdf , .doc

main objective is to sepeate a files with financial tables and tagged tables that are tagged with the file names

PFA

Thanks in advance .........

Hi

Am very very thankful to your hard and code was still not copying the files if it exits in my machine...

I tried a lot but still not working both code and macro was running successfully with out throwing any errors but output folder was always empty ...

Thank you so much....

If you have time you can review or else skip this, it might be my excel problem and am very thankful to for your propmt response .....

Once again Thank You................

Hi,

Please check once....

Option Explicit

Sub CopyFiles()
    Dim iRow As Integer         ' ROW COUNTER.
    Dim SourcePath As String
    Dim DestinationPath As String
    Dim sFileType As String
    
    Dim bContinue As Boolean
    
    bContinue = True
    iRow = 2
    
    ' THE SOURCE AND DESTINATION FOLDER WITH PATH.
    
SourcePath = InputBox("PLEASE ENTER PATH", "SOURCE PATH")
DestinationPath = InputBox("PLEASE ENTER PATH", "DESTINATION PATH")
    
    sFileType = ".docx" 'TRY WITH OTHER FILE TYPES LIKE ".pdf".
    sFileType = ".rtf"
   
    ' LOOP THROUGH COLUMN "B" TO PICK THE FILES.
    While bContinue
    
        If Len(Range("A" & CStr(iRow)).Value) = 0 Then    ' DO NOTHING IF THE COLUMN IS BLANK.
            MsgBox "Process executed" ' DONE.
            bContinue = False
        Else
            ' CHECK IF FILES EXISTS.
            
            If Len(Dir(SourcePath & Range("B" & CStr(iRow)).Value & sFileType)) = 0 Then
                Range("B" & CStr(iRow)).Value = "Does Not Exists"
                Range("B" & CStr(iRow)).Font.Bold = True
            Else
                Range("B" & CStr(iRow)).Value = "Copied"
                Range("B" & CStr(iRow)).Font.Bold = False
            
                If Trim(DestinationPath) <> "" Then
                    Dim objFSO
                    Set objFSO = CreateObject("scripting.filesystemobject")
                    
                    ' CHECK IF DESTINATION FOLDER EXISTS.
                    If objFSO.FolderExists(DestinationPath) = False Then
                        MsgBox DestinationPath & " Does Not Exists"
                        Exit Sub
                    End If

  '*****
                    ' HERE I HAVE INCLUDED TWO DIFFERENT METHODS.
                    ' I HAVE COMMENTED THE SECOND METHOD. TO THE SEE THE RESULT OF THE
                    ' SECOND METHOD, UNCOMMENT IT AND COMMENT THE FIRST METHOD.
                    
                    ' METHOD 1) - USING "CopyFile" METHOD TO COPY THE FILES.
                    objFSO.CopyFile Source:=SourcePath & Range("B" & CStr(iRow)).Value & _
                        sFileType, Destination:=DestinationPath
                    
                    ' METHOD 2) - USING "MoveFile" METHOD TO PERMANENTLY MOVE THE FILES.
                    'objFSO.MoveFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _
                        sFileType, Destination:=sDestinationPath
                    '*****
                End If
            End If
        End If
       
       iRow = iRow + 1      ' INCREMENT ROW COUNTER.
    Wend
End Sub

Please see the above ....

Settings are the same...

Hi,

Thanks for replying but i tried on the code still it is not copying the files from source to destination based on the excel value.

Comment should be provided whether it has copied or not in column B

Am sharing screenshot as "Not working" and "Required outout (actual output)"

PFA

Can you pleas review once and am very thankful to you......

Macro was running properly but not copying the files listed in A column but not giving any error message i think it was not looping here ....

Can you please help me.......

1. objFSO.CopyFile Source:=SourcePath & Range("B" & CStr(iRow)).Value & _
2. sFileType, Destination:=DestinationPath

Hi,

i want to copy the list of files from A column and below macro was copied but it was not looping to copied command, can any one help me....

Option Explicit


Sub CopyFiles()
Dim iRow As Integer         ' ROW COUNTER.
Dim SourcePath As String
Dim DestinationPath As String
Dim sFileType As String


Dim bContinue As Boolean


bContinue = True
iRow = 2


' THE SOURCE AND DESTINATION FOLDER WITH PATH.


SourcePath = InputBox("PLEASE ENTER PATH", "SOURCE PATH")
DestinationPath = InputBox("PLEASE ENTER PATH", "DESTINATION PATH")


sFileType = ".docx" 'TRY WITH OTHER FILE TYPES LIKE ".pdf".
sFileType = ".rtf"


' LOOP THROUGH COLUMN "B" TO PICK THE FILES.
While bContinue


If Len(Range("A" & CStr(iRow)).Value) = 0 Then    ' DO NOTHING IF THE COLUMN IS BLANK.
MsgBox "Process executed" ' DONE.
bContinue = False
Else
' CHECK IF FILES EXISTS.


If Len(Dir(SourcePath & Range("B" & CStr(iRow)).Value & sFileType)) = 0 Then
Range("B" & CStr(iRow)).Value = "Does Not Exists"
Range("B" & CStr(iRow)).Font.Bold = True
Else
Range("B" & CStr(iRow)).Value = "Copied"
Range("B" & CStr(iRow)).Font.Bold = False


If Trim(DestinationPath) <> "" Then
Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")


' CHECK IF DESTINATION FOLDER EXISTS.
If objFSO.FolderExists(DestinationPath) = False Then
MsgBox DestinationPath & " Does Not Exists"
Exit Sub
End If


'*****
' HERE I HAVE INCLUDED TWO DIFFERENT METHODS.
' I HAVE COMMENTED THE SECOND METHOD. TO THE SEE THE RESULT OF THE
' SECOND METHOD, UNCOMMENT IT AND COMMENT THE FIRST METHOD.


' METHOD 1) - USING "CopyFile" METHOD TO COPY THE FILES.
objFSO.CopyFile Source:=SourcePath & Range("B" & CStr(iRow)).Value & _
sFileType, Destination:=DestinationPath


' METHOD 2) - USING "MoveFile" METHOD TO PERMANENTLY MOVE THE FILES.
'objFSO.MoveFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _
sFileType, Destination:=sDestinationPath
'*****
End If
End If
End If


iRow = iRow + 1      ' INCREMENT ROW COUNTER.
Wend
End Sub

Range is "Column A" and Msg box is "Column B" Format types .doc, .rtf,.docx , .pdf

Please anyone help me in this regards or if possible make it as easier to loop and thanks in advance.

<img src="https://www.ozgrid.com/forum/core/images/smilies/emojione/263a.png" alt=":)" class="smiley" srcset="https://www.ozgrid.com/forum/core/images/smilies/emojione/[email protected] 2x" height="23" data-tooltip="smile" id="wscSmiley_0_0">

Range is "Column A" and Msg box is "Column B"  Format types .doc, .rtf,.docx , .pdf

Please anyone help me in this regards or if possible make it as easier to loop and thanks in advance.


<img src="https://www.ozgrid.com/forum/core/images/smilies/emojione/263a.png" alt=":)" class="smiley" srcset="https://www.ozgrid.com/forum/core/images/smilies/emojione/[email protected] 2x" height="23" data-tooltip="smile" id="wscSmiley_0_0">

Hi,

I need a macro for BROWSE, SEARCH AND COPY FILES FROM ONE LOCATION TO ANOTHER BASED ON THE LIST GIVEN IN EXCEL SHEET USING VBA.

Range is selected in excel

Source path: "Shold be given manually"

Destination path: "Shold be given manually"

Msg "Files Copied "

Besides list name colum can we update "copied" if it found and "N/A" if it is not found.

Format types: .doc, .rtf , .docx , .xlsx , .pdf

Hi,

I need a macro for BROWSE, SEARCH AND COPY FILES FROM ONE LOCATION TO ANOTHER BASED ON THE LIST GIVEN IN EXCEL SHEET USING VBA.

Range is selected in excel

Source path: "Shold be given manually"

Destination path: "Shold be given manually"

Msg "Files Copied "

Besides list name colum can we update "copied" if it found and "N/A" if it is not found.

Format types: .doc, .rtf , .docx , .xlsx , .pdf

Please any one help me .....

Thanks in advance.............

Hi,

PFA ...

Thanks for replying...

Thanks for replying its working great ...

But i need as dynamic and i want drop down list in any particular column, can you review once ....

Please find the attachment.

Thank you so much....

Am having another quiery can you please review once............

COUNT AND SUM FUNCTION BY DIALY, WEEKLY AND MONTHLY

Hi,

Thanks for replying and i have attached a word document clearly mentioning my requirement, can you please review once and if you need any further clarification am ready to provide all the data and thanks for replying....

Hi,

Thanks a lot for replying.............

In sheet2 "A" column i have created a drop down list of all languages from the range "C: column.

Now if i want to select "Turkish" in A2 i need to scroll down from "Albanian" to "Turkish" (means 63 times i need to scroll down by down arrow)

If we create a searchable drop down list in "A" column such as if enter "T" it will displays all languages having a letter "T"

suppose if i enter "TUR" it shows tur list from that range ....

Please Find the sample attachment.

I need the result whether it has done by VBA or Userform or with formula no issues.....

Thanks in Advance....

Hi,

I have data i need to count of fields based on the data by dialy, weekly and monthly.

Weekly format : 03/01/2020-03/07/2020 , 03/08/2020-03/14/2020 .......................................... (this can be done through formula)

Monthly format : 03/01/2020-03/31/2020 , 04/01/2020-04/30/2020 ........................................ (this can be done through formula)

Above two columns are in weekly report and monthly report sheet and based on the data in sheet1 all the date fields get updated in weekly and monthly

Just count and sumup all the fileds based on dates (Dialy, weekly, monthly)

PFA and do the needful ...

Thanks in advance...........