Thread: Affiliate Worksheet Help

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.