Read and write from excel to excel - automated

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
0
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
 

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
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
 

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
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


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
 

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
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
 

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
0
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
 

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
Code:
[COLOR=#333333]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:
[/COLOR][COLOR=#333333]Set r = orig.Sheets(s1).Range([B]orig.[/B]Sheets(s1).Cells(2, 1), orig.Sheets(s1).Cells([/COLOR][B]orig.[/B][COLOR=#333333]Sheets(s1).Range("A1").End(xlDown).Row, 1))

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

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
0
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
 

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
Put them outside of the loop.
Code:
[B]book.Sheets(s2).Cells(1 , 1) = "[/B][B]Title 1"[/B]
[B]book.Sheets(s2).Cells(1 , 2) = "[B]Title 2"[/B][/B]
[B]book.Sheets(s2).Cells(1 , 3) = "[B]Title 3"[/B][/B]
[B]book.Sheets(s2).Cells(1 , 4) = "[B]Title 4"
[/B][/B][B][B][B][B]
For Each ....
     ....
[/B][/B][/B][/B]I have no idea how to do it out of Excel, that's out of my knowledge unfortunately.
 

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
0
Put them outside of the loop.
Code:
[B]book.Sheets(s2).Cells(1 , 1) = "[/B][B]Title 1"[/B]
[B]book.Sheets(s2).Cells(1 , 2) = "[B]Title 2"[/B][/B]
[B]book.Sheets(s2).Cells(1 , 3) = "[B]Title 3"[/B][/B]
[B]book.Sheets(s2).Cells(1 , 4) = "[B]Title 4"
[/B][/B][B][B][B][B]
For Each ....
     ....
[/B][/B][/B][/B]
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 AColumn B
F325RFW4YH56748
D5645E578YH56748
234RFDS34YH56748


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

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
Something like this?
Code:
[COLOR=#333333]For Each
    c = iif(c <> "" and c.offset(0,1) = "",c,[/COLOR][COLOR=#333333]c.offset(0,1))[/COLOR][COLOR=#333333]
[/COLOR][COLOR=#333333]    book.Sheets(s2).Cells(Count + 1, 1) = c.Value[/COLOR][COLOR=#333333]
[/COLOR]
 

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
0
Something like this?
Code:
[COLOR=#333333]For Each
    c = iif(c <> "" and c.offset(0,1) = "",c,[/COLOR][COLOR=#333333]c.offset(0,1))[/COLOR][COLOR=#333333]
[/COLOR][COLOR=#333333]    book.Sheets(s2).Cells(Count + 1, 1) = c.Value[/COLOR][COLOR=#333333]
[/COLOR]

Something like that, but where would i put it
 

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
0
Keeping in mind that
I have bunch of values in column E and its rows and "422rf3" in Column F, but not in all rows ( some are blank) because i want that if both Column E and F have the values in it i want to take the value of whatever is in that Column E row and display it in column K of the new excel sheet that im creating. If a particualr row is blank in Column F that i want to take the value that is in column E's row
 

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
Well, I think something is wrong with your logic?

if Both E and F has values, you take E
if F is blank, you take E

I see no difference from taking E all the time? UNLESS:

if Both E and F has values, you take E
if E is blank, you take F


Code:
[COLOR=#333333]book.Sheets(s2).Cells(Count + 1, 11) = iif(orig.Sheets(s1).Cells(Count + 1, 5).Value = "",[/COLOR][COLOR=#333333]orig.Sheets(s1).Cells(Count + 1, 6).Value[/COLOR][COLOR=#333333],[/COLOR][COLOR=#333333]orig.Sheets(s1).Cells(Count + 1, 5).Value[/COLOR][COLOR=#333333])
[/COLOR]
 

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
0
Well, I think something is wrong with your logic?

if Both E and F has values, you take E
if F is blank, you take E

I see no difference from taking E all the time? UNLESS:

if Both E and F has values, you take E
if E is blank, you take F


Code:
[COLOR=#333333]book.Sheets(s2).Cells(Count + 1, 11) = iif(orig.Sheets(s1).Cells(Count + 1, 5).Value = "",[/COLOR][COLOR=#333333]orig.Sheets(s1).Cells(Count + 1, 6).Value[/COLOR][COLOR=#333333],[/COLOR][COLOR=#333333]orig.Sheets(s1).Cells(Count + 1, 5).Value[/COLOR][COLOR=#333333])[/COLOR]

I see what you're saying. Let me see if i can fix up my logics

If E and F has values, then i want to take F
If E is blank i want to take F
If F is blank i want to take E

I want the final value to only display in Column K in the document workbook
Does that make better sense?
 
Last edited:

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
You already have a line that takes E and puts into K right? Just change to this
Code:
[COLOR=#333333]book.Sheets(s2).Cells(Count + 1, 11) = iif(orig.Sheets(s1).Cells(Count + 1, 5).Value <> "" and [/COLOR][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 6).Value <> ""[/FONT][COLOR=#333333][FONT=Verdana],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 6).Value[/FONT][/COLOR][COLOR=#333333][FONT=Verdana],iif([/FONT][/COLOR][COLOR=#333333][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 5).Value = "",[/FONT][/COLOR][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 6).Value[/FONT][COLOR=#333333][FONT=Verdana],[/FONT][/COLOR][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 5).Value[/FONT][FONT=Verdana])[/FONT]
 

tonx

New member
Joined
Aug 21, 2013
Messages
11
Reaction score
0
Points
0
You already have a line that takes E and puts into K right? Just change to this
Code:
[COLOR=#333333]book.Sheets(s2).Cells(Count + 1, 11) = iif(orig.Sheets(s1).Cells(Count + 1, 5).Value <> "" and [/COLOR][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 6).Value <> ""[/FONT][COLOR=#333333][FONT=Verdana],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 6).Value[/FONT][/COLOR][COLOR=#333333][FONT=Verdana],iif([/FONT][/COLOR][COLOR=#333333][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 5).Value = "",[/FONT][/COLOR][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 6).Value[/FONT][COLOR=#333333][FONT=Verdana],[/FONT][/COLOR][FONT=Verdana]orig.Sheets(s1).Cells(Count + 1, 5).Value[/FONT][FONT=Verdana])[/FONT]

For some reason this is giving me a syntax error and whenever it hits a blank row in Column A the code stops and doesnt go through the rest :(
 
Top