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]

BTW, did you apply the AutoFiter to the ENTIRE table and not just on column? All you need do is select any ONE cell in the table and then Data > Filter > AutoFilter

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 cleared data in all empty cells below the 1012 sales record and it still shows the same amount of empty cells as stated above. i did the auto filter, but there are no blanks in the sales record column

 

[jgarite]

Let me just clarify, this is the SQL statement:

SELECT [Sales Record Number],1 as 'Line',[Item Title], Quantity,[Sale Price] 
   
FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$` 
   
UNION ALL    

SELECT [Sales Record Number],2, 'Shipping And Handling',null,    [Shipping And Handling] 
   
FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$` 
     
UNION ALL    

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

Order by 1, 2

 

[SkipVought]

You can add a WHERE clause criteria like this...

SELECT [Sales Record Number],1 as 'Line',[Item Title], Quantity,[Sale Price] 
   
FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$` 

Where [Sales Record Number] is not null

UNION ALL    

SELECT [Sales Record Number],2, 'Shipping And Handling',null,    [Shipping And Handling] 
   
FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$` 

Where [Sales Record Number] is not null
     
UNION ALL    

SELECT [Sales Record Number],3,  'Tax',null,    [US Tax]

FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$` 

Where [Sales Record Number] is not null

Order by 1, 2

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]

EXCELLENT! One final question...can this be executed through the macro?

 

[SkipVought]

Sheets("YourSheetName").querytables(1).refresh false

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, Ill have to try that. I just gotta make a few changes to the code (IE add the product code column) and than remove the '' around the Line...I guess that has to be done after the query because it is needed in the query it seems. Thank you so much for your help. I'll repost if i need more help.

 

[SkipVought]

You can code...

SELECT [Sales Record Number],1 as Line

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]

I tried that, it gave me an error.

Any idea why this wont work? When I added the bold it gave me an error:

SELECT [Sales Record Number],1 as 'Line ID',[Item Title] as 'Product ID',[b][Item Title] as 'Product Code',[/b] Quantity,[Sale Price]      

 FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$`    

Where [Sales Record Number] is not null    

UNION ALL       

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

FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$`    

Where [Sales Record Number] is not null         

UNION ALL        

SELECT [Sales Record Number],3,  'Tax',Quantity,    [US Tax]   

FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$`    

Where [Sales Record Number] is not null   

Order by 1, 2

 

[SkipVought]

You must have FIELDS in each query to match the FIRST QUERY. The first query has SIX fields. You inserted a second [Item Title]. There must be a corresponding TEXT field, either a field from the table or a literal value, to match. Hence the '' "field", a literal zero length string.

I've formatted the SQL code in a manner that I most often use. It helps the readability and maintainability...

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

FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$`    

Where [Sales Record Number] is not null    

UNION ALL       

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

FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$`    

Where [Sales Record Number] is not null         

UNION ALL        

SELECT 
  [Sales Record Number]
, 3
, 'Tax'
, ''
, Quantity
, [US Tax]    

FROM `C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`ebay$`    

Where [Sales Record Number] is not null   

Order by 1, 2

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]

Hey, that last code worked perfect with minor changes to the titles. Thanks so much. I tried using the code you provided to have the macro run the query, bt it didnt work (I replaced the sheet name and the query...my query name is ebay_to_yahoo_tems and the worksheet i renamed to was the ebay worksheet)...any thoughts?

 

[SkipVought]

Please post the code that fails.

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]

Sheets("ebay").ebay_to_yahoo_items(1).refresh false

 

[SkipVought]

Sheets("ebay").QueryTables("ebay_to_yahoo_items").refresh false

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]

getting the error Subscript out of range ...

 

[SkipVought]

That error message means that an Object is non-existent. It could ither be the sheet name or the QueryTable name.

If you select in the QT, and Data > Import External Data > Data Range Properties what is the value in the Name textbox at the top of the dialog? THAT is the name of the QueryTable and not ebay_to_yahoo_items, I suspect.

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 the name in the textbox :-{

 

[SkipVought]

How can the Source Data be on ...

`C:\Documents and Settings\RBC\Desktop\Final Macro Completed`.`[b]ebay[/b]$`

AND

the QueryTable be on the SAME SHEET?????

What sheet is the QueryTable on?

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]

Now I'm confused...this is what I did to create the query, maybe I did it wrong:
Data > Import External Data > New Database Query
(On the Databases Tab) Excel Files > OK
I opened the Final Macro Completed.xls file
--It than told me there were no visible tables, I hit OK and it asked me if I want to continue editing this query in Microsoft Query, I clicked Yes.
--It prompted me to Add Tables, there were none so I clicked Close and closed the Query from Excel Files window leaving only the Microsoft Query Window open
I hit the SQL button and pasted the code you provided and saved it
Than I hit the Return Data button and that's how I was getting the data into the spreadsheets.

How would I find what she the Query Table is on? Do I have to provide a full file path?

 

[PHV]

I guess you have to create the Query in the Final Macro Completed.xls ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886