Who's good at MS Excel?
post-2778419
Post #16 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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.
 
     Share This Post       
post-2785724
Post #17 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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?
 
     Share This Post       
post-2786383
Post #18 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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???
 
     Share This Post       
post-2786484
Post #19 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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
 
     Share This Post       
post-2815739
Post #20 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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?
 
     Share This Post       
post-2817822
Post #21 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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?
 
     Share This Post       
post-2818109
Post #22 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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.
 
     Share This Post       
post-2818237
Post #23 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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???
 
     Share This Post       
post-2819463
Post #24 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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).
 
     Share This Post       
post-2819494
Post #25 of 26

Pepsione1

1000+ Head-Fier
Joined
Aug 2, 2001
Messages
1,332
Reaction score
10
Joined
Aug 2, 2001
Posts
1,332
Likes
10
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.
 
     Share This Post       
post-2820519
Post #26 of 26

Gord SW Ont

100+ Head-Fier
Joined
Jan 4, 2002
Messages
495
Reaction score
10
Joined
Jan 4, 2002
Posts
495
Likes
10
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.
 
     Share This Post       

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top