#1
|
||||
|
||||
Extract data based on pattern with respect to specific column
I have source excel(main.xlsx) file containing three column
Code:
4913864 20020403 EXP. 4913864 20011023 REM. 4913864 19970512 M184 4913864 19930816 ASPN 4913864 19930715 M183 4984887 19950115 EXP. 4984887 19940823 REM. 4984887 19910506 ASPN 4998743 19950312 EXP. 4998743 19941018 REM. 5076714 20031231 EXP. 5076714 20030716 REM. 5076714 19990621 M184 5076714 19990223 RMPN I have another excel (database2.xlsx) file containing two column for example Code:
ASPN Payor Number Assigned. EXP. Patent Expired for Failure to Pay Maintenance Fees. EXPX Patent Reinstated After Maintenance Fee Payment Confirmed. F160 Maintenance Fee Has Already Been Paid. Refund is scheduled. F161 Maintenance Fee Tendered too Early. Refund is scheduled. F162 Maint Fee Payment Does Not Properly Identify the Patent. F163 M.F.Payment/Certificate of Mailing Untimely, 37 CFR 1.8. F164 M.F.Payment/Express Mail Procedures Untimely, 37 CFR 1.10. F165 M.F.Payment Sent With Fees for Other Purposes - Will Refund. F166 M.F. Payment not Accepted/Not Immed. Negotiable/Returned. F167 Check/Deposit Account Authorization is Unsigned - Returned. F168 Payment Received - Balance in Deposit Account Insufficient. F169 Payment is in Excess of Amount Required. Refund Scheduled. Now in source file I would like to replace column C with what found for the same from database file column B Both files are relatively small and can be converted to any file formate if requred (XML, HTML etc). Files can be found at: https://sites.google.com/site/rtsk2015/excelforum I can not upload files because of cyberoam at my office |
#2
|
|||
|
|||
Hi,
Do you open both file at the same time? Which file do you need the code in? Are you wanting a separate workbook to hold the code? |
#3
|
||||
|
||||
Sorry for not making it clear
I want data to be replaced in main.xlsx to make it final.xlsx, can be found at: Final.xlsx should look like this. Code:
4984887 19950115 Patent Expired for Failure to Pay Maintenance Fees. 4984887 19940823 Maintenance Fee Reminder Mailed. 4984887 19910506 Payor Number Assigned. 4998743 19950312 Patent Expired for Failure to Pay Maintenance Fees. https://sites.google.com/site/rtsk2015/excelforum file name. Final.xlsx |
#4
|
||||
|
||||
Try:
Code:
Sub Demo() Application.ScreenUpdating = False Dim rSrc As Long, rTgt As Long, StrTmp As String Dim xlShtTgt As Worksheet, xlShtSrc As Worksheet, xlRng As Range On Error Resume Next Set xlShtTgt = Workbooks("Main.xlsx").Sheets(1) Set xlShtSrc = Workbooks("Database2.xlsx").Sheets(1) With xlShtTgt Set xlRng = .Range("A1:A" & .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row) End With With xlShtTgt For rTgt = 1 To .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row StrTmp = .Cells(rTgt, 3).Value With xlShtSrc rSrc = .Range("A1:A" & .UsedRange.Cells.SpecialCells(11).Row).Find( _ What:=StrTmp, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ MatchCase:=True, SearchFormat:=False).Row End With .Cells(rTgt, 3).Value = xlShtSrc.Cells(rSrc, 2).Value Next With .Columns(3) .WrapText = False .AutoFit End With End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
Sorry to bother but this is my first encounter with excel macro.
I tried running the macro from main.xlsm but its not working, nor showing any error. I think problem is with Path to database file. How do I Run if both of my files are on desktop i.e. C:\Users\rahulkumar.patel\Desktop. From wchich document I should run macro. I tried changing like this. Code:
xlShtTgt = Workbooks("C:\Users\rahulkumar.patel\Desktop\Main.xlsm").Sheets(1) Set xlShtSrc = Workbooks("C:\Users\rahulkumar.patel\Desktop\Database2.xlsx").Sheets(1) |
#6
|
||||
|
||||
The macro is designed to be run with both files open. As is obvious from the code I posted, the doesn't make any attempt to open either file. After all, you haven't provided any indication of whether either file is stored in relation to the other. The macro can be run from any open Excel file.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
||||
|
||||
First Excel Macro!!!!!!!!!!
Can we assign path If I dont want to open any of the file and want to run macro from third macro enabled doccument?
Yes: I got the solution using simple excel tutorial Code:
Sub Demo() Application.ScreenUpdating = False Dim rSrc As Long, rTgt As Long, StrTmp As String Dim xlShtTgt As Worksheet, xlShtSrc As Worksheet, xlRng As Range On Error Resume Next Set wkb = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\Sheet1.xlsx") Set wkb1 = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\FE.xlsx") Set xlShtTgt = Workbooks("Sheet1.xlsx").Sheets(1) Set xlShtSrc = Workbooks("FE.xlsx").Sheets(1) With xlShtTgt Set xlRng = .Range("A1:A" & .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row) End With With xlShtTgt For rTgt = 1 To .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row StrTmp = .Cells(rTgt, 3).Value With xlShtSrc rSrc = .Range("A1:A" & .UsedRange.Cells.SpecialCells(11).Row).Find( _ What:=StrTmp, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ MatchCase:=True, SearchFormat:=False).Row End With .Cells(rTgt, 3).Value = xlShtSrc.Cells(rSrc, 2).Value Next With .Columns(3) .WrapText = False .AutoFit End With End With Application.ScreenUpdating = False wkb.Close SaveChanges:=True wkb1.Close SaveChanges:=True End Sub |
#8
|
||||
|
||||
Since the files must be open for the macro to work, they would have to be opened anyway, whether by the user or by the macro. I've already provided you with code in the Word forum showing what's involved in testing whether the file is open and, if not, opening it.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
||||
|
||||
I will try and use guidance provided by you.
|
#10
|
||||
|
||||
I found solution to run my script from CMD
I made following vbscript to run macro saved in macro enabled doc. Code:
Option Explicit On Error Resume Next ExcelMacroExample Sub ExcelMacroExample() Dim xlApp Dim xlBook Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\Fees.xlsm", 0, True) xlApp.Run "OF" xlApp.Quit Set xlBook = Nothing Set xlApp = Nothing End Sub |
#11
|
||||
|
||||
I can't see what that code has to do with anything discussed in this thread. Furthermore, if you want to run Excel from Word, I've already shown you the proper way to do so, here: https://www.msofficeforums.com/word-...html#post90291
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
||||
|
||||
Sorry for posting extended solution.
Actually I wanted to automate few things using vbscript. So in this case I saved above vbscript as Fees.vbs and ran using Code:
cscript Fees.vbs I Thout this might be helpful to someone in case. |
#13
|
||||
|
||||
Quote:
Furthermore, in post #7 you have: Code:
Set wkb = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\Sheet1.xlsx") Set wkb1 = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\FE.xlsx") Set xlShtTgt = Workbooks("Sheet1.xlsx").Sheets(1) Set xlShtSrc = Workbooks("FE.xlsx").Sheets(1) Code:
Set wkb = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\Sheet1.xlsx") Set wkb1 = Workbooks.Open("C:\Users\rahulkumar.patel\Desktop\FE.xlsx") Set xlShtTgt = wkb .Sheets(1) Set xlShtSrc = wkb 1.Sheets(1)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Hi,
I believe if the OP answered my questions there may have been less confusion. |
#15
|
||||
|
||||
As, this has solved my problems, can be marked as solved.
|
Tags |
excel vba, excel vba xml import loop |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extract Data From Text file based on Pattern | PRA007 | Word VBA | 13 | 11-01-2015 11:20 PM |
Powerpoint show specific data based on selection | Christov | PowerPoint | 1 | 05-14-2015 09:35 PM |
How to Extract Data from table based on pattern. | PRA007 | Word Tables | 4 | 03-17-2015 11:05 PM |
Excel VBA Macro - Deleting Specific Data based on criteria | MD011 | Excel Programming | 3 | 12-10-2014 02:15 AM |
Mouseover cell to indicate mouse pointer location based on Specific Row/Column values | bolandk | Excel | 1 | 05-15-2014 08:22 AM |