Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Macros to make Sheet 1 Look like Sheet 2 in EXCEL

  1. #1
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286

    Macros to make Sheet 1 Look like Sheet 2 in EXCEL

    hello, i know this is an access forum, and ive tried posting on the microsoft office forum but have not been able to get any insight. was wondering if anyone can help me with this.



    was wondering if its possible to re arrange sheet 1 to look like sheet 2 in the file attached. So something like:

    If any text string in column A looks like "chain" then put this into cell 1 Column A<--so running something like this should put "Chain: 12345678" into the first cell in column A since thats the only text value on the excel doc with the word "chain in it" (See sheet 2 for reference".

    The way my users will use the macros is detailed below:


    1)user receives a word doc with contents
    2)user opens new instance of excel and paste contents from word to excel
    3)user hits macros button and data reorganizes and gets rearranged to look like the example in sheet 2.

    ^i hope im a little more clear, sorry if im not but thanks for the help!!!goodsample.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,362
    Wouldn't be easy, mostly because each row of Sheet1 is a single string of text with multiple data elements within the string. Breaking this up will be very complicated code and that depends on consistency of the Word document structure.

    Why is this data in a Word document? Might be easy to extract data from a Word formfill document.
    Review:
    http://office.microsoft.com/en-us/wo...010030746.aspx
    http://www.techrepublic.com/blog/mso...ext-wizard/694
    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.

  3. #3
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    hi june! thanks for chiming in! yea id thought that would be the case. the logic i was thinking was for the code to look for a string that matches the string im looking for such as "chain" and then from there, only extract everything after "chain" and stop when you get to a space or maybe more than two spaces? That way it encapsulates "Chain" as well as the values after it...thats just an idea not exactly how to write that in code


    *also, it comes to us from another company in word format. the format of the document stays constant and unfortunately not something i can change..

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    Since this is a forum on Access, I sent you a PM about the Excel code.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,362
    Yes, that would be basic logic I had in mind.

    Let us know if ssanfu provided a working solution.
    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.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    How close is this? (The code is not pretty, but it works )
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    thanks so much ssanfu, you are a life saver! ive also attached another version of the raw data like youve asked, it is alright if you cannot get this version to work the same as what you did with the the first version. this version may be missing some of the fields that you see in sheet 2 but its okay because if its missing we just request the sender to send us the missing fields. thanks!

    ill look at your zip file above in about an hour or so, just have to finish some leftover work really quick.


    sample.zip

  8. #8
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    sanfu, i just tried running your macros and it works amazing!!! thanks so much

  9. #9
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    hi steve,

    for some reason when im copying from the raw data file and parsing into excel, and run the macros it does not not display the output values. it just shows:


    merchant_
    merchant_
    merchant_
    merchant_

    etc.

    I think it has to do with the fact that i did not provide the exact raw data file which i apologize for. I have attached the actual raw data file that my users will be parsing from to the excel sheet to run the macros.raw file.zip

  10. #10
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    i realized the data i had originally included in SHEET 1 does not look exactly like the data from the raw file i just attached, so the code is probably looking at the wrong spots...sorry!

  11. #11
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    i was able to get the code below from another forum, this looks like it works after ive parsed the data fromt he raw file into excel. however, it doesnt remove the original data in sheet 1, how would i get it to delete all original data in the macros? It also does not include the "merchant_" prefix in front of the fields, is there a way to modify the code below to do so?



    Sub Macro1() Range("A:B").EntireColumn.Insert shift:=xlToRight Range("A1").Value = "Chain:" Range("A2").Value = "BIN:" Range("A3").Value = "MCC/SIC:" Range("A4").Value = "City:" Range("A5").Value = "Country:" Range("A6").Value = "Currency Code:" Range("A7").Value = "Merchant Number:" Range("A8").Value = "Location Number:" Range("A9").Value = "Merchant Name:" Range("A10").Value = "State:" Range("A11").Value = "Store Number:" Range("A12").Value = "Phone Number:" Range("A13").Value = "V Number:" Range("A14").Value = "Postal Code:" Range("A15").Value = "Terminal#:" For i = 1 To 15 Set oString = Range("C1:C1000").Find(Range("A" & i).Value, lookat:=xlPart) oStartPosition = WorksheetFunction.Find(Range("A" & i).Value, oString.Value) + Len(Range("A" & i).Value) Range("A" & i).Value = Range("A" & i).Value & " " & Trim(Mid(oString.Value, oStartPosition, 19)) Next Columns("A:B").EntireColumn.AutoFit End Sub

  12. #12
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    sorry, heres the reformatted version:


    Code:
    Sub Macro1()
    
        Range("A:B").EntireColumn.Insert shift:=xlToRight
        
        Range("A1").Value = "Chain:"
        Range("A2").Value = "BIN:"
        Range("A3").Value = "MCC/SIC:"
        Range("A4").Value = "City:"
        Range("A5").Value = "Country:"
        Range("A6").Value = "Currency Code:"
        Range("A7").Value = "Merchant Number:"
        Range("A8").Value = "Location Number:"
        Range("A9").Value = "Merchant Name:"
        Range("A10").Value = "State:"
        Range("A11").Value = "Store Number:"
        Range("A12").Value = "Phone Number:"
        Range("A13").Value = "V Number:"
        Range("A14").Value = "Postal Code:"
        Range("A15").Value = "Terminal#:"
    
        For i = 1 To 15
            Set oString = Range("C1:C1000").Find(Range("A" & i).Value, lookat:=xlPart)
            oStartPosition = WorksheetFunction.Find(Range("A" & i).Value, oString.Value) + Len(Range("A" & i).Value)
            Range("A" & i).Value = Range("A" & i).Value & " " & Trim(Mid(oString.Value, oStartPosition, 19))
        Next
        Columns("A:B").EntireColumn.AutoFit
        
    End Sub

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    The line in the word doc that has "Phone Number" has two tabs, moving the "Contact Name" right two columns which causes the code to bomb. If I change the two tabs to spaces, then paste the lines from the "Date/Time" line down to "Networks and Sharing Groups" and run the code, all is good. I'll work on it over the weekend..... I'll have to come up with a better method.

    In the mean time, replace the current code with the code in the attached text file. Let me know
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,362
    I used Steve's original code (not as complex as I expected) and modified it to get this working version with the posted Word document. I put the code in Sheet2 module.
    Code:
    Sub Macro1()
    Dim i As Integer, oString As String, oSearch As String, oStartPosition As Integer
        Range("A1").Value = "Chain:"
        Range("A2").Value = "BIN:"
        Range("A3").Value = "MCC/SIC:"
        Range("A4").Value = "City:"
        Range("A5").Value = "Country:"
        Range("A6").Value = "Currency Code:"
        Range("A7").Value = "Merchant Number:"
        Range("A8").Value = "Location Number:"
        Range("A9").Value = "Merchant Name:"
        Range("A10").Value = "State:"
        Range("A11").Value = "Store Number:"
        Range("A12").Value = "Phone Number:"
        Range("A13").Value = "V Number:"
        Range("A14").Value = "Postal Code:"
        Range("A15").Value = "Terminal#:"
        For i = 1 To 15
            oSearch = Range("A" & i).Value
            oString = Sheet1.Range("A1:A1000").Find(oSearch, lookat:=xlPart)
            oStartPosition = InStr(oString, oSearch) + Len(oSearch)
            oString = Trim(Mid(oString, oStartPosition))
            oString = Trim(Left(oString, IIf(InStr(oString, "  ") = 0, Len(oString), InStr(oString, "  "))))
            Range("B" & i).Value = oString
        Next
        Columns("A:B").EntireColumn.AutoFit
        Columns("C:B").HorizontalAlignment = xlRight
    End Sub
    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.

  15. #15
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    ^thanks June. i asked steve this question but seems like he may be done for the week and dont want to burden him with more work hah, was wondering if you had a suggestion on this.

    what is the vb code to trim a variable down?
    ex: in steves code sChain = Chain: 00001. Im planning to create a 2nd variable called sChain2 which will trim sChain down to just 00001. Thanks!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. From Recordset to excel sheet
    By mark71 in forum Programming
    Replies: 1
    Last Post: 12-10-2012, 03:40 PM
  2. Import excel sheet data into Ms-access using VBA macros
    By gokul1242 in forum Import/Export Data
    Replies: 2
    Last Post: 10-02-2012, 04:39 AM
  3. Exporting to Specific Excel Sheet
    By unrealtb in forum Access
    Replies: 2
    Last Post: 01-24-2012, 10:32 PM
  4. Replies: 6
    Last Post: 10-17-2011, 11:16 PM
  5. Import Excel sheet with query
    By tpcervelo in forum Queries
    Replies: 1
    Last Post: 08-03-2010, 07:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums