如何为Adword格式化excel数据

Question

我正在寻找帮助，使我的Excel工作表到一个特定的格式，以导入到Adwords。我有一个包含以下格式数据的工作表：

​

​

我需要将其转换为以下格式：

​

​

让这件事变得棘手的是每个SKU有3行代码。一行包含广告组创建，下一行是文本广告创建，然后关键字和bid在下一行。

有没有人能帮我实现这个目标？我将不胜感激。

谢谢!

Answer 1

希望这能帮到你。

(对于所有人)

在工作表中，设置可在代码中看到的范围名称

ReportAddress
theSiteShort
theSiteLong
First
Second

并使用它在书中引用。(这只是为了不使用$A$1)

关注评论...

'Take the report and store the address in the
'Range("ReportAddress") to use it later...
Sub takeReport()
    'this is to take the reporte and store the address in Cells C6
    'that has the name ReportAddress as you can see
    Dim i
    Dim FD As FileDialog 'to take files
    Set FD = Application.FileDialog(msoFileDialogFilePicker)

    With FD 'invoke the file dialog
        If .Show = -1 Then 'if you take any file (1 or more)
            For Each i In .SelectedItems 'for each file do this...
                Range("ReportAddress").Value = i 'and as you can see, just take one file...
            Next i
        End If
    End With
End Sub


'to create the report
Sub do_Report()
    Dim dirReport As String
    Dim wrkData As Workbook
    Dim shtData As Worksheet
    Dim tmpReport As Workbook
    Dim shtReport As Worksheet
    Dim skuNum() 'to store the data
    Dim skuName()
    Dim vendorPartNum()
    Dim manufacturer()
    Dim r 'var for rows
    Dim c
    Dim col
    Dim fil
    Dim i
    Dim counter

    Dim shortSite As String
    Dim longSite As String
    Dim first As String
    Dim second As String

    'this is to a better handle of the data...
    shortSite = Range("theSiteShort").Value
    longSite = Range("theSiteLong").Value
    first = Range("First").Value
    second = Range("Second").Value

    Workbooks.Open Range("ReportAddress").Value 'open the workbook with the data
    Set wrkData = ActiveWorkbook
    Set shtData = ActiveSheet 'here we can fail, because if the xls has more than 1 sheet and that sheet
                              'is not the sheet we need, could fail...

    Workbooks.Add 'create a new workbook (CTRL+N)
    Set tmpReport = ActiveWorkbook 'Store it here
    Set shtReport = ActiveSheet 'as well the active sheet

    'headlines
    Range("A1").FormulaR1C1 = "Ad Group"
    Range("B1").FormulaR1C1 = "Bid"
    Range("C1").FormulaR1C1 = "Headline"
    Range("D1").FormulaR1C1 = "Desc 1"
    Range("E1").FormulaR1C1 = "Desc 2"
    Range("F1").FormulaR1C1 = "Display URL"
    Range("G1").FormulaR1C1 = "Final URL"
    Range("H1").FormulaR1C1 = "Keyword"

    wrkData.Activate 'got to the data report
    shtData.Activate 'activate the sheet with the data, remember the comment!

    r = Range("A1").End(xlDown).Row 'find the last row of data
    c = Range("A1").End(xlToRight).Column 'As well the last column

    For col = 1 To c 'well may is always the same qty of columns, but i like to count!!!
        For fil = 2 To r 'for every row
            Select Case col 'depends in which column is...
                Case 1 'the first one use SkuNum... and so on... (This are the columns of the data source)
                    ReDim Preserve skuNum(1 To fil - 1)
                    skuNum(fil - 1) = Cells(fil, col)
                Case 2
                    ReDim Preserve skuName(1 To fil - 1)
                    skuName(fil - 1) = Cells(fil, col)
                Case 3
                    ReDim Preserve vendorPartNum(1 To fil - 1)
                    vendorPartNum(fil - 1) = Cells(fil, col)
                Case 4
                    ReDim Preserve manufacturer(1 To fil - 1)
                    manufacturer(fil - 1) = Cells(fil, col)
                Case Else
                    'do nothing 'just in case...
            End Select
        Next fil
    Next col

    tmpReport.Activate 'go to the new book, that is our final report
    shtReport.Activate 'go to the sheet... just in case again... 'This line could be deletec

    counter = 0 'a counter (index) for the vars()
    For i = 1 To (r * 3) Step 3 '
        'i got r lines and i need to put every 3 lines,
        'then, that why I use Step 3 = (every 3th line), and that 3 * r.
        counter = counter + 1
        If counter > UBound(skuName) Then 'if the counter is bigger that the
                                          'qty of vars inside SkuName (or any other)
            Exit For 'get out the for loop!
        End If
        'here is the magic... almost...
        Cells(i + 1, 1).Value = manufacturer(counter) & " - " & vendorPartNum(counter)
        Cells(i + 1, 2).Value = first
        Cells(i + 2, 1).Value = manufacturer(counter) & " - " & vendorPartNum(counter)
        Cells(i + 2, 3).Value = manufacturer(counter) & " - " & vendorPartNum(counter) & " On Sale"
        Cells(i + 2, 4).Value = skuName(counter)
        Cells(i + 2, 5).Value = "Shop " & manufacturer(counter) & " now."
        Cells(i + 2, 6).Value = shortSite & manufacturer(counter)
        Cells(i + 2, 7).Value = longSite & skuNum(counter)
        Cells(i + 3, 1).Value = manufacturer(counter) & " - " & vendorPartNum(counter)
        Cells(i + 3, 2).Value = second
        Cells(i + 3, 8).Value = "+" & manufacturer(counter) & " +" & vendorPartNum(counter)
    Next i
    Cells.EntireColumn.AutoFit 'Autofit all columns...
    MsgBox "Ready!" 'Finish!
End Sub