Excel VBA Loop Rows 1

[jgarite]

Hello all, this is my first post/first VBA encounter...
Files I have:
1 ebay export of orders (csv)
1 yahoo orders file (csv)
1 yahoo items file (csv)

What I am trying to accomplish:
I need to move the data from the eBay export file to the two yahoo files. The number of rows in the eBay exports file will vary of course, but I do not know how to work with loops so I have set it to work with a large range (ie: A2:A100). This works fine (although I would rather it work on a loop) for the Yahoo orders file (although I have to clean the file of excess rows that have been filled with pre-generated text strings I created) however does not work so well with the yahoo items file. Heres why:
The Yahoo Items file has three rows for every order (All are to be pulled from the eBay orders export):
Row 1: Item Info
Row 2: Shipping Info
Row 3: Tax Info
I managed to loop it, but it doesnt go past the first item and gets stuck in the loop until I hit the escape key. Because the macro I made is so utterly messy and long, I have decided to attach the file rather than copy and paste it. When the macro is run, it will generate the two yahoo formats as two new spreadsheets. The inital spreadsheet you will see is an example of an ebay export.

If I was not clear enough, please let me know what I can tell you to make it clearer as I am really struggling to get this done. After the loop is complete, I also have another question, but I will post it after...one thing at a time Thanks in advance.

 

[SkipVought]

Please post the CODE where you are having a problem.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[SkipVought]

...also, check out faq68-5829.

It's alot easier to query than to write VBA code.



Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[jgarite]

'ITEMS file creation

  Sheets("ebay").Select
    Sheets.Add
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "items"
    Do
    ActiveCell.FormulaR1C1 = "Order ID"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Line ID"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Product ID"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Product Code"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Quantity"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Unit Price"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "3"
    Sheets("ebay").Select
    Range("A2").Select
    Selection.Copy
    Sheets("items").Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A2:A4"), Type:=xlFillDefault
    Range("A2:A4").Select
    Sheets("ebay").Select
    Range("P2").Select
    Selection.Copy
    Sheets("items").Select
    Range("F2").Select
    ActiveSheet.Paste
    Sheets("ebay").Select
    Range("Q2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("items").Select
    Range("F3").Select
    ActiveSheet.Paste
    Sheets("ebay").Select
    Range("R2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("items").Select
    Range("F4").Select
    ActiveSheet.Paste
    Range("C3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Shipping"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "Shipping"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "Tax"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "Tax"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "1"
    Sheets("ebay").Select
    Range("N2").Select
    Selection.Copy
    Sheets("items").Select
    Range("C2").Select
    ActiveSheet.Paste
    Range("D2").Select
    ActiveSheet.Paste
    Sheets("ebay").Select
    Range("O2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("items").Select
    Range("E2").Select
    ActiveSheet.Paste
    
    Loop Until IsEmpty(ActiveCell.Offset(1, 0))
    
     'Change Formatting
    Range("F:F").NumberFormat = "general"
   ' Range("B:B").NumberFormat = "mm/dd/yyyy hh:mm"
    'Ended Changing Formating

 

[SkipVought]

Here's the query that, seems to get you close...

SELECT [Sales Record Number], Quantity,[Sale Price],	[Shipping And Handling],	[US Tax],	Insurance

FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbeby`.`ebay$` 

UNION ALL 

SELECT [Sales Record Number], null,null,null,null,null

FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbeby`.`ebay$` 

UNION ALL 

SELECT [Sales Record Number], null,null,null,null,null

FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbeby`.`ebay$` 

Order by 1, 2 Desc

And the result...
[tt]
Sales Record Number Quantity Sale Price Shipping And Handling US Tax Insurance
1007 1 357.99 69.99 0 0
1007
1007
1008 1 409.99 69.99 0 0
1008
1008
1009 1 357.99 69.99 0 0
1009
1009
1010 1 357.99 69.99 0 0
1010
1010
1011 1 357.99 19.92 0 0
1011
1011
1012 1 409.99 19.92 0 0
1012
1012
[/tt]


Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[jgarite]

OK, that would work, however, here is the desired layout:
Order ID Line ID Product ID Product Code Quantity Unit Price
1007 1 Item Item 1 $357.99
1007 1 Shipping Shipping 1 $69.99
1007 1 Tax Tax 1 $0.00


(Sorry for the poor formatting, I don't know how to line it up like you did)
Could this also be used for the Yahoo orders format? And why are the nulls there?

 

[jgarite]

I'm confused, I just tried the code in a new macro on the ebay file, but im getting compile error: syntax error. Do I have to replace anything...sorry I'm a complete noob at VB

 

[SkipVought]

Please review the TGML Tags link in your browser on Tek-Tips.
[tt]
Order ID Line ID Product ID Product Code Quantity Unit Price
1007 1 Item Item 1 $357.99
1007 2 Shipping Shipping 1 $69.99
1007 3 Tax Tax 1 $0.00
[/tt]
Wouldn't the [LineID] be 1, 2, 3???

Furthermore, you do not have a [Product ID] or [Product Code] on the ebay sheet. Where do they come from?

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[jgarite]

Opps, slight oversight on my part, yes it is supposed to be 1,2,3. The Product ID and Product Code come from column N in the ebay sheet. Still trying to figure out how to get the query to work, im reading the faq page you linked to, but i dont know how to make it a table. in addition, will i have to do that everyday? this is something that is going to be run twice a day with different data (same headers)

 

[SkipVought]

"The Product ID and Product Code come from column N in the ebay sheet"

Column N is Item Title

Please post a SAMPLE of DATA.

Yes, this can work.


Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[jgarite]

That is correct, Unfortunatly, eBay does not allow us to use Product ID's/Codes, so we have to change it when it gets imported in our system which isn't a big deal. What data would you like to see? The Yahoo Orders, the Yahoo Items, or the eBay Export? Since you have the eBay Export from the link in the first post, and you generated the Yahoo Items sheet in your post timestamped 26 Feb 08 14:05 (3 above this one), I am going to post the Yahoo Orders, although I don't see what that has to do with the items at this point...but I'm a noob so I could be wrong
----I cant get the formatting becasue the columns go all the way to AM, I have to upload the file, please check back in a minute or two. I appreciate your help

 

[SkipVought]

SELECT [Sales Record Number],1 as 'Line',[Item Title], Quantity,[Sale Price]

FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbeby`.`ebay$` 

UNION ALL 

SELECT [Sales Record Number],2, 'Shipping And Handling',null,	[Shipping And Handling]

FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbeby`.`ebay$` 


UNION ALL 

SELECT [Sales Record Number],3,  'Tax',null,	[US Tax]
FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbeby`.`ebay$` 

Order by 1, 2

[tt]
Sales Record Number 'Line' Item Title Quantity Sale Price
1007 1 Asus Eee PC 4G Surf - Galaxy Black 1 357.99
1007 2 Shipping And Handling 69.99
1007 3 Tax 0
1008 1 Asus Eee PC 4G - Pearl White with Camera 1 409.99
1008 2 Shipping And Handling 69.99
1008 3 Tax 0
1009 1 Asus Eee PC 4G Surf - Pure White 1 357.99
1009 2 Shipping And Handling 69.99
1009 3 Tax 0
1010 1 Asus Eee PC 4G Surf - Galaxy Black 1 357.99
1010 2 Shipping And Handling 69.99
1010 3 Tax 0
1011 1 Asus Eee PC 4G Surf - Galaxy Black 1 357.99
1011 2 Shipping And Handling 19.92
1011 3 Tax 0
1012 1 Asus Eee PC 4G - Pearl White with Camera 1 409.99
1012 2 Shipping And Handling 19.92
1012 3 Tax 0
[/tt]
Naturally, your FROM clause will phave a different PATH than mine...

FROM `C:\Documents and Settings\ii36250\My Documents\vba\dbeby`.`ebay$`

that will result from the drilldown to your own workbook on your PC in the MS Query Wizard.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[jgarite]

opps...duuhhhh LoL I didnt change the path...but the thing is I was trying to put it inside a macro, thats wrong im assuming...I never used the MS Query Wizard so I have to figure that out before I can use your code I'm linking to the orders page becuase what you just generated is basically what the items page looks like and i already posted the ebay page.

 

[SkipVought]

More than likely, no loop.

Am I correct in assuming that you will have ONE sheet of source data, the ebay sheet?

On a blank sheet in another workbook is where you ought to be doing this query.

The query will point to the workbook containing the ebay sheet as the source data.

If that workbook changes names, we will have to have some code to direct the query to the correct workbook.

In your new workbook/worksheet, do the Data > Import External Data > New Database Query - Select Excel Files* and drill down to the workbook containing the ebay sheet. This you do ONLY ONCE!!!!!!

Subsequent queries are merely Data > Refresh

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[jgarite]

Man...this is turning out to be harder than I thought LoL, but its a good learning experience for me. I'm in the SQL Statement area of the Microsoft Query Program, and here is what I put:

SELECT [Sales Record Number],1 as 'Line',[Item Title], Quantity,[Sale Price]

FROM `C:\Documents and Settings\RBC\Desktop\dbeby`.`ebay$`

UNION ALL

SELECT [Sales Record Number],2, 'Shipping And Handling',null,    [Shipping And Handling]

FROM `C:\Documents and Settings\RBC\Desktop\dbeby`.`ebay$`


UNION ALL

SELECT [Sales Record Number],3,  'Tax',null,    [US Tax]
FROM `C:\Documents and Settings\RBC\Desktop\dbeby`.`ebay$`

Order by 1, 2

and it shot back the following:

Could not add the table '`C:\Documents and Settings\RBC\Desktop\dbeby`.`ebay$`'.

Did I miss something?

 

[SkipVought]

Could not add the table '`C:\Documents and Settings\RBC\Desktop\dbeby`.`ebay$`'.

dbeby is the name of MY workbook. Your workbook name belongs there.

Really, the way to begin (we jumped in with several UNION ALL joins, skipping LOTS of elementary stuff) is that when you get to the Query By Example grid, you Open Tables and choose a SHEET (table), then drag fields (column names) from the Table to the Grid. We skipped all that. Doning THAT, you would have seen YOUR PROPPER PATH AND WORKBOOK NAME rather than what you copied and THOT that you changed properly, but not quite far enough.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[jgarite]

Ok, I realized that I didnt change the filename. I got it to work, but there are about 30 rows that only contain line numbers and shipping and tax...how did that happen? And now i guess the question is how to have the macro access the sql query...

 

[SkipVought]

"...but there are about 30 rows that only contain line numbers and shipping and tax"

No Sales Record Number either?

Check your source data, using the AutoFilter. Look for BLANKS in Sales Record Number.



Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[jgarite]

Nope, in the ebay sheet, it goes A1:A7 thats it. In the new sheet it goes A1-Header, A2:A31 Blank, A32:A49 Sales Record Numbers. Any ideas why it would do that?

 

[SkipVought]

You actually have DATA in those rows. Data could be SPACES or other non-printing character(s). Hard to tell.

Select ALL the rows below the data and DELETE.

Try again.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]