Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Read and write from excel to excel - automated

  1. #1

    Question Read and write from excel to excel - automated



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Everyone,

    Column A

    Water
    salt

    I want to take Column A from sheet 1 and create the following in another sheet. Row by row. Column A is going to have hundreds of entry and I would need to automate this process
    cat/images/[column A]/type/an_[ column A]_color.jpg
    cat/images/[column A]/type/an_[ column A]_color2x.jpg
    cat/images/[column A]/black/an_[ column A]_black.jpg
    cat/images/[column A]/black/an_[ column A]_black2x.jpg

    e.g.
    cat/images/water/type/an_water_color.jpg
    cat/images/water/type/an_water_color2x.jpg

    cat/images/water/black/an_water_black.jpg
    cat/images/water/black/an_water_black2x.jpg

    Thanks so much in advance

  2. #2
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Code:
    Sub test()
        s1 = "Sheet1"
        s2 = "Sheet2"
        Set r = Sheets(s1).Range(Sheets(s1).Cells(1, 1), Sheets(s1).Cells(Sheets(s1).Range("A1").End(xlDown).row, 1))
        
        Count = 0
        For Each c In r
            Sheets(s2).Cells(Count + 1, 1) = "cat/images/" & c.Value & "/type/an_" & c.Value & "_color.jpg"
            Sheets(s2).Cells(Count + 2, 1) = "cat/images/" & c.Value & "/type/an_" & c.Value & "_color2x.jpg"
            Sheets(s2).Cells(Count + 3, 1) = "cat/images/" & c.Value & "/black/an_" & c.Value & "_black.jpg"
            Sheets(s2).Cells(Count + 4, 1) = "cat/images/" & c.Value & "/black/an_" & c.Value & "_black2x.jpg"
            Count = Count + 4
        Next c
    End Sub

  3. #3
    Quote Originally Posted by millz View Post
    Code:
    Sub test()
        s1 = "Sheet1"
        s2 = "Sheet2"
        Set r = Sheets(s1).Range(Sheets(s1).Cells(1, 1), Sheets(s1).Cells(Sheets(s1).Range("A1").End(xlDown).row, 1))
        
        Count = 0
        For Each c In r
            Sheets(s2).Cells(Count + 1, 1) = "cat/images/" & c.Value & "/type/an_" & c.Value & "_color.jpg"
            Sheets(s2).Cells(Count + 2, 1) = "cat/images/" & c.Value & "/type/an_" & c.Value & "_color2x.jpg"
            Sheets(s2).Cells(Count + 3, 1) = "cat/images/" & c.Value & "/black/an_" & c.Value & "_black.jpg"
            Sheets(s2).Cells(Count + 4, 1) = "cat/images/" & c.Value & "/black/an_" & c.Value & "_black2x.jpg"
            Count = Count + 4
        Next c
    End Sub

    This is very very awesome thank you a lot!!!!!, but i do have another question. what if i want to put put each entry into in column C, D, E, F, G, and H in the same row instead of the same column

  4. #4
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Code:
        Count = 1
        For Each c In r
            Sheets(s2).Cells(Count, 3) = "cat/images/" & c.Value & "/type/an_" & c.Value & "_color.jpg" '3 = Column C
            Sheets(s2).Cells(Count, 4) = "cat/images/" & c.Value & "/type/an_" & c.Value & "_color2x.jpg" '4 = Column D, etc.
            Sheets(s2).Cells(Count, 5) = "cat/images/" & c.Value & "/black/an_" & c.Value & "_black.jpg"
            Sheets(s2).Cells(Count, 6) = "cat/images/" & c.Value & "/black/an_" & c.Value & "_black2x.jpg"
            Count = Count + 1
        Next c

  5. #5
    I tried it this way and it seems to work, but i decided to switch it from the same excel document to create another excel document with a specific document name and put the extracted information there, but for some reason it will not run. Can you take a look at me it for me, please?

    Sub test()
    Dim orig As Workbook
    Set orig = ActiveWorkbook


    Dim book As Workbook
    Set book = Workbooks.Add


    s1 = "Sheet1"
    s2 = "Sheet10"
    Set r = orig.Sheets(s1).Range(Sheets(s1).Cells(2, 1), orig.Sheets(s1).Cells(Sheets(s1).Range("A1").End(xlDown).Row, 1))
    Count = 1
    For Each c In r
    book.Sheets(s2).Cells(Count + 1, 1) = "" & c.Value & ""
    book.Sheets(s2).Cells(Count + 1, 2) = "" & orig.Sheets(s1).Cells(Count + 1, 2).Value & ""
    book.Sheets(s2).Cells(Count + 1, 3) = "cat/type/" & c.Value & "/option/an_" & c.Value & "_co.png"
    book.Sheets(s2).Cells(Count + 1, 4) = "cat/type/" & c.Value & "/option/an_" & c.Value & "_co2.png"
    book.Sheets(s2).Cells(Count + 1, 5) = "cat/type/" & c.Value & "/shade/an_" & c.Value & "_shade.png"
    book.Sheets(s2).Cells(Count + 1, 6) = "cat/type/" & c.Value & "/shade/an_" & c.Value & "_shade2.png"
    book.Sheets(s2).Cells(Count + 1, 7) = "cat/type/" & c.Value & "/shade/an_" & c.Value & "_shade.png"
    book.Sheets(s2).Cells(Count + 1, 8) = "cat/type/" & c.Value & "/shade/an_" & c.Value & "_shade2.png"
    book.Sheets(s2).Cells(Count + 1, 9) = "" & orig.Sheets(s1).Cells(Count + 1, 3).Value & ""
    book.Sheets(s2).Cells(Count + 1, 10) = "" & orig.Sheets(s1).Cells(Count + 1, 4).Value & ""
    book.Sheets(s2).Cells(Count + 1, 11) = "" & orig.Sheets(s1).Cells(Count + 1, 5).Value & ""
    Count = Count + 1
    book.SaveAs ("destin.xls")
    Next c


    End Sub

    Thanks

  6. #6
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Code:
    Set r = orig.Sheets(s1).Range(Sheets(s1).Cells(2, 1), orig.Sheets(s1).Cells(Sheets(s1).Range("A1").End(xlDown).Row, 1))

    You need to set the workbook name for all the sheets
    Code:
    Set r = orig.Sheets(s1).Range(orig.Sheets(s1).Cells(2, 1), orig.Sheets(s1).Cells(orig.Sheets(s1).Range("A1").End(xlDown).Row, 1))


    Next, are you sure in your newly added workbook has a worksheet named "Sheet10" ?

  7. #7
    ooooh i had that, but changed it for some odd reason. Now its works great!!! Thanks!!!
    I have two more questions:
    -I wanted to put a header for each of the following entries when i create the destination file like this ( keeping in mind that these headers don't exist in the source file):
    Title 1
    book.Sheets(s2).Cells(Count + 1, 1) = "" & c.Value & ""
    Title 2
    book.Sheets(s2).Cells(Count + 1, 2) = "" & orig.Sheets(s1).Cells(Count + 1, 2).Value & ""
    Title 3
    book.Sheets(s2).Cells(Count + 1, 3) = "cat/type/" & c.Value & "/option/an_" & c.Value & "_co.png"
    Title 4
    book.Sheets(s2).Cells(Count + 1, 4) = "cat/type/" & c.Value & "/option/an_" & c.Value & "_co2.png"

    -And the other is can i run this script without opening excel - maybe in a form type or a standalone program

  8. #8
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Put them outside of the loop.
    Code:
    book.Sheets(s2).Cells(1 , 1) = "Title 1"
    book.Sheets(s2).Cells(1 , 2) = "Title 2"
    book.Sheets(s2).Cells(1 , 3) = "Title 3"
    book.Sheets(s2).Cells(1 , 4) = "Title 4"
    
    For Each ....
         ....
    

    I have no idea how to do it out of Excel, that's out of my knowledge unfortunately.

  9. #9
    Quote Originally Posted by millz View Post
    Put them outside of the loop.
    Code:
    book.Sheets(s2).Cells(1 , 1) = "Title 1"
    book.Sheets(s2).Cells(1 , 2) = "Title 2"
    book.Sheets(s2).Cells(1 , 3) = "Title 3"
    book.Sheets(s2).Cells(1 , 4) = "Title 4"
    
    For Each ....
         ....
    
    I have no idea how to do it out of Excel, that's out of my knowledge unfortunately.
    No problem!!!!! Thanks a billion!!!

    I promise this time one more thing that i completely missed:
    In the source file i have two columns and i want to create an if statement that says --> If column A is there( meaning numbers were entered), but column B is blank, then display Column A value else if Column A is there and Column B is there then display Column B's value.....i want to do this row by row. Does it make sense?

    Source
    Column A Column B
    F325RFW4 YH56748
    D5645E578 YH56748
    234RFDS34 YH56748


    Column's B value is the same and that never changes, but column A values does.

  10. #10
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Something like this?
    Code:
    For Each
        c = iif(c <> "" and c.offset(0,1) = "",c,c.offset(0,1))
        book.Sheets(s2).Cells(Count + 1, 1) = c.Value
    

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •