Free Microsoft Excel 2013 Quick Reference

Showcase Query Add-on Constantly Disappearing

Has anyone seen this happen before? The add-on will be there and functioning properly and after I close out all of my excel files and the program itself, the next time I launch an excel file the add on will be gone.

Post your answer or comment

comments powered by Disqus
would anyone know why doing a sumif on a range taken from a Showcase Query would bring back #value?

Ie. Showcase query is on range sheet1 A1:B3
If i do a sumif on that, it returns a linkedarea1 instead of the range and returns #value
If I do a sumif on the range greater than A1:B3 (ie A1:B1000) I get the correct answer.

Hi Guys,

How can i create a query based on two column listbox, i have copied a public code wherein you can create a query but only based on 1 column inside the listbox,

here is the code: this is only for 1 selection "fiscal years", i want to have a selection of fiscal years and budget type, i dont know how to incorporate in the code

Private Sub ForecastButton_Click()
On Error GoTo Err_forecastButton_Click
Dim MyDB As dao.Database
Dim qdef As dao.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM [Order Entry File Modified File Forecast]"

'Build the IN string by looping through the listbox
For i = 0 To Forecastyears.ListCount - 1
If Forecastyears.Selected(i) Then
If Forecastyears.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & Forecastyears.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [fiscal year] in (" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "Forecast Table source"
Set qdef = MyDB.CreateQueryDef("Forecast Table source", strSQL)

'Open the query, built using the IN clause to set the criteria
'DoCmd.OpenQuery "Standard Table source", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.Forecastyears.ItemsSelected
Me.Forecastyears.Selected(varItem) = False
Next varItem

Exit Sub

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_ForecastButton_Click
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_ForecastButton_Click
End If

End Sub

Can you help me on this?

I am running into a problem with my showcase query. For whatever reason, query updates are not working when run in VBA. When I click on RefreshAll under the tool bar, it works, but it does not work when I program it into code. Have anyone ever experienced this before?

Thanks for your help!

I am wrinting a macro to import data from a text file. I thought it would be a simple record macro and go. Everything works fine except for one aspect that I have been unable to figure out. I have an excel file that I would like to import a file into on a daily basis. The thing that I cannot figure out is when I do the import, it shifts the previous imports over by the width of the new import.

So the macro has the first blank row set as the destination for the query.add. The first run imports the data into A1. Assume there are 5 rows and 5 columns af data. The next run will put the data at A6, but it will shift rows 1 through 5 over by 5 columns. Each time I run the macro it will go down the correct 5 rows, but everything gets shifted right 5 columns.

I am assuming this has something to do inherently with the "add" part. Is there someway to turn this off? If I do not use a macro, but just do two data imports by hand, the second one does not shift anything.

Here is the code I am using. It is a straight macro recording except for the destination.

rCount = Cells(Application.Rows.Count, 1).End(xlUp).Row

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:Documents and Settingse003523Desktop342 bytes.txt", Destination:= _
    Cells(rCount, 1))
    .Name = "342 bytes"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = True
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = True
    .TextFileColumnDataTypes = Array(1, 1, 1, 3, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

I have just started to get a very strange crash in Excel 2000(sp3) (Win2000pro-sp4) which seems to be related to enableing the MS Query add-in.

With this checked, when I start Excel, as soon as I type in a cell on the blank sheet, it crashes with a memory read error. There are no other hidden sheets loaded and only a couple of other add-ins (VBA and Solver). If I disable MS Query, all seems fine - though I've had it enabled for the last couple of years without problems.

I've checked my RAM, replaced the three MS Query files in ...libraryMSquery with known good copies and "repaired" my copy of Excel but to no avail.

Any ideas anyone?

Mike P

I would like to pause a macro, but not in the traditional sense. I am running a Showcase query from inside excel. I want the query to update, drop the data in excel save it and then close. The problem is that the save command is executing before the data is dropped. The Wait command pauses all processes so it won't work. Is there a way to have a minute of filler until the data is dropped so it will save?

Hi everyone!

I've got a table that I, with help of a query and many criterias on different fields, can filter so that I get the records I desire to have. I then done a chart out of these records. So far everything works..

but now to the problem...
The table is among other things based on years and quaters. I want to be able to compare different quaters. So what I've done is to make a new query that has the same criteria as the first query except the criteria for years and qutarers. That criteria is taken from other comboboxes. (All the criteria are taken from a form with different combos).

So finaly what I've tried to do is to do a third query based on the two first queries so that I there would be able to take the fields I want and calculate the difference between them. That doesn't work!?...

It says: "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields drom a table and from a query based on that table. If so, try choosing fields from only the table or only the query"

What can I do!??!
Any help would be appreciated

Sorry for my poor english, hope you understand...

Thanks in advance



I m trying to make a query based on a dynamic range and paste the results in a sheettab of my wrorkbook. How can I do it?

Thanks once again

I am looking at building a standardised model and have come across an add-on to Excel called BPM (Best Practice Modelling) Toolbox. My understanding of this package is that it provides the purchaser with an additional toolbar in Excel with a number of macros already built into it. The Toolbox also comes with a set of guidelines and principles that one ought to apply when building a model in order to produce 'best practice' quality. The end result is that you can press a button in the toolbar and a standardised model (i.e. all the worksheets) then populates the assumptions page and customises the workings sheets to populate the standard model output/sheets.

I am not aware of any other packages out there that attempt to achieve a similar outcome. Any suggestions?

hey all,

I have created a suite of macros that are essentially utilities for various things my team does regularly that I wish to share with the rest of the team.

I have lumped them all together into one project and saved is as an (xla) add-on, with a custom menu to access the commands (which I had to pin to a blank workbook to distribute).

The question is, how can we all access the add-on in such a way as to allow various team members to also add their own macros to the code for us all to use?

Will new add-ons have to be created each time a change is made and we all update which add-on we are using?

or can the same add-on just be amended?

Looking for an Excel add on called "Spreadsheet Professional". Can anyone point me in the right direction?


I get an "Invalid Web Query" error on opening a 2003 worksheet which contains
links to Yahoo Finance. The links themselves are valid. Any ideas?


Can somebody suggest any third party Charting
Add-ons/Plug-ins/Controls that expand the charting capabilities of MS

Such that may be code-controlled in VB or VBA.


We have recently been told by our agency systems administrator that we need
to upgrade all of our existing PCs so they have Office 2003 because of an XML
function that allows us to import/export data to and from our agency server.
Obviously, this would not only be prohibitively expensive, but time-consuming
as well.

What I would like to know is if there is some kind of XML add-on that we can
install for Excel 2000 and/or XP that will give us the same XML functionality
as Excel 2003?

Thanks MUCH in advance if anyone can give me an answer on this.

I have a user who is pretty good at Excel. He wants to know if there are
more sophisicated graphs within EXCEL or add-ons he could use. He had a
problem where he inserted 4 graphs into a document and the bottom L/H side
one enlarged itself a little and the other graphs played up a bit.

It could be the way he is wokring with them but he is looking for something
that is more easily manipulated.

Any ideas of add-ins or another program. He works with graphs etc all day

Org Chart Add On for Office Pro Plus 2007

How do I open the Org Chart add on to Power Point for OfficeProPlus 2007 after I downloaded it?

Hi everyone,

Just joined this forum. Wanting to say hello, and see if someone can direct me to a template the allows add on interest. (What I am calling it)

Now what I am referring to is I have financed something for a company and they have been slow pay. I need a template that will allow me to adjust the interest each time the party is late with a payment. I may not be referring to the sheet with the proper term. (I don't know the question to ask the FAQ)

Need payment due date, payment date, that will adjust the days and charge interest from the last principle amount. This will add the interest to the principle, then correct the interest charge if still late (more days) then add this to the principle. When a payment is made adjust the amount paid by the interest due and subtract this, and if any left over, reduce the principle.

I hope I have explained this well enough to have it understood.

Thanks in advance for any help on this.



I've just found the ASAP utility by chance and found it really useful in different things for me.

Please does there is any free add-ons for Excel 2007 that is might be as useful as the ASAP!?

Or in other words any that has been tried and trusted!?


Is there a project management add on for excel inc. gant charts?

Can anyone recommend a good statistical add-on package that corrects for the
erroneous statistical functions of Excel?




I need to add a constant number (i.e., 2 or 3) to a column of Excel data. Is
there an easy way to do this?

Hi all,

Can anyone suggest an application/add-on (preferably free/cheap) that will help me identify which cells are linked in a spreadsheet? I've 'adopted' a document that has dozens of links to both live and redundant worksheets and I need to get rid of the inapposite ones.



Hey everyone! I am looking for a specific Add on for Excel. Here is what I need it to do. Removes formatting and formulas on unused cells and therefore shrinks the spreadsheet size. Any help would be useful, and very helpful. I have found one in the past, but due to the user crashing his HDD, it was lost forever.

I want to add on 6% to each cell. Ranges are from B11 - L61. I have put a 6
in a cell by it self, highlighted, paste special, then multiply.

My problem is - it only displays how much 6% is of the sum that I have put
into the cell.

E.G In one cell, I have .58900 - I multiply that by 6.0% and get .035340.
Which is correct. BUT I need .58900 + 0.03534 to SHOW that .58900+0.03534 =

How do I do this???


No luck finding an answer? You could always try Google.