Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27

Macros to make Sheet 1 Look like Sheet 2 in EXCEL

  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,400
    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.

  2. #17
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    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 ":"

  3. #18
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,400
    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.

  4. #19
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    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

  5. #20
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,400
    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.

  6. #21
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    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

  7. #22
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,400
    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.

  8. #23
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    ^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

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,400
    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.

  10. #25
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    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)

  11. #26
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,400
    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.

  12. #27
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    thanks june, was able to get it working.

Page 2 of 2 FirstFirst 12
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