I'm trying to convert my google adwords account to microsoft adcenter. I've
downloaded the csv from google and the microsoft adcenter template. I've
figured out which fields map to what and actually the fields are named the
same, they are just ordered differently.

I was hoping someone would be able to make a script to delete a couple of
rows and rearrange the columns so people can use this script to
automatically convert their google adwords to ms adcenter.

Here's what the script needs to do
(Note, when I was doing this manually, I would cut columns and insert them
into position while noticing the changes being made to column
letters/position)

Delete Rows 1-5
Delete the very last row

Rearrange rows...
Cut, column J "Current Maximum CPC" Insert into column E
Cut, column K " Keyword Destination URL" Insert into column F
Delete Column K (Column L "Destination URL" becomes column K)
Delete Column L

Thanks

I've found that recording a macro when I do things like this manually works
reasonably well.

About the only thing I see that might cause trouble is deleting that last row.

I'd use something like:

with activesheet
.cells(.rows.count,"A").end(xlup).entirerow.delete
end with

Instead of using the recorded portion. (xl will record a specific row to
delete.)

ZenMasta wrote:
>
> I'm trying to convert my google adwords account to microsoft adcenter. I've
> downloaded the csv from google and the microsoft adcenter template. I've
> figured out which fields map to what and actually the fields are named the
> same, they are just ordered differently.
>
> I was hoping someone would be able to make a script to delete a couple of
> rows and rearrange the columns so people can use this script to
> automatically convert their google adwords to ms adcenter.
>
> Here's what the script needs to do
> (Note, when I was doing this manually, I would cut columns and insert them
> into position while noticing the changes being made to column
> letters/position)
>
> Delete Rows 1-5
> Delete the very last row
>
> Rearrange rows...
> Cut, column J "Current Maximum CPC" Insert into column E
> Cut, column K " Keyword Destination URL" Insert into column F
> Delete Column K (Column L "Destination URL" becomes column K)
> Delete Column L
>
> Thanks

--

Dave Peterson

Oh, I didn't know about that ability. I'll give it a try.

I guess I would create a macro first with the code you provided, then start
recording. That way I can record the use of the delete last row macro.

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I've found that recording a macro when I do things like this manually
> works
> reasonably well.
>
> About the only thing I see that might cause trouble is deleting that last
> row.
>
> I'd use something like:
>
> with activesheet
> .cells(.rows.count,"A").end(xlup).entirerow.delete
> end with
>
> Instead of using the recorded portion. (xl will record a specific row to
> delete.)
>
> ZenMasta wrote:
>>
>> I'm trying to convert my google adwords account to microsoft adcenter.
>> I've
>> downloaded the csv from google and the microsoft adcenter template. I've
>> figured out which fields map to what and actually the fields are named
>> the
>> same, they are just ordered differently.
>>
>> I was hoping someone would be able to make a script to delete a couple of
>> rows and rearrange the columns so people can use this script to
>> automatically convert their google adwords to ms adcenter.
>>
>> Here's what the script needs to do
>> (Note, when I was doing this manually, I would cut columns and insert
>> them
>> into position while noticing the changes being made to column
>> letters/position)
>>
>> Delete Rows 1-5
>> Delete the very last row
>>
>> Rearrange rows...
>> Cut, column J "Current Maximum CPC" Insert into column E
>> Cut, column K " Keyword Destination URL" Insert into column F
>> Delete Column K (Column L "Destination URL" becomes column K)
>> Delete Column L
>>
>> Thanks
>
> --
>
> Dave Peterson

Nah...

Start a new workbook (it's where the macro will live)
Start by recording your macro into that new workbook
Open the CSV file (make that part of the macro)

Do your best to not screw it up <vbg>. Keep the mouse movements/selections to
just what you need. They'll be less things to throw away later.

You may find that you'll want to start from scratch a few times. The cleaner
the recorded code is, the fewer things will be confusing when you start to tweak
it.

Then stop recording.

Close the csv file without saving
Save your macro workbook (don't close it).
tools|macro|macro and run that macro
(or alt-f8 and run that macro)

Did it work ok?

When you have questions about what to tweak, post back with your code and where
you're having trouble.

If the name of the CSV file doesn't change, you won't have much to change. If
the CSV file's name can change, then you could modify the macro to ask what file
should be opened.


ZenMasta wrote:
>
> Oh, I didn't know about that ability. I'll give it a try.
>
> I guess I would create a macro first with the code you provided, then start
> recording. That way I can record the use of the delete last row macro.
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I've found that recording a macro when I do things like this manually
> > works
> > reasonably well.
> >
> > About the only thing I see that might cause trouble is deleting that last
> > row.
> >
> > I'd use something like:
> >
> > with activesheet
> > .cells(.rows.count,"A").end(xlup).entirerow.delete
> > end with
> >
> > Instead of using the recorded portion. (xl will record a specific row to
> > delete.)
> >
> > ZenMasta wrote:
> >>
> >> I'm trying to convert my google adwords account to microsoft adcenter.
> >> I've
> >> downloaded the csv from google and the microsoft adcenter template. I've
> >> figured out which fields map to what and actually the fields are named
> >> the
> >> same, they are just ordered differently.
> >>
> >> I was hoping someone would be able to make a script to delete a couple of
> >> rows and rearrange the columns so people can use this script to
> >> automatically convert their google adwords to ms adcenter.
> >>
> >> Here's what the script needs to do
> >> (Note, when I was doing this manually, I would cut columns and insert
> >> them
> >> into position while noticing the changes being made to column
> >> letters/position)
> >>
> >> Delete Rows 1-5
> >> Delete the very last row
> >>
> >> Rearrange rows...
> >> Cut, column J "Current Maximum CPC" Insert into column E
> >> Cut, column K " Keyword Destination URL" Insert into column F
> >> Delete Column K (Column L "Destination URL" becomes column K)
> >> Delete Column L
> >>
> >> Thanks
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Thanks, I figured out how to add the code snippet you provided to the end of
the macro I recorded.

Oh, here's the code. Hopefully someone will find it via search engine one of
these days.

Sub ConvertAdwords()
'
' ConvertAdwords Macro
' Macro recorded 10/4/2007 by (E-Mail Removed) This macro converts a
Google AdWords report into a working Microsoft Adcenter file (Follow this
URL to create the Google Report
http://forums.microsoft.com/AdCenter...5020&SiteID=44)
'
' Delete Rows 1-5
'
' Rearrange columns...
' Cut, column J "Current Maximum CPC" Insert into column E
' Cut, column K " Keyword Destination URL" Insert into column F
' Delete Column K (Column L "Destination URL" becomes column K)
' Delete Column L

'
Application.WindowState = xlNormal
Application.Left = 10
Application.Top = 35.5
Application.Width = 767.25
Application.Height = 585
Rows("1:5").Select
Selection.Delete Shift:=xlUp
Columns("J:J").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Cut
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft

' Delete the very last row
With ActiveSheet
.Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
End With

End Sub

And some of the things that you recorded don't really help the code. Try this
against a copy of your .csv file (just in case I screwed something up):

Option Explicit
Sub ConvertAdwords2()
'
' ConvertAdwords Macro
' Macro recorded 10/4/2007 by (E-Mail Removed) This macro converts a
' Google AdWords report into a working Microsoft Adcenter file (Follow this
' URL to create the Google Report
' http://forums.microsoft.com/AdCenter...5020&SiteID=44)
'
' Delete Rows 1-5
'
' Rearrange columns...
' Cut, column J "Current Maximum CPC" Insert into column E
' Cut, column K " Keyword Destination URL" Insert into column F
' Delete Column K (Column L "Destination URL" becomes column K)
' Delete Column L

Rows("1:5").Delete
Columns("J:J").Cut
Columns("E:E").Insert Shift:=xlToRight
Columns("K:K").Cut
Columns("F:F").Insert Shift:=xlToRight
Columns("K:K").Delete Shift:=xlToLeft
Columns("L:L").Delete Shift:=xlToLeft

' Delete the very last row
With ActiveSheet
.Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
End With

End Sub

And you may want to try this...

Open your macro workbook.
View|toolbars|Forms
Click on the button icon and draw a button on your worksheet
Assign your macro to that button
Change the caption to something like:
Click to Import Google AdWords CSV file

(add a few instructions to this worksheet -- or a different worksheet)

Actually, copy this code into your workbook's project and assign it to that
button.

Option Explicit
Sub ConvertAdwords3()

Dim myFileName As Variant
Dim CSVWks As Worksheet

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Set CSVWks = Workbooks.Open(Filename:=myFileName).Worksheets(1)

With CSVWks
.Rows("1:5").Delete
.Columns("J:J").Cut
.Columns("E:E").Insert Shift:=xlToRight
.Columns("K:K").Cut
.Columns("F:F").Insert Shift:=xlToRight
.Columns("K:K").Delete Shift:=xlToLeft
.Columns("L:L").Delete Shift:=xlToLeft
.Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
End With

End Sub

You'll be prompted for what .csv file to open.


ZenMasta wrote:
>
> Oh, here's the code. Hopefully someone will find it via search engine one of
> these days.
>
> Sub ConvertAdwords()
> '
> ' ConvertAdwords Macro
> ' Macro recorded 10/4/2007 by (E-Mail Removed) This macro converts a
> Google AdWords report into a working Microsoft Adcenter file (Follow this
> URL to create the Google Report
> http://forums.microsoft.com/AdCenter...5020&SiteID=44)
> '
> ' Delete Rows 1-5
> '
> ' Rearrange columns...
> ' Cut, column J "Current Maximum CPC" Insert into column E
> ' Cut, column K " Keyword Destination URL" Insert into column F
> ' Delete Column K (Column L "Destination URL" becomes column K)
> ' Delete Column L
>
> '
> Application.WindowState = xlNormal
> Application.Left = 10
> Application.Top = 35.5
> Application.Width = 767.25
> Application.Height = 585
> Rows("1:5").Select
> Selection.Delete Shift:=xlUp
> Columns("J:J").Select
> Selection.Cut
> Columns("E:E").Select
> Selection.Insert Shift:=xlToRight
> Columns("K:K").Select
> Selection.Cut
> Columns("F:F").Select
> Selection.Insert Shift:=xlToRight
> Columns("K:K").Select
> Selection.Delete Shift:=xlToLeft
> Columns("L:L").Select
> Selection.Delete Shift:=xlToLeft
>
> ' Delete the very last row
> With ActiveSheet
> .Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
> End With
>
> End Sub

--

Dave Peterson

Ps. If you don't want to see the screen flickering around, you can use
something like:

Option Explicit
Sub ConvertAdwords3()

Dim myFileName As Variant
Dim CSVWks As Worksheet

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Application.screenupdating = false

Set CSVWks = Workbooks.Open(Filename:=myFileName).Worksheets(1)

With CSVWks
.Rows("1:5").Delete
.Columns("J:J").Cut
.Columns("E:E").Insert Shift:=xlToRight
.Columns("K:K").Cut
.Columns("F:F").Insert Shift:=xlToRight
.Columns("K:K").Delete Shift:=xlToLeft
.Columns("L:L").Delete Shift:=xlToLeft
.Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
End With

Application.screenupdating = True

End Sub

Dave Peterson wrote:
>
> And some of the things that you recorded don't really help the code. Try this
> against a copy of your .csv file (just in case I screwed something up):
>
> Option Explicit
> Sub ConvertAdwords2()
> '
> ' ConvertAdwords Macro
> ' Macro recorded 10/4/2007 by (E-Mail Removed) This macro converts a
> ' Google AdWords report into a working Microsoft Adcenter file (Follow this
> ' URL to create the Google Report
> ' http://forums.microsoft.com/AdCenter...5020&SiteID=44)
> '
> ' Delete Rows 1-5
> '
> ' Rearrange columns...
> ' Cut, column J "Current Maximum CPC" Insert into column E
> ' Cut, column K " Keyword Destination URL" Insert into column F
> ' Delete Column K (Column L "Destination URL" becomes column K)
> ' Delete Column L
>
> Rows("1:5").Delete
> Columns("J:J").Cut
> Columns("E:E").Insert Shift:=xlToRight
> Columns("K:K").Cut
> Columns("F:F").Insert Shift:=xlToRight
> Columns("K:K").Delete Shift:=xlToLeft
> Columns("L:L").Delete Shift:=xlToLeft
>
> ' Delete the very last row
> With ActiveSheet
> .Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
> End With
>
> End Sub
>
> And you may want to try this...
>
> Open your macro workbook.
> View|toolbars|Forms
> Click on the button icon and draw a button on your worksheet
> Assign your macro to that button
> Change the caption to something like:
> Click to Import Google AdWords CSV file
>
> (add a few instructions to this worksheet -- or a different worksheet)
>
> Actually, copy this code into your workbook's project and assign it to that
> button.
>
> Option Explicit
> Sub ConvertAdwords3()
>
> Dim myFileName As Variant
> Dim CSVWks As Worksheet
>
> myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
> Title:="Pick a File")
>
> If myFileName = False Then
> MsgBox "Ok, try later" 'user hit cancel
> Exit Sub
> End If
>
> Set CSVWks = Workbooks.Open(Filename:=myFileName).Worksheets(1)
>
> With CSVWks
> .Rows("1:5").Delete
> .Columns("J:J").Cut
> .Columns("E:E").Insert Shift:=xlToRight
> .Columns("K:K").Cut
> .Columns("F:F").Insert Shift:=xlToRight
> .Columns("K:K").Delete Shift:=xlToLeft
> .Columns("L:L").Delete Shift:=xlToLeft
> .Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
> End With
>
> End Sub
>
> You'll be prompted for what .csv file to open.
>
> ZenMasta wrote:
> >
> > Oh, here's the code. Hopefully someone will find it via search engine one of
> > these days.
> >
> > Sub ConvertAdwords()
> > '
> > ' ConvertAdwords Macro
> > ' Macro recorded 10/4/2007 by (E-Mail Removed) This macro converts a
> > Google AdWords report into a working Microsoft Adcenter file (Follow this
> > URL to create the Google Report
> > http://forums.microsoft.com/AdCenter...5020&SiteID=44)
> > '
> > ' Delete Rows 1-5
> > '
> > ' Rearrange columns...
> > ' Cut, column J "Current Maximum CPC" Insert into column E
> > ' Cut, column K " Keyword Destination URL" Insert into column F
> > ' Delete Column K (Column L "Destination URL" becomes column K)
> > ' Delete Column L
> >
> > '
> > Application.WindowState = xlNormal
> > Application.Left = 10
> > Application.Top = 35.5
> > Application.Width = 767.25
> > Application.Height = 585
> > Rows("1:5").Select
> > Selection.Delete Shift:=xlUp
> > Columns("J:J").Select
> > Selection.Cut
> > Columns("E:E").Select
> > Selection.Insert Shift:=xlToRight
> > Columns("K:K").Select
> > Selection.Cut
> > Columns("F:F").Select
> > Selection.Insert Shift:=xlToRight
> > Columns("K:K").Select
> > Selection.Delete Shift:=xlToLeft
> > Columns("L:L").Select
> > Selection.Delete Shift:=xlToLeft
> >
> > ' Delete the very last row
> > With ActiveSheet
> > .Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
> > End With
> >
> > End Sub
>
> --
>
> Dave Peterson

--

Dave Peterson

Nice, thanks!