Please help - formula query

=if(originalformula=0,"",originalformula)

jonmo1 said:

=if(originalformula=0,"",originalformula)

Click to expand...

thanks for that, it works a treat

Cheers

Tony

Try formatting date cells as

dd/mm/yy;;

note the two ;

barry houdini said:

Try formatting date cells as

dd/mm/yy;;

note the two ;

Click to expand...

Thanks a lot, thats extremely handy to know!

Cheers

Tony

Tony,

It might suit to populate your 3rd sheet without using formulas at all. When working with data tables there is database-type functionality within Excel. Tables can be joined like in a relational database. This might then return you a true null (instead of zeros) and be better suited to loading to the database. For example, refer to 'external data' within Excel help, and the menu data, import external data, new database query, etc. External data can come from the same file, it doesn't have to be from a separate workbook.

HTH, Fazza

Fazza, what's your preference if you are pulling data into an Excel file from another Excel file -- using Get External Data..., or writing an ADO procedure.

I am finding ADO so easy to use that I don't even want to bother with the MSQuery tools - but perhaps once you define a data source using Get External Data would also be quite fast to access repeatedly. Do you have any strong opinions here?

Regards.

Hi, Alexander.

No strong opinions. Agree very much with your comments.

ADO and a little knowledge of SQL is great. A little VBA. Excellent for pulling data on the fly from mutliple workbooks/sources.

I guess it depends on what you're doing. The above is the main use for me. Something programmed and re-usable. I create the result each time.

I have limited use for a 'fixed' query table that can then be repeatedly updated. And there is the complication of changing references should source files change. I might use it in a one off task as it is easier to generate via the normal menu. I can edit the SQL in MS Query and get some powerful/handly results quickly. It is also easier to show someone, or to explain when answering forum posts. It doesn't need VBA. (As with this thread. I would use VBA & ADO to do the job however it is simpler to explain to others to use the MS Query interface.)

I think more verbosely than you eloquently expressed, I am agreeing with you.

For the benefits of others, ADO offers three main objects. For a single task the recordset object is simplest I find. A late bound example where strSQL is the SQL string and strConnection the connection string
I have a few instances of pulling data from dozens of files, creating the SQL in VBA. It still impresses me how quickly the data is pulled even when the SQL does something complex and maybe also has a subquery.

regards, Fazza

Thanks for the response.

It still impresses me how quickly the data is pulled

Click to expand...

I am rather new to ADO and I am in awe - its a real thrill to see my blank worksheet suddenly bursting with data, with only a few lines of code to make it happen. But I also find the query tools to be a very convenient way to show my coworkers what a SQL query is like. Primarily, I've used ADO with Access databases -- I will have to play with some Excel-to-Excel connections.

Regards.

Only difference Excel-Excel v Access-Excel is the connection string. So, that will be trivial for you.

With a little ADOX you can create an .mdb file on the fly. I did a neat little test recently and with a relatively few lines of code created an .mdb file with new table (ADOX) and then (ADO) pulled data from mulitple Excel files into the new file. As I late bound, this could be run from 'anywhere'. So, even without Excel or Access even being loaded on the machine, the code ran fine from VBA in MS Outlook or MS Word. Another bit of magic - no Excel, no Access and a new mdb file is created from multiple Excel files!