[Gord SW Ont]

Okay ... now you need to get someone in who knows a little more about databases.

You really shouldn't be playing around with databases without knowing it's structure (tables, relationships, etc). Not sure if "scripting" is absolutely required as running a query to extract the required data can be done manually or via macros as well as through programming (ie via Visual Basic or whatever).

You may be able to use Microsoft's Access database app (or whatever database application your company uses) and link to your data source using ODBC drivers for example. But you will probably need to to write a query in SQL to retrieve a subset of your data (if you don't need the entire table or if the data is in more than one table). This isn't learned in a few minutes playing around with an application you're not familiar with.

Sorry I can't be more helpful.

 

[Pepsione1]

Hey Gord, I am trying to take another stab at this.

If I can get the data in CSV format then I can do what I need to do with it. So far I have created a macro in xls a file to pull in data from the SQL server every 15 mins. My trouble is saving this data in CSV format.

I would like the above the completely automated that's why I created the macro. Everything is fine until the macro tries to overwrite the existing csv file. A windows prompt would pop up telling you a file already exist, do you want to replace?

I want it to replace the file because that's the equilvalent of updating it. The program that's going to be using this csv file will only have to work with 1 file (not 1 file with multiple names as time goes on and updates are added).

So how do I get rid of the prompt or is there a better way to convert xls file to csv format?

 

[Gord SW Ont]

Quote:

Pepisone1 wrote: ..... So how do I get rid of the prompt ...

I'm still not sure exactly how you're doing this??? Did you write/record a macro or did you use the VB editor directly?

Not sure I'd recommend this approach, but you can turn off that warning by adding the following line above of your SaveAs statement (without the quotation marks) ...

"Application.DisplayAlerts = False"

You should probably add:

"Application.DisplayAlerts = True"

before the End Sub statement to turn them back on. ;^)

If it were me I'd probably play around with Microsoft Query and possibly a little Visual Basic but it would depend on exactly what your setup is???

 

[Pepsione1]

That's exactly the command I was looking for. You always seem to have the answers to my questions. Perhaps we should change your title to MS Excel Guru. lol

 

[Pepsione1]

I have yet another question for Gord.

There is a worksheet with many rows and columns but the size of this table doesn't change. In this table the fields are rarely filled with data. I want to focus only on fields that contains data. How can I hide the empty fields or extract the ones with data in it to another workbook?

 

[Gord SW Ont]

Sorry for the delay but we've had great weather the last couple of days and I was out metal detecting.

Not sure what you mean ... is your "table" just a list on the worksheet with fields headings across the top of each column and the data as rows or is it a pivot table? Are some fields totally blank so that you want to remove the column?

 

[Pepsione1]

It's not a pivot table, it's nothing fancy. It just has data on a sheet with headings in the top rows and headings in the left most columns. The data resides below and to the right of these headings.

For every field that has data, it must bring that data to a another sheet/workbook along with the headings it's under and to-the-right-of.

 

[Gord SW Ont]

Without seeing the data it's hard to say what the best way is ... I'm assuming that you want all rows with "any" valid data in them (ie no row with no data in it)???

To automate this would probably require visual basic but one fairly easy way to accomplish this would be to add a column to the right side of your data (for example, call it "Check"), and put a formula in that checks for a row with all blanks. One way that works is to use the following formula in the first row of data ...

=IF(COUNTBLANK($B2:$D2)<3,"NB","B")

Note: the "3" is maximum number of columns minus one for the row heading.

You can quickly fill this down to the last row of your table. All rows with a "NB" in the Check column you want to extract while those with a "B" are all blank (except for the row heading).

Now just use the menu item "Data - Filter - Advance Filter" and use "Copy to another location" (must be on the same worksheet). You will have to create a Criteria range ... select a column to the right and put "Check" in a cell and under it put "NB" (without the quotation marks). Once you select the location you want your data at in the Advanced Filter dialog box click Ok and you data will appear on the sheet. You can now select it (without the Check column) and put it anywhere you want.

I know it seems like a lot of effort but it's not really and if your original table size stays the same the formulae should not change for future efforts.

There are other solutions ... this is the first thing that popped into my mind (it may have been adversely affected by my headphones). BTW I can e-mail you a simple example if you like.

Hope this is not too confusing???

 

[Gord SW Ont]

Actually once you add the "Check" column you can just select the table and do an autofilter for "Check" equals to "NB" and then copy and paste where ever you want. Then delete the "Check" column or just copy and paste everything but the Check column. (the advanced filter is not required).

 

[Pepsione1]

Gord, I found something that will do what I need. Auto Filter.

Enable the auto filter and then a combo box automatically becomes available on the first row of each column. From there I go to a column and select how I want it filtered. After the filtering, it reduces this column to what fits the criteria that was chosen. The best part, it also reduces all the other columns that are wrapped around the filtered column. Not based on a specific criteria of the columns. It simply strikes out the entire row that was filtered out in the column you sorted. A couple of shift+click and ctr+c. I have the filtered list with the proper row headings that describes these post-filter fields that are filled with data.

So this is exactly what I wanted except it's somewhat slow at each sort but I can live with that. It's good when something works.

Thanks again for taking the time to answer my question Gord.

 

[Gord SW Ont]

Good to see you figured it out. It helps to know what the data looks like in order to get the best solution.

Not sure exactly what you did with autofilter but be careful that you don't duplicate rows if you autofilter column by column. I may not have understood you correctly as I'm not sure autofilter alone (ie without the "Check" column) would work properly with my interpretation of your posts???.

As long as it works for you ... that's what's important.