Affiliate Worksheet Help

I would like some help with a couple of worksheets... I hope someone can help me.

This is what i need to do.

Worksheet 1

All the data I need is on worksheet 1 this is just some of the data for example,

PROGRAMNAME PROGRAMURL NAME KEYWORDS DESCRIPTION SKU BUYURL IMPRESSIONURL
Bejool http://www.bejool.com Bejool.com Sterling Silver Hoop Earrings Sterling Silver Hoop Earrings Sterling Silver Hoop Earrings Sterling Silver http://www.jdoqocy.com/click-

Worksheet 2

SKU post_title post_content post_excerpt type code starts expires url link featured packageID image images category

What I need to do is map the fields from worksheet 1 to worksheet 2, and move all the data in most cases about 50,000 line.

So I would need all the data from PROGRAMNAME on (worksheet 1) to be inserted under post_title on (worksheet 2)

I hope this makes since.

Thank You
Robert

I inserted the data just like I would need it for importing into my website.

But here it is again in case it not to confusing.
PROGRAMURL = url
CATALOGNAME = category
NAME = post_title (and) post_excerpt
KEYWORDS = tags
DESCRIPTION = post_content
SKU = SKU
BUYURL = link
IMAGEURL = image (and) images
STARTDATE = starts
ENDDATE = expires

Well I keep a copy and ftp it into the server, then I go to the admin panel of my script and upload it.

Sorry, Yes...

That looks great, will I need to change the name on the code for each of the files.. since (CPO Outlet) was just 1 of 1250 different file names.

I'm not sure I understand what it should do, I tried it a few times and nothing. Should I open the file with all the data or should I open both; I'm not sure.

This is the folder that I keep all the data in. C:\Users\Robert\Desktop\CJ database files\CJ text deals\Extracted Files

the names of the files are all different here are the first 4 in the folder
1st in video
4allmemory
101Phones
123Inkjets

also if you would like I live in Modesto I can call you or I use Teamviewer for remote access.

"If you need we can make Excel prompt you to select the file to open and import from, too, so you have variable control of that without editing the macro. "

I like this option, to be able to select each file.

You are awesome... it works great

I don't have much, but can I donate or buy you lunch? via paypal

Since you have opened up my eyes about what Excel can do, dealing with that same file system the columns that do not have data to import I have to enter manually for example

Column E (type) I can put either Coupon or offer
Column V (post_type) needs the word post
Column W (post_status) should have the word publish

Can this also be done?

Jerry,

I have run across another issue that I think you can help me with, some of my files are larger then 65536 line can we write into the macro that if it is more then 65536 then it will split it up into multi workbooks that are say 30,000 each or something like that.

Thank You,

That code is for another macro.

The new macro needs something similar

1) Column (O) needs to say "Active" from O2: to the end of the sheet.
2) Column (P) needs to say "Yes" from P2: to the end of the sheet.
3) Column (AK) needs to have either the number (1)
4) Column (AL) needs to have either the number (1)
5) Column (AP) needs to have options (Merchant Coupon)

So if I follow you it should look like this
_______________________________________________________
wb1.Close False

With wsNEW
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("O2:O" & LR).Value = "Active"
.Range("P2:P" & LR).Value = "Yes"
.Range("AK2:AK" & LR).Value = "1"
.Range("AL2:AL" & LR).Value = "1"
.Range("AP2:AP" & LR).Value = "Merchant Coupon"


End With
_______________________________________________________

"needs to have the options (Merchant Coupon)", I had 3 options but I forgot the last time you helped me you stated that I would not be able to pick the option I needed.

Here is the sheet after i run the macro.

OK, Let me start from scratch sheet 1 will be the file I need to convert, sheet 2 will be the import fields, sheet 3 will be the macro code, and there is a image of the idea if it is possible.

What I need is for

Cell (O2) to have the word "Active"
Cell (P2) to have a choice of "Yes" or "No" --- Maybe a box could pop up and I have to pick one.
Cell (AK2) to have a choice of "Yes" or "No" ---
Cell (AL2) to have a choice of "Yes" or "No" ---
Cell (AP2) to have a choice of "Deals" , "Offer" or "Merchant Coupon" ---

If it is possible to have a box pop up with check box's (see image)

I hope this helps and thank you very much



**** ALSO EVERY TIME I OPEN EXCEL THE FILE (PERSONAL) OPENS SO I HAVE TO CLOSE THAT FILE AND CLICK NEW ***
Can you please tell me how to disable that.excel-pop-up-box.gifSheet 1.xlsSheet 2.xlsm

Is this everything I need or just an example?

I ran it the way it is and I get a compile error with this line.

.Parent.Value = WorksheetFunction.Lookup(MsgBox("Click the button that will be the default value for Column P", vbYesNo), Array(6, 7), Array("Yes", "No")) End With

So I'm not sure what happened but nothing went into (P), nothing went into (O), nothing went into (AK), nothing went into (AL) & nothing went into (AP)...

The worksheet that needed to imported had some information in it but it was all wrong.

I'm not sure what all the commands are for so I can't try and figure it out myself. I will give it my best try and maybe you can correct my errors.

This is what I need added to the original sheet you made me.
Cell (O2) to have the word "Active"
Cell (P2) to have a choice of "Yes" or "No" --- Maybe a box could pop up and I have to pick one.
Cell (AK2) to have a choice of "Yes" or "No" ---
Cell (AL2) to have a choice of "Yes" or "No" ---
Cell (AP2) to have a choice of "Deals" , "Offer" or "Merchant Coupon" ---


Here is the one you made me before. I changed a few thing to work with my new format but this part works great.
Sub PHPIMPORT()
'Imports a file you select into the activesheet
Dim wb1 As Workbook, wsNEW As Worksheet
Dim LR As Long, fNAME As String

If MsgBox("Process data into the activesheet?", _
vbYesNo, "Confirm") = vbNo Then Exit Sub
Set wsNEW = ActiveSheet

fNAME = Application.GetOpenFilename("Microsoft Office Excel Files (.xls),.xls")
If fNAME = "False" Then Exit Sub

On Error Resume Next
Set wb1 = Workbooks.Open(fNAME)

wsNEW.UsedRange.Offset(1).ClearContents

With wb1.Sheets("Sheet1")
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("E2:E" & LR).Copy wsNEW.Range("B2") 'NAME>TITLE
.Range("G2:G" & LR).Copy wsNEW.Range("C2") 'DESCRIPTION>DESCRIPTION
.Range("E2:E" & LR).Copy wsNEW.Range("BD2") 'NAME>MCOUPON_TAG
.Range("B2:B" & LR).Copy wsNEW.Range("D2") 'PROGRAMURL>URL
.Range("R2:R" & LR).Copy wsNEW.Range("BC2") 'BUYURL>AFFURL
.Range("R2:R" & LR).Copy wsNEW.Range("BE2") 'BUYURL>MCOUPON_URL
.Range("T2:T" & LR).Copy wsNEW.Range("BI2") 'IMAGEURL>MCOUPON_LOGO
.Range("C2:C" & LR).Copy wsNEW.Range("L2") 'CATALOGNAME>CATEGORY_ID
.Range("F2:F" & LR).Copy wsNEW.Range("AT2") 'KEYWORDS>META_KEYWORDS
.Range("G2:G" & LR).Copy wsNEW.Range("BG2") 'DESCRIPTION>MCOUPON_DESC
.Range("N2:N" & LR).Copy wsNEW.Range("AW2") 'SALEPRICE>PRICE
.Range("T2:T" & LR).Copy wsNEW.Range("AX2") 'IMAGEURL>IMAGE
.Range("C2:C" & LR).Copy wsNEW.Range("AZ2") 'CATALOGNAME>STORE_ID
End With


Now I will try and do as much as I can.


wb1.Close False

LR = Range("A" & Rows.Count).End(xlUp).Row

Range("O2:O" & LR).Value = "Active"

Range("P2:P" & LR).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Yes, No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
.Parent.Value = WorksheetFunction.Lookup(MsgBox("Click the button that will be the default value for Column P", vbYesNo), Array(6, 7), Array("Yes", "No"))

End With

Range("AK2:AK" & LR).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Yes, No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
.Parent.Value = WorksheetFunction.Lookup(MsgBox("Click the button that will be the default value for Column AK", vbYesNo), Array(6, 7), Array("Yes", "No"))

End With

Range("AL2:AL" & LR).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Yes, No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
.Parent.Value = WorksheetFunction.Lookup(MsgBox("Click the button that will be the default value for Column AL", vbYesNo), Array(6, 7), Array("Yes", "No"))

End With

Range("AP2:AP" & LR).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Deals, Offer, Merchant Coupon"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
.Parent.Value = WorksheetFunction.Lookup(MsgBox("Click the button that will be the default value for Column P" _
& vbLf & vbLf & "Yes = 'Deals'" & vbLf & "No = 'Offer'" & vbLf & "Cancel = 'Merchant Coupon'", vbYesNoCancel, "Choose"), _
Array(2, 6, 7), Array("Merchant Coupon", "Deals", "Offer"))
End With

End Sub


OK; so this is what I have done, I will try it but, I don't think it's going to work.

OK, So I ran the macro and this is what I got in return.

If you look at the field names it removed the name and added what the macro was told to bu only on 2 rows not the complete worksheet, this worksheet had 16,000 lines I cut it to just 2 so you could see it.

OK, we are almost there. some of it is perfect and some I just need to make changes to.

1) Column (O) nothing happens.
2) Column (P) Needs to have the options but, I need to choose the option before the import and then it places the same answer on all the rows.
3) Column (AK, AL) are just fine this way I can pick the ones I need to have as Featured.
4) Column (AP) just like column (p) once I choose the option I want then it add it to all the rows. This is because some merchants offer coupons some offer deals, and so on

OK, you did it again, everything works perfect.

I'm back, and I have another change going on with that same sheet.

This is what I'm trying to do.

With this section, can the box pop up; and instead of having "Yes", "No", & "Cancel" can I have a choice of buttons to push or a check box with these options. I will only need to choose 1 option then press a button that says "Enter" and that will enter that option all the way down the sheet.

• Hot Product
• Great Deals
• Promotional Offers
• Printable Coupons
• Grocery Coupons
• Free Products
• Sale/Discount
• Free Shipping
• Coupons
• Sweepstakes
• NONE OF THE ABOVE (THIS WILL JUST CLOSE THE BOX WITHOUT DOING ANYTHING.)

With Range("L2:L" & LR).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Deals, Offer, Merchant Coupon"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
.Parent.Value = WorksheetFunction.Lookup(MsgBox("SELECT LINK TYPE" _
& vbLf & vbLf & "Yes = 'Deals'" & vbLf & "No = 'Offer'" & vbLf & "Cancel = 'Merchant Coupon'", vbYesNoCancel, "Choose"), _
Array(2, 6, 7), Array("Merchant Coupon", "Deals", "Offer"))

End With


Once again thank you for all your help.

Robert Threadgill
http://wegatta.com