Can someone do me a favor? small script

[ZenMasta]

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]

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]

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]

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]

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

 

[ZenMasta]

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 address 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/ShowPost.aspx?PostID=2115020&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]

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 address 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/ShowPost.aspx?PostID=2115020&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.

 

[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

 

[ZenMasta]

Nice, thanks!