Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
Thanks, I figured out how to add the code snippet you provided to the end of
the macro I recorded. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
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 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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
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 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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone do me a favor? small script
Nice, thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SMALL IF | Excel Discussion (Misc queries) | |||
ISERROR,SMALL,INDEX, MATCH, SMALL?? | Excel Discussion (Misc queries) | |||
A Little Favor for a Friend | Excel Programming | |||
Using Small | Excel Worksheet Functions | |||
Excel 2000/XP script to Excel97 script | Excel Programming |