Excel's refresh data resets filters

[MCubitt]

I have a spreadsheet which uses "Import text File" external data to refresh the database. This works.

I have autofiltered each column to give the user ability to select certain paramters, such as only their data (one column is userid).

However, when a filter is used and THEN refresh data is pushed, the filter goes back to showing everything.

Can these be locked ?



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SkipVought]

MC,

Data/ImportExternal data/External data Range Properties - and check Preserve column sort/filter/layout.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[MCubitt]

Skip,

That option is greyed out.
Any ideas why?




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[xlbo]

You could mod the SQL and get the user to enter their userID into a cell - the SQL could then be firther modded to accept that as aparameter in the WHERE clause so that only their data is returned .....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MCubitt]

There is no SQL. It uses "Import text File" external data to refresh the database, not an ODBC connection (which would mean setting it up on everyone's machine).

Thanks anyway


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[xlbo]

Fair enough

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MCubitt]

Looks like I am stuffed, unless that preserve sorting/filters can be switched on!

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Considering the VBS to extract the file is, itself, in the macro to REFRESH, I could (in theory) filter there. So can I reference a (hidden) cell inside the macro?




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[xlbo]

Techically yes but you would have to show the code you are using to be certain

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MCubitt]

Geoff,

A text file is read into an array, split by cr/lf.

It is reads backwards (to reverse sort) line at a time.

Each line is reformatted (each column reterived and then glued back in another order). At this stage a variable holds the column values before writng to the new file.

I can paste the cost if you want (how do I MAKE it code in here?)





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[xlbo]

So I'm clear here - is this code in VBA or VBScript ??

Does the MACRO exist in excel or another app ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MCubitt]

Sorry, it is VBS.

The macro is Excel. The VBS is PART of the macro (ie not called).


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[xlbo]

Hmmm - not quite getting it but I guess I don't really need to
To get a cell value into a variable, just use

myVar = sheets("Sheetname").range("a1").value

to get whatever is in A1 into the variable

You can then use

Sheets("Sheetname").autofilter field:=3, criteria1:= myVar

where you want to filter on column C (3)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MCubitt]

Okay, now for some reason the line

ActiveSheet.QueryTables(1).Refresh

is giving a very odd error!

I even removed the new code.

Run-time error 1004, Application-defined or object-defined error.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[xlbo]

the only reason that should error is if there is no QueryTable on the ACTIVE sheet

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MCubitt]

Closed and opened old version.

Filter now working (think when I inserted new row it broke it).

I added at top of macro:
filteruserid = ActiveSheet.Range("f1").Value

And inside detail:
If filteruserid = "" Or filteruserid = docorderedby Then
Outfile.WriteLine (Doctext)
End If

can I use "like" ?

Thanks!

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[xlbo]

Should be able to use
Like "Text*"

Need the * as the wildcard

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question