I think the version I offered does that.
I think the version I offered does that.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
hey june, thanks for looking at it. however its not coming out like the exact way i need it to. your code parses out the values, but im looking for the reverse, where it parses out the name and leaves the value.
steves code is perfect for what i need, except how would i trim the value of a variable using another variable
ex: string 2 = trim value of string 1 to show eveyrhting after ":"
Is this what you want?
sChain = "Chain: 00001"
sChain2 = Trim(Mid(sChain, InStr(sChain, " ")))
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
hello June, your code works perfectly! thanks
Steve, here is the 2nd version of the raw file, this would also need to be formatted to look like how you have set up the code. the only problem is this one has two columns, one for values and for the fields, so its a little bit different and your original code doesnt seem to apply to it...thanksrawdata2.zip
The code provided so far assumed strings of text copy/pasted from Word to Excel. This latest version of the Word document has a table, not just paragraphs of text. You can actually select the table and copy/paste from Word to Excel and the column structure will be intact. Then delete the header and subheader rows not needed.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
you are correct, this version has a table which i assume it would be easier to create a brand new macros for in excel. the problem is that our department does not have access installed nor do our users (i wish we did, because all of this would be a lot easier as i am more versed in access). because of the two different versions, im planning to just have my users switch between the two macros saved in an excel spreadsheet
What do you want the new macro to do? If users copy/paste the table from Word the data is already in columns.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
^hey june, yes thats true its in table format which is fine. however the order of the fields is not in the right order i want it to be in. i also want to attach a prefix of "merchant_" to each field. I have attached an example of how the raw data looks when copied to excel (sheet 1). In sheet 2, is how i want it to look after the macros runs. please let me know if you need more clarification. thanks!version2.zip
Here is sample code. Copy the code and change bold text for each data item.
Cells.Find(What:="Merchant Number", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheet2.Range("A1") = "merchant_Agent Number"
Sheet2.Range("B1") = ActiveCell.Offset(0, 1)
I created the Cells.Find code with the Macro Recorder. Then I wrote the last two lines. Macro Recorder is a good way to develop new VBA code in Excel. Sometimes need to refine and clean up the generated code but in this case, I didn't change the Cells.Find statement at all.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
hi june, your information and advice to use the macros recorder works great! i do have on question. for this version i have a row that looks like:
City, State ALBUQUERQUE, NM
i want it so it separates the two, one for city and one for state, as well as removing the comma. so itll look like
City ALBUQUERQUE
State NM
please let me know if you have code that can do that. i tried using the macros recorder but it simply just replaces the value and inserts a new row versus separating it at the comma and copy the value in the city cell and parse to the newly inserted State row only the value prior to the comma (which should be the state)
Just modify the suggested code.
Have one to search for City and one to search for State.
Parse the string for City:
strCity = ActiveCell.Offset(0, 1)
appropriate cell reference here = Left(strCity, InStr(strCity,",")-1)
Parse the string for State (assumes always last two characters):
appropriate cell reference here = Right(ActiveCell.Offset(0, 1), 2)
Code assumes there will always be a City and State entry, otherwise the InStr function will error.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
thanks june, was able to get it working.