SQL in Excel data
Is there a possibility/way to run an SQL query in an excel data sheet?
I have quite some data like the sample below, now i would like to have the
sum of spending for each person. Like it is possible in Access.
Advice would be appriciated.
You could use a formula like this ...Excel 2003 extract repeating cells
I have successfully sorted my data to show repeating entries in
relation to two specific columns. I can't seem to figure out how to
select these repeating entries (without doing it manually, of course)
and putting them either into their own column(s) or an entirely
different spreadsheet altogether.
The goal is to save time in managing THOUSANDS of documents in this
manner so that my colleagues can easily pick up repeating entries and
take according action. Doing it manually is very time-inefficient.
You don't really describe enough of your layout or what you actually mean...How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from
the following XML file:
<?xml version="1.0" encoding="utf-8" ?>
There will be more data than just a name for each Parameters node. Here is
Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...Exporting file names to an excel spreadsheet.
I have a folder containing many many many .pdf files.
Is there a way to export the filenames of these .pdf files into an
If this is a dumb question, I apologize. I didnt have any luck
searching for the answer here.
Kepf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30611
View this thread: http://www.excelforum.com/showthread.php?threadid=547520
I use Jim Cone's fine Add-in called "ListFiles"for this sort of thing. It's
available free (a...excel spreadsheet
How do I delete columns from my spread sheet? When I right click the word
"delete" isn't highlighted, so it doesn't work.
Is the sheet protected or shared?
Please keep all correspondence within the Group, so all may benefit!
"Ang" <Ang@discussions.microsoft.com> wrote in message
How do I delete columns from my spread sheet? When I right click the word
"delete" is...excel, worksheet, set print area, position selection on printed pa
Office 07, Excel.
Print area is set, print preview displays selection, always on far left of
I want to choose the position on the page, eg centered or right aligned etc.
To get your printout to center on the page chose Page Layout, click the
Page Setup quick launch button (bottom right corner of the Page Setup group).
Choose the Margins tab, click Horizontal.
There is no command to right align a printout, but you can change the left
margin to force the printout to the left.
If this helps, please click the Yes button
"Eve Al...Repair SQL Corrupt database
I have a corrupt MS SQL Server 2005 database which I
am trying to repair using:
DBCC CHECKDB ('MYDB', REPAIR_ALLOW_DATA_LOSS)
Unfortunately this does not seem to fix anything.
After running the repair multiple times, I ran
DBCC CHECKDB ('MYDB') WITH NO_INFOMSGS
to see if it fixed the corruption, I noticed that is was returning a
random amount of errors on each run.
Does any know if It is the case that this database is beyond repair?
If so will the best approach be to revert back to a non corrupt
database backup and then roll forward using Transaction Lo...how to turn off automatic format in Excel?
Excel automatically change the first character in a cell to be
uppercase. I just want lowercase. How can I turn off this function?
Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence.
"ngoc" <email@example.com> wrote in message
> Excel automatically change the first character in a cell to be
> uppercase. I just want lowercase. How can I turn off this function?
...Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my
classes.(Alphabetized names down left vertical column. Dates across
top of horizontal row.) I added a different color to all cells in
every other row to make for easier reading of each student's name and
absences. Every other row stays with a white background.
My question: I wondered if it was possible to lock row colors while
allowing data to change on top of them. If a new student is added to
my class in alphabetical order, the alternating color pattern is often
lost. It is a pain to rechange row and cell colors.
Any shortcut ...How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data..
Refresh Data to requery the source, but I want to be able to do it in a more
Is there some sort of macro I can write when opened or something?
Select the cell the data starts in, select Data/Import External Data/Data
Check the box "Refresh on file open" and anything else that needs checking
Other than that, you could record a new Macro that selects your cell,
refreshes data, and assign the macro to a keystroke, or even an icon.
...Excel 2000 macro security Level
Hi! I am running an ASP.NET application which ideally opens an Exce
spreadsheet with a macro included in it. When I try opening
spreadsheet without a macro, everything works fine, but with macros, i
hangs on the opening command. I tried to go to Excel 2000 and go t
Tools->Macros->Security Level->Low but it still hangs when I try t
open the macro spreadsheet. All works fine with the spreadsheet no
containing macros, so it really is a question of macro security. But
put it to low, so I don't understand how it is still not opening!! An
help would be greatly appreciated!
Thanks and...z-order of various lines and bars in an Excel 2007 chart
What is the procedure for changing the z-order of various lines and
bars in an Excel 2007 chart, such as drop lines, high-low lines, up/
down bars, and error bars?
I observed that when I add drop lines to an Excel 2007 chart, they
appear in front of the underlying data -- in my case, the underlying
data is displayed as a plotted area. I want the plotted area to be
fully visible with no lines over it.
I would like to send the drop lines to the back and keep the plotted
area in the front, but there is no option to do this.
So, is there a procedure for adjusting the z-order of drop...Running an Excel Macro from an Access Command Button
Can anyone help me with this, I am trying to run an excel macro from an
access command button.
I want the macro to Bold the Headings on a excel spreadsheet.
The code runs without any errors but the macro doesnt seem to work, I think
I have got the line
ExcelApp.ExecuteExcel4Macro "RUN(""PERSONAL.XLS!BoldHead"")" wrong
Private Sub cmdRunMac_Click()
Dim ExcelApp As Excel.Application
Dim ExcelBook As Excel.Workbook
Set ExcelApp = Excel.Application
ExcelApp.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Open("C:\Documents and
Settings\...Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be
missing something here. Some data on vendor numbers changed. Let’s say IBM
used to have a vendor number 12345 and now it’s vendor number is 56789. I
can identify IBM as IBM, but I really want to use the number, not the name.
Should I set up a table that ties the numbers together, so that Access knows
12345 = 56789? Or, should I do some kind of Update Query and change all
incidences of 12345 to be 56789? Or, is there some other, method, like a
‘best practices for missing data’?
...Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office
Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders
File (.pst). I understand the basic "Office Outlook Personal Folders File
(.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002
Personal Folders File (.pst) to create a new Outlook .pst file that is
compatible with earlier versions of Outlook."
When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003,
when adding PST's you can chosse either of the two file type options and
browse to chos...New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet
via a macro, a new named range (for the same range) is created. This
does not pose a problem, but after a while, I'll have a huge number of
named ranges that will never be used. Why does Excel name the range
and how can I stop this? I noticed in the recorded macro, there was a
line .Name = "drd_5". I commented this out to see what would happen,
but it just renamed the range "ExternalData_5". The next one was
You could refresh with differen...Backup Excel file
I want to take backup of a file every time. I am using - save as - tools -
general option - always create back up. The backup file is in the same
location but I want to put it in a different folder. What should I do, kindly
Jan Karel Pieterse's has an addin called AutoSafe (note spelling).
(look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.)
It doesn't overwrite the existing workbook when it saves. It saves to a user
selectable folder. And when it's done, it either deletes these backups (or puts
them...Formatting Cells in Excel 97
Sorry if I sound real stupid but is there anyway that we
can control the column formatting in Excel 97 like let's
say column A = GENERAL(6), column B = GENERAL(4), column C
= TEXT(18) etc?
Appreciate any form of advice, thanks!!
I replied in the programming group. Are you seeking a programming
answer? It's usually best to only post to one group, and include the
remark "Please tell me if I should ask this in another group."
On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <firstname.lastname@example.org> wrote:
>Sorry if I sound real stupid b...Excel devides every number by 100
Even when I open a brand new file. I input a number (ie 5) and get 0.05 back.
I have tried doing everything in cell format etc.. anyone have an idea? it is
very annoying and adding loads of work to my project.
> Even when I open a brand new file. I input a number (ie 5)
> and get 0.05 back. I have tried doing everything in cell
> format etc.. anyone have an idea? it is very annoying and
> adding loads of work to my project.
...Excel Histogram -- Incorrect Bins
I am using the Histogram tool in Excel 2004 for Mac -- I am finding that =
Excel is shifting all my data one bin to the right. =20
For example, with data...
and bin range selected as 0,5,10
I am EXPECTING 3 items in bin 0 (between zero and five) and 3 items in =
5 (between five and ten).
Excel is RETURNING a histogram with nothing in the zero bin, and 3 items =
each in the 5 bin and the 10 bin.
This is driving my bonkers! I can't find a solution...can somebody =
Eric Anderson -
> I am using the Histogram tool in Excel 2004 for Mac -- I am...Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a
popup asking for the value of those 2 columns. The problem is, I don't use
those columns so I need to find what on the form is refferencing the deleted
I have looked at the control drop down on the properties window and the tab
order window and can't find a control with either one of the missing column
How do I determine what is trying to refference the deleted columns?
I found the problem, the column was still referenced in the underlying query
that fed the form.
"MeSteve"...Combining Lists within excel.
I'm hoping someone can help here. I'll try to explain the
problem I have.
I have 2 worksheets both containing a product list. I have
entries on one worksheet which I need on the other, so
basically my question is... might there be a way of
combining these two lists so one updates the other with
the missing entries.
Help on this would be great, as they are very large lists
which I dont much fancy the thought of looking through
Are you familiar with the VLOOKUP function? I use it all the time for this type
vlookup(Product cell in first sheet, Array...Import excel named range into Access error 3011
Hi, I've read a few posts on this topic, but none addresses my
I use Office 2003 on an XP SP3 machine.
I am using transferspreadsheet to import a named range in an excel file
This has worked beautifully until suddenly it stopped working.
I find now that if I extract the address of the named range from within
the Access VBA code,
it looks like this:
rather than this, which is what it should be:
When I open the spreadsheet and get the address of the named range in
the immediate window,
it also has the #REF error....Excel AutoSave #2
In Excel 2000 was this feature available?
User is not seeing it.
Excel 2000 had the AUTOSAVE.XLA Add-in
Should be in Tools>Add-ins.
If not available there, do a file search on your computer.
If not there, install from the CD.
Gord Dibben Excel MVP
On Thu, 6 May 2004 13:26:16 -0700, "Ssolano"
>In Excel 2000 was this feature available?
>User is not seeing it.
...auto hide toolbar in Excel won't work
I have the toolbar at the bottom of the screen set to 'autohide' but when I'm
in Excel sometimes it does 'hide' until the cursor is moved over it, but
mostly it won't autohide so that I cannot see the Sheet names at the bottom
of the screen. I am running Windows XP Home and Microsoft Office Professional
2003. Can anyone help please?
Every time you move your cursor too far into the bottom area, the toolbar
will pop up. Normally it disappears when you move your cursor up again. If
it does not, try clicking on you spreadsheet. If you right click o...