After recording a macro in yesterday's podcast, I take a look at the recorded code to look for instances where the macro recorder hard coded the number of records. With just a few tweaks, we have a working macro to automate the import and conversion of data from one system to another. Episode 774 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast.
Now, in yesterday's podcast, we developed a macro -- actually, recorded a macro -- that went through, pulled in one CSV file, rearranged a lot of columns, and saved out the CSV file.
I didn't try and run that macro.
I want to go take a look at it.
I'll press ALT+F8 and say that I want to EDIT, and we're looking here for things that are going to cause problems.
I figured that we're going to have a different number of rows every day.
That's just the nature of this thing, and so we do WORKBOOKS.OPEN.
That's going to work out great provided we always save the file to the same place.
Select all cells.
We're going to sort and there's nothing there that is saying we have a specific number of rows.
That's going to be great.
We select the entire column and do text to columns.
That's going to work as well, and then, if you remember, we changed some headings and I used the END key and then the DOWN ARROW key to ride down to the bottom of the range, and because I turned on relative recording, when I did that, that's going to work perfectly as well.
Basically, we select from the current selection that, in that case, was row 163 up to ENDXLUP.
Now, here’s where I actually run into problems.
At that point then, I had selected one too many rows and so I want to not say that I want to go 161 rows every time.
That's a bad line of code and so, instead, I'm going to go to the previous line and add an OFFSET 1 row down, 0 columns over.
That will make sure that, instead of starting in row 1, basically I press END UP and then go down 1, and now I'm able to take away this line of code where I was saying, hey, we're always going to select from A1 to A162.
Now, the macro recorder always records our formulas in R1C1 style.
It's a long story but that's actually better and it allows the code to work, and so we're good there, do a lot of scrolling, and, in fact, we really don't need those scrolls to appear in the macro.
I'm going to delete all of those scrolls.
It's crazy how many we get just as I scroll to the top of the window.
I'm now continuing down through our code here.
ACTIVECELL.OFFSET –162 RANGE A1 SELECT.
That's basically where we went up to our heading in I1, and so, if I was smart, I would have turned off relative recording at that point.
Wasn't smart enough to do it so I'm just going to write in here RANGE I1.SELECT and then, later on, we add the formula.
In fact, I'm going to take everything from RANGE I1 down to .FORMULAR1C1 and delete that.
So, we just simply write the word DESCRIPTION.
From there, it's going to work out great.
We're going to go back and delete the previous 5 columns, activate a column, change directory, and save the file.
So, at this point, I think that we're going to have something that's going to work fairly well.
The one problem is if we don't delete Google Calendar after we import it each day, the file is going to already exist, and so what we can do is add the kill command.
The kill command is a great way to delete a file, and I'll just copy this text from the next line.
Now if we try and delete a file that's not there, it's going to give us an error message.
So, right before that, I’m going to say ON ERROR RESUME NEXT.
This is crazier to me.
Hey, we're trying to delete the file, and I’ll come up and say I can’t delete the file.
It’s not there.
Well, my reaction is, great.
That’s one place where I use ON ERROR RESUME NEXT and then ON ERROR GOTO 0 after that line.
So, that way, if we would get a different error, we would know that, and actually, I think, at this point with just those few tweaks now, we should have a macro that'll work.
Let's just go back here and try and run it.
So, ALT+F8, I’ll click RUN.
Do I want to save the changes?
We’ll click NO, and we should be in good shape.
Now, that last warning.
When we have a CSV file, and save the CSV file, and then try and close it, it's going to give us that warning, and so what we can do here is, right before we close the file, ACTIVEWORKBOOK.CLOSE, I'll say SAVE CHANGES = FALSE.
By me putting that in the code, the person running the macro each day won't have to answer that question.
It's admittedly a confusing question.
Okay.
So, there we have it.
It took 2 days to solve this problem.
Yesterday, we recorded the macro.
We were very careful, but then you always have to go through your recorded macro and look for things where it hard-coded the number of rows because we can never count on how many rows we’re going to have each day.
So, a couple of tweaks here between the course of a couple of days got this one CSV file being converted to a Google Calendar CSV file.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Now, in yesterday's podcast, we developed a macro -- actually, recorded a macro -- that went through, pulled in one CSV file, rearranged a lot of columns, and saved out the CSV file.
I didn't try and run that macro.
I want to go take a look at it.
I'll press ALT+F8 and say that I want to EDIT, and we're looking here for things that are going to cause problems.
I figured that we're going to have a different number of rows every day.
That's just the nature of this thing, and so we do WORKBOOKS.OPEN.
That's going to work out great provided we always save the file to the same place.
Select all cells.
We're going to sort and there's nothing there that is saying we have a specific number of rows.
That's going to be great.
We select the entire column and do text to columns.
That's going to work as well, and then, if you remember, we changed some headings and I used the END key and then the DOWN ARROW key to ride down to the bottom of the range, and because I turned on relative recording, when I did that, that's going to work perfectly as well.
Basically, we select from the current selection that, in that case, was row 163 up to ENDXLUP.
Now, here’s where I actually run into problems.
At that point then, I had selected one too many rows and so I want to not say that I want to go 161 rows every time.
That's a bad line of code and so, instead, I'm going to go to the previous line and add an OFFSET 1 row down, 0 columns over.
That will make sure that, instead of starting in row 1, basically I press END UP and then go down 1, and now I'm able to take away this line of code where I was saying, hey, we're always going to select from A1 to A162.
Now, the macro recorder always records our formulas in R1C1 style.
It's a long story but that's actually better and it allows the code to work, and so we're good there, do a lot of scrolling, and, in fact, we really don't need those scrolls to appear in the macro.
I'm going to delete all of those scrolls.
It's crazy how many we get just as I scroll to the top of the window.
I'm now continuing down through our code here.
ACTIVECELL.OFFSET –162 RANGE A1 SELECT.
That's basically where we went up to our heading in I1, and so, if I was smart, I would have turned off relative recording at that point.
Wasn't smart enough to do it so I'm just going to write in here RANGE I1.SELECT and then, later on, we add the formula.
In fact, I'm going to take everything from RANGE I1 down to .FORMULAR1C1 and delete that.
So, we just simply write the word DESCRIPTION.
From there, it's going to work out great.
We're going to go back and delete the previous 5 columns, activate a column, change directory, and save the file.
So, at this point, I think that we're going to have something that's going to work fairly well.
The one problem is if we don't delete Google Calendar after we import it each day, the file is going to already exist, and so what we can do is add the kill command.
The kill command is a great way to delete a file, and I'll just copy this text from the next line.
Now if we try and delete a file that's not there, it's going to give us an error message.
So, right before that, I’m going to say ON ERROR RESUME NEXT.
This is crazier to me.
Hey, we're trying to delete the file, and I’ll come up and say I can’t delete the file.
It’s not there.
Well, my reaction is, great.
That’s one place where I use ON ERROR RESUME NEXT and then ON ERROR GOTO 0 after that line.
So, that way, if we would get a different error, we would know that, and actually, I think, at this point with just those few tweaks now, we should have a macro that'll work.
Let's just go back here and try and run it.
So, ALT+F8, I’ll click RUN.
Do I want to save the changes?
We’ll click NO, and we should be in good shape.
Now, that last warning.
When we have a CSV file, and save the CSV file, and then try and close it, it's going to give us that warning, and so what we can do here is, right before we close the file, ACTIVEWORKBOOK.CLOSE, I'll say SAVE CHANGES = FALSE.
By me putting that in the code, the person running the macro each day won't have to answer that question.
It's admittedly a confusing question.
Okay.
So, there we have it.
It took 2 days to solve this problem.
Yesterday, we recorded the macro.
We were very careful, but then you always have to go through your recorded macro and look for things where it hard-coded the number of rows because we can never count on how many rows we’re going to have each day.
So, a couple of tweaks here between the course of a couple of days got this one CSV file being converted to a Google Calendar CSV file.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
