Data range properties
I have a problem with external data. In the Data Range Properties, under
There are 3 options after
'If the number of rows in the data range changes upon refresh:'
1 - Insert cells for new data, delete unused cells
2 - Insert entire rows for new data, clear unused cells
3 - Overwrite existing cells with new data, clear unused cells.
I want option 3 to be the default oprion but it always defaults back to
option 2. Is there any way I can change this.??
Apologies somehow managed to post twice
> I have...Table Import or Mail Merge Macro?
I need to set up (item card, assign site, assign creditor) about 250 new
items. I've been testing the table import process in the test company, but
keep getting handling exception errors when testing the item after the
"successful" import because of an obscure (for me) blank field.
All the items have the same Class ID, creditor, site, etc. They only differ
in item code, desc, curr cost, list price, gen desc, and creditor item code.
So would I have better luck recording a macro setting up the first one and
mail merging the necessary fields?
If you believe table import is ...Pivot Table
I have one issue when i get the data through the pivot table
option. after creating a pivot table, i enabled the sub-total and the
grand total in the pivot table properties. after enabling that the
sub-total and the grand total columns appear as #VALUE!.
I have office 2003 in OS 2000 proffesion.
Please help me
You have one suggestion in .excel
> Hi all,
> I have one issue when i get the data through the pivot table
> option. after creating a pivot table, i enabled the sub-total and the
> grand total in the pivot table properties....Is any group function which concatenates data?
In a query as follows I would like to concatenate data instead of
aggregating in a field which is not a part of group clause.
SELECT invoice_no, device_type, concatenate_text(device_serial),customer
group by invoice_no, device_type, customer
Full data set without grouping looks like:
SRW/1/2010, AAASSSQQWE, sn000001, ABIX
SRW/2/2010, AAASSSQQWE, MSD001XXX, ADA
SRW/2/2010, AAASSSQQWE, sn000002, ADA
SRW/3/2010, AAASSSQQWE, sn000001, ADA
SRW/4/2010, AAASSSQQWE, sn000001, ADT
I would like to have something like this after group by and "co...on NoData, show value in table
My report recordsourse is based on qrystudent where i have 2 tables.
'duration' table has 2 fields [from] and [to].
on NoData event of the report, I want to give message like 'You have not
logged in during ' [from] and [to]
If NoData, how can I give message include fields value in 'duration' table?
'duration' table has only one record.
Song Su wrote:
>My report recordsourse is based on qrystudent where i have 2 tables.
>'duration' table has 2 fields [from] and [to].
>on NoData event of the report, I want to give message l...Delete a person from the drop down in the to field
How do I delete a name from my automatic drop down menu in
the to field. If I send a mail to fx.
firstname.lastname@example.org, the next time I write mic... in
the to field, the drop down menu keeps the address in the
list. How do I delete this name again.
use the arrow buttons to select it and hit Del
Diane Poremsky [MVP - Outlook]
Author, Teach Yourself Outlook 2003 in 24 Hours
Outlook Tips: http://www.outlook-tips.net/
http://www.poremsky.com - http://www.cdolive.com
Expert Zone http://www.microsoft.com/windowsxp/expertzone
Search for answers: http://groups.go...Combining rows of data that have one cell in common
Hi everyone, let me explain...
I'm working with a large excel file (13,000+ rows) of cancer cases recorded
in the state of Florida. Each time a patient presents with a cancer, that
"case" is recorded as a row on the spreadsheet, along with a UNIQUE patient
ID assigned to that particular individual. If that same person develops
another cancer sometime later, that information appears in a new case/row,
but with the SAME patient ID.
I need to export this file to another program (SPSS v13.0) to do some
advanced statistical analysis. However, the program requires that all of t...Changing font in rich text table
I want to standardise the font of all data in a table's rich text
field. If I use:
Dim DB As Database
Dim rst As Recordset
Dim strSQL As String
Set DB = CurrentDb()
strSQL = "SELECT tblReportTermly.Aims FROM tblReportTermly;"
Set rst = DB.OpenRecordset(strSQL)
Do Until rst.EOF
!Aims.FontName = "Calibri"
!Aims.FontSize = "11"
i get "Object doesn't support this property or method". Am I
approaching this incorrectly?
...Dimension Update and Pivot table report
I have an Excel (03) Report file which uses Analysis Services (SP4) Cube.
Works fine still using AS 2000 not 2005.
When I make any changes to Dimension and process Dim. and Cube Excel file
doesn't takes my Dim changes... I have to take (drag) the changed Dim. back
to field list and bring it back to report.... then only it works.....problem
is we have 100s of Excel Cube Reports.
Error I get: The item Could not be found in OLAP Cube.
Thank you - Ashok
...Alternatives to MS Query for Pivot Tables & Cubes
I am using Excel 2003. I would like to know if there is an add-in or other
(preferably free) tool out there that I could use to create OLAP cubes for
Excel pivot tables that would have better functionality than MS Query. I find
MS Query very difficult to use and almost impossible for creating calculated
fields that can then be built into a cube. (Note: I thought I could just make
calculated fields in the pivot table itself, but now I see that Excel will
not let you do that when using an OLAP cube as a source.)
Any suggestions would be greatly appreciated.
...Money Premium 2006: Moving my data to my laptop
For reasons too numerous to go into here, I believe it likely that I'm going
to have to move my Microsoft Money 2006 Premium database from my desktop,
where it has been the past 6 years or so, to my laptop. I don't have Money
installed on my laptop, but will have to.
What I want to know is, how do I get all of that data, safely to my laptop?
In microsoft.public.money, Rod wrote:
>For reasons too numerous to go into here, I believe it likely that I'm going
>to have to move my Microsoft Money 2006 Premium database from my desktop,
>where it has been the past ...Split combined date time data #3
From file dump have combined date time cells eg "14/04/03 14:20" (value
37725.59722). Want to perform time analysis so need to split to 2 separate
cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a
fnc to do this? (Currently convert cell to value, then fncs trunc & cell
less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively)
Data/Text to Columns. Select Delimited, and click Next. Check the Space
checkbox and click Finish.
In article <D84AFF1F-CA34-456B-AA06-926914397A3C@microsoft.com>,...Font manipulation
I'm curious I can change apperance in chat window. Like the background of
the tab of my messages and different background of the tab of the different
I don't mean the background of the text.
And is there any way of increace the spacing of lines between them? The
lines are really near...
For more understanding: I wish to control it somewhere with like a css
command "Padding" or "margin".
font color: gold;
font size: 1.5em;
font color: black;
I think y...Getting a value from large table.
Hi, I have a data table where column A = Make (named MAKE), B = Model (named
MODEL), Row 1 = Model Year (named MODELYR).
On a different sheet, I have a list of miscellaneous combinations. On this
sheet, column A contains the make, B the Model and C the Model Year.
How can I get the contents from the table to be populated in column D for my
I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but
continue to get the #Value error. I have also array-entered each of the
I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1.
TIA, Br...VBA Import from Access using ODBC
I am trying to import from Access using ODBC - then I want to
basically concatenate two of the columns.
If I run my macro it actually runs the concatenate part first, then
brings in the odbc information.
If I put a pause or step through it - it works no problem. Code is
What am I doing wrong?
Thanks in advance...
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & fileandpath & ";DefaultDir="
& spath & "; DriverId=281;FIL=MS Access;MaxBufferSize =2048;
PageTimeout =5;")), Dest...Using ad hoc table to select which records to print
Hi all. I have a situation I could use some help with. I have a
database used for a high-volume electronics repair business, and I'm
trying to streamline pre-shipment paperwork functions for customer
service. Depending on the customer and order type, up to 6 different
reports might be printed for each order.
I've set up a table populated on an ad hoc basis by customer service
through a continuous form with a text box for the order identification
number and a check box for each report to select which to print. Each
of these reports might also be printed from elsewhere in the database
(...Re: Debtor Period Summary Table RM00104
i was doing some customer monthly sales report, and i come across this
it has a field called HISTTYPE, History Type. and it has a value or either 0
or 1, for the financial year of 2006, which is also the 1st year GP started
in the company, there are some customers having both 0 and 1 for the same
customer1, period5, year2006, histtype1, $309
customer1, period5, year2006, histtype0, $253
could anyone shed some light on what the history type is? coz in GP, when
reviewing past month sales, seems it brings up figers in the row where
histtype is 1
...Moving data across columns
I hope someone can help me. I have a file containig contact informatio
with all the data in column 1 i.e. name in cell A:1, complete addres
in cell A:2, phone number in cell A:3.
I need the data in a database format i.e. name in A:1, address in B:1
city in C:1, zip in D:1 and phone in E:1. I have about 1,500 files s
the Macro would have to move down and repeat the process.
Also, since A:2 contains the full address, the macro should also brea
apart this information so that the city, state and zip is in its ow
I may need two separate macros to accomplish this. Does a...Field Service #3
Has anybody had this problem in field service.
1) I create a couple of new service calls
2) I add parts to them
3) I then go to inventory requirements and mark p = po and c = consolidate
then create PO
It is important to note that I am purchasing from the same supplier.
In the next screen I make sure that the first record is not marked for
consolidation and the rest are ( which means the firts record should create a
new PO and the rest should add lines to the PO.
However this is not the case Field service will at random create all as new
po's or next time round will create 2 new...changing proportion of chart and data table
I have a chart with 19 lines of data and I have been requested to print the
data table with the chart. When I add the data table so that I can read the
text (e.g., 11 or 12 pt font), I cannot see all the data table line in the
page viewer or print it all out. I can only print out all the data table
lines along with the chart if I make the font so small that it renders the
data table useless (or similarly, if I shrink the size of the size the chart
so that what I am looking at is so small you can't read the labels anymore).
In either case, when I do this I also I end up with half ...performance issues linking sql 2000 table in access
I hope I'm just overlooking the obvious and there is an easy solution for my
issue. I scanned the messages here and didn't see anything that looked
My issues is I have a SQL database with a table containing a little over 2.5
million rows linked to access. We typically open the linked table and filter
for a subset of records to modify in table view. The database was recently
moved to a new faster server with more memory and a bigger hard drive. After
the initial move performance when opening and using the table seemed more
robust than on the older slower machine. The ...Pivot Table Filter Error
When I filter data in my pivot table in Excel 2007, it is splitting up the
same data. For instance, if there are numbers describing a job poisition the
filter is splitting the people who are in group 12. instead of showing me 25
people in group 12 it is showing two groups of 12's and splitting them up as
23 and 2, instead of one group of 25.
I formatted the column as all numbers and that didn't work.
Any other suggestions?
...How to return the address of the range of plotted data on an XY scatter chart?
I can't figure out the VBA code that will return the address of the
range of data plotted on an embedded chart on say sheet1.
Everything I try just results in the "object does not support this
property or method" error message.
I've been recording macros for clues as well as studying the Object
Browser, all to no avail.
To get the ranges used by a chart data series use the formula property.
Use this in the immediate window.
See John Walkenbach's page on...pivot table #5
I have a pivot table and would like to view and change my SQL and database
Are there any way to open it to debug and verify it?
I know that I can edit SQL from MS SQL if I use MS SQL to retreive data.
I could not figure to open the cub file to deit and debug.
Your inforamtion is great appreciated,
The free Pivot Play add-in, located at Debra Dalgleish's website, may help:
It allows you to view/edit the connection and SQL code for Pivot Tables that
use external data sources. It has many other features, too.
Does that help?
-----...Importing data from MS Access to Outlook
I have tried unsuccessfully to send text message from MS Access to Outlook
Express (MS Access has a SendObject command to send data. It works but will
only send 255 or 256 characters, the rest is truncated). I'm wondering if
the opposite will work. Can I set up a form text message in Outlook and
import certain data from my MS Access database (someone's name, email, etc)
or am I way off base?
Thanks in advance for any help.
"Ceedy" <Ceedy@discussions.microsoft.com> wrote in message