Proper method for Excel/Query/Macro reporting

Hi All

1st of all sorry for the xpost, but I honestly don't know which group to
post this query in.

Basically I need to do an Excel report as follows:

* User enters an sales rep code into say cell A1 and then clicks a button on
the spreadsheet.

* An SQL query is executed to extract all sales rep details and put it in
the spreadsheet starting from say cell C1 onwards.

* one blank row inserted

* Another SQL query is executed to extract all sales rep's customer details
and put it in the spreadsheet starting after the 1st query's data + the 1
blank line.

* one blank row inserted

* Another SQL query is executed to extract all sales rep's notes and put it
in the spreadsheet starting after the 2nd query's data + the 2nd blank line.

* one blank row inserted

* Another SQL query is executed to extract all sales rep's prices and put it
in the spreadsheet starting after the 3rd query's data + the 3rd blank line.

If I was to do this in ASP I could do it all in 1 ADO connection and close
the connection once I'd finished the extraction to make it tidy.

Can somebody once and for all let me know the syntax for doing this sort of
thing much akin to how ASP/ADO works.

Many thanks.

Rgds

Laphan


0
news782 (11)
10/29/2003 11:33:12 PM
excel 39879 articles. 2 followers. Follow

1 Replies
471 Views

Similar Articles

[PageSpeed] 48

Hi,

> * User enters an sales rep code into say cell A1 and then clicks a button
on
> the spreadsheet.
>
> [...]

You can do this by creating Visual Basic code that executes the queries as
you mentioned. If you do not want to write such code from scratch, I suggest
you have a look at ExcelSQL. It allows you to enter SQL code in Excel cell
notes and then execute the code with a menu command (or from a macro). For
more information, see http://www.excelsql.com/

ExcelSQL is good choice if you know SQL and can write SQL comfortably. If
not, have a look at Microsoft Query instead.

--

Mikko Noromaa (mikkon@excelsql.com)
- SQL in Excel, check out ExcelSQL! - see http://www.excelsql.com -

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3fa04d4f_3@127.0.0.1...
> Hi All
>
> 1st of all sorry for the xpost, but I honestly don't know which group to
> post this query in.
>
> Basically I need to do an Excel report as follows:
>
> * User enters an sales rep code into say cell A1 and then clicks a button
on
> the spreadsheet.
>
> * An SQL query is executed to extract all sales rep details and put it in
> the spreadsheet starting from say cell C1 onwards.
>
> * one blank row inserted
>
> * Another SQL query is executed to extract all sales rep's customer
details
> and put it in the spreadsheet starting after the 1st query's data + the 1
> blank line.
>
> * one blank row inserted
>
> * Another SQL query is executed to extract all sales rep's notes and put
it
> in the spreadsheet starting after the 2nd query's data + the 2nd blank
line.
>
> * one blank row inserted
>
> * Another SQL query is executed to extract all sales rep's prices and put
it
> in the spreadsheet starting after the 3rd query's data + the 3rd blank
line.
>
> If I was to do this in ASP I could do it all in 1 ADO connection and close
> the connection once I'd finished the extraction to make it tidy.
>
> Can somebody once and for all let me know the syntax for doing this sort
of
> thing much akin to how ASP/ADO works.
>
> Many thanks.
>
> Rgds
>
> Laphan
>
>


0
mikkon (1)
10/30/2003 12:59:52 PM
Reply:

Similar Artilces:

CRM 3.0 Reporting errors, unable to access externally
Installed CRM 3.0 on SQL 2005 SP1 on Windows SBS 2003 SP1, and am unable to get the reporting features to work. When viewing the http://server:5555 and clicking on reports, I get a popup "Error An error has occurred. For more information, contact your system administrator." I am able to access the /reports and /reportserver folders. I have done days worth of tinkering, research, reinstalls, and have not been able to get this working. Also when trying to access the site externally, it begins to launch, I login, then the page and launcher close. Any ideas? I appreciate any help. ...

Tab color in Excel 2003
I set up all my worksheet tabs with color. But for some reason when I go to format and then sheet, my 'tab color' is greyed out. I see this graying out only when the workbook is protected (Tools, Protection) or the workbook is shared (Tools, Share Workbook). -- Jim "slinge" <slinge@discussions.microsoft.com> wrote in message news:45A695EB-EA20-4B47-8538-02AAB94FD29B@microsoft.com... |I set up all my worksheet tabs with color. But for some reason when I go to | format and then sheet, my 'tab color' is greyed out. Thank you Jim but the worksheet is not p...

New to CRM
In the Contact/Accounts/Leads entry form, is there a preferred or recommend way of typing in the phone, fax, etc. numbers. E.g. 604 222-2222 or 6042223333 or 604-222- 3333? Also in the Address when creating, the Address Name would be the building E.g. Shaw Tower, and for addresses, what is recommended - Address 1 for 123 Main Street, and Address 2 for P.O. Box? With this in mind, what is the best way to enter the Address in Outlook Contacts to ensure a smooth conversion? Thanks! Shauna Microsoft CRM does not care how you enter these values. Currently there is no formatting checks f...

How do I work with Excel 2007 and htm files?
I created a htm file in Excel 97-2003 and I was able to access my tables and change the information around. When I open the file in the new Excel 2007, it changes a lot of my numbers to dates and some other crazy numbers I have no idea where they come from. I've tried going to excel options and turning off automatic calculations but that doesn't help. How do I get excel to not change any of the information in the cells of my workbook? [In the previous excel all you had to do was insert an apostrophe ' to prevent any changes.] Example: Supposed to say: 01-05 Says: 39087 ...

Excel OLE data in PPT reverting to OLD data after macro updation
Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) ...

excel program
how can we use this program for drawing a graph by math formula? Thanks Set up two columns, one containing the x values and the other containing f(x) values. The x column should be to the left of the f(x) column, but they do not have to be adjacent. Select both columns of data and on the menu select Insert|Chart|XY (Scatter) and select the first example in the second column of "Chart sub-types" ("Scatter with data points connected by smoothed Lines without markers.") Follow the prompts selecting any desired customizations. Jerry Niloufar Esfandi wrote: >...

Hiding a userform properly!
Please take a look at the code below which my workbook is using: '-------------------------------------------------------- Private Sub Workbook_Open() Userform1.Show End Sub '------------------------------------------------------- Private Sub UserForm_Initialize() MsgBox "Initialize stuff here" Userform1.Hide MsgBox "Okay, form1 is hidden now!" Userform1.Show End Sub '-------------------------------------------------------- So....obviously this code will not work. I'm guessing that the second call to "Userform1.Show...

Formula from excel to VBA
I have a date formula that I need to rewrite in VBA. Essentially i takes a date expressed in the format:m/d/yyyy h:mm, and then adds hour to it incrementally down rows. The first date is a constant, th second date is the addition of one hour but the third date onward is big formula. Effectively the formula keeps adding hours but takes int account daylight savings in the months of april and october. I am not sure how to recreate this in VBA due to the brackets used i excel In excel it looks like this: A1 = 1/1/2002 0:00 B2 = a1+1/24 C =A2+1/24+IF(AND(MONTH(A2)=4,WEEKDAY(A2)=1,HOUR(A2)=1,...

Problem using Visual Studio 2005 to write reports
I have visual studio 2005. When I try to install the SRS from my CRM disk it does not install the Report Designer component because it is looking for visual studio 2003. All the documentation I have including SDK3.0 state you can use visual studio 2005. Am I missing something?? ------=_NextPart_0001_417611DB Content-Type: text/plain Content-Transfer-Encoding: 7bit <ril@discussions.microsoft.com> wrote: > I have visual studio 2005. When I try to install the SRS from my CRM disk it > does not install the Report Designer component because it is looking for > visual stu...

Macro wait
Is there a macro command that I could add into a macro to tell it to wait for xx minutes (while something is running in the background)? Or maybe for it to wait for all processes to finish before continuing? JP, You can insert a pause into a macro, but this is as close as you will get, since you will need to manually trigger it to continue. Now if what you are looking for is to execute a macro (which in turn performs some actions) on a scheduled basis after some other process is completed, you can use the Macro Scheduler in Professional Services Tools Library. It sounds, however, you ...

Excel Conditional Formula Problem
undefined::undefined::undefined I'm modeling the development of a 57 unit housing project, and need t determine the number of units under construction in any given month The inputs are as follows: C7: Total Units 57 C8: Unit Construction Start Month: 2 C9: Unit Construction Starts per Month: 2 C10: Unit Construction Cycle (months): 5 Row 48 contains the number of housing starts by month. There are 3 columns, which represent the total months in the project. I've writte a formula which indicates the number of housing starts per month. C4 through AL46...

Automating export to Excel
I'm trying to export data from Access into an existing Excel file with a push of a button. These are the steps that I need to perform and would like to do it all from Access, meaning all macors or coding. I'm using MS Office 2003. -Run & export access query to a specific folder location & over write any existing file (if it cant be overwritten then use the same name & add 1 (or increase by 1) to the end of the name) -Open an existing excel spreadsheet. If the spreadsheet is already open, then open as read only. (the excel spreadsheet will automatically populate a...

autoshape on my chart got locked
Accidently, I pressed a combination of alt/shift/ctrl and autoshape drawn on the sheet (flowchart) got locked, moving mouse on any elemnet of the drawing shows a circle with a diagnol, symbolizing not available mark ...

Macro that opens files with differing extensions
I've got a macro that currently prompts the user to open a .CSV file. The command I use for this is: FName = Application.GetOpenFilename("Excel Files (*.csv),*.csv") This command only displays .CSV files in the "open" window that's displayed to the user. I'd like to alter this somewhat so that the user can open more than just .csv files. I'd like for it to list the .txt and .xls files as well. What would the adjusted GetOpenFilename command look like for this? Many thanks. VBA's help is pretty good for this: Dim FName As Variant FName =...

visual fit method
how do you calculate the accounting cost using the visual fit method and the high-low method including graphs Were you able to figure out the visual fit method -- sony ----------------------------------------------------------------------- sonya's Profile: http://www.officehelp.in/member.php?userid=489 View this thread: http://www.officehelp.in/showthread.php?t=75399 Posted from - http://www.officehelp.i ...

Reporting by size / color (colour)
Hi there. Am I missing something in terms of reporting on size / color (colour) from our system? We are running RMS 2.0 and many of our items are matrix (clothing/footwear). I want to be able to run reports that show me the size / colour of the item - and not just the description. For example: We have an item: M running shoe - it comes in 2 colours and 8 sizes (16 sku's). We have sold 600 units. I want to know which colour / size is the most popular. I can't seem to find a report that shows it. The only thing I can think of is to extract this information in 2 separate files fr...

PROPER
I need to change the info in a column. Right now it is all uppercase and I need for it to be like a PROPER name. I tried the PROPER function and that is not working. I need to do the complete column. I am a novice at excel. Please help If the data is in A1, =PROPER(A1) and copy down, Regards, "Annie B" <anonymous@discussions.microsoft.com> wrote in message news:3b8e01c4a4be$c8587b80$a401280a@phx.gbl... > I need to change the info in a column. Right now it is > all uppercase and I need for it to be like a PROPER name. > I tried the PROPER function and that is...

Excell cell format
I am editing a spreadsheet which the original user used cell formatting to indicate postives. It looks very pretty!! Is there a function I can use,as it contains over 3000 row, to replace the shaded (filled but blank) cells with yes? Not sure how the original user used cell formatting to indicate positives, but you could use Custom Formatting: Format/Cells/Number/Custom "Yes";;;@ will display "Yes" in any cell with a positive number as a value. If you need the *value* to be "Yes", you'll need to explain what gives the cells the value in the ...

Images with transparent areas don't print properly.
I have a project to make an A4 poster with Images and text. 2 of the images have transparent backgrounds created in an imaging package (Paint Shop Pro) as .PNG files. When I insert these images into the Project they display fine but don't print properly - only a vague outline of the darker parts print. However on Print Preview the document looks perfect. The other non-transparent area images on the page print fine. I am using Publisher 2003 on Windows XP Pro printing to a networked OKI C5200N Colour laser printer on A4 size. What could be wrong here? I would suspect the printe...

.csv spreadsheet To Normal Excel Spreadsheet Format: How Please ?
Hello, Using Excel 2007. I have a .csv formatted spreadsheet I downloaded. Is there any "straightforward" way to convert this to the normal Excel configuration format ? Thanks, Bob Open the file in excel. Save the file as a .xlsx (or .xlsm if you add macros). On 06/13/2010 14:46, Bob wrote: > Hello, > > Using Excel 2007. > > I have a .csv formatted spreadsheet I downloaded. > > Is there any "straightforward" way to convert this to the normal Excel > configuration format ? > > Thanks, > Bob -- Dave Peterson On 6/13/2010 4:00 PM, Dav...

Copying Data from MS Word into Excel #2
Thanks Sue. Unfortunatley the Word documents aren't tabled. : -- OptionTrade ----------------------------------------------------------------------- OptionTrader's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1495 View this thread: http://www.excelforum.com/showthread.php?threadid=26583 Could you convert it to Tables in Word (Table>Convert>Text To Table)? -- HTH RP "OptionTrader" <OptionTrader.1djqom@excelforum-nospam.com> wrote in message news:OptionTrader.1djqom@excelforum-nospam.com... > > Thanks Sue. > > Unfort...

Convert Excel list
I have an Excel mailing list ... entries on on individual lines. Is it possible to convert this list to the following format? me@mydomain.com,you@yourdomain.com,them@theirdomain.com, on and on, comma separated but with no spaces? Many thanks, LinK If less than 256, Copy ,Paste Special, Transpose, Save as .CSV. LinK wrote: > I have an Excel mailing list ... entries on on individual lines. > > Is it possible to convert this list to the following format? > me@mydomain.com,you@yourdomain.com,them@theirdomain.com, on and on, > comma separated but with no spaces? > > M...

Links with excel
Is there a way to get an outlook calender event to automatically produce if an excel spreadsheet field gets populated with a particular character? You'll have to use VBA for this type of custom events. You'll have to start with Excel validation to trigger the event and look for the "particular character". If that condition is met, then you'd continue with creating the calendar item in Outlook. For help with the first part, ask in an Excel newsgroup that discusses VBA. If you need help with the Outlook part, you'll can ask in the outlook.program_vba ne...

Excel 2003
I need to set up a workbook with one worksheet per month for twelve months. There will be formulas in each worksheets, two of which need to carry forward totals from the previous month's worksheet. How do I link worksheets in this way? How do I replicate that resulting formula on ALL the worksheets without entering the formula in every single worksheet? Let's say it's February and you need to link to the January totals. Assume also that the tabs in your workbook are called Jan Feb Mar etc. Click on the Feb tab, and land the cell pointer on the cell that hold the January data. S...

Collation Problem in a Union Query
Hi, I have two queries that I'm using UNION on. The queries run fine individually but when I use UNION, it gives me an error complaining about collation. In the first query, I had to use COLLATE SQL_Latin1_CP1_I_AS on one of the columns. Thanks for your help. On Wed, 24 Feb 2010 10:37:01 -0800, mavrick_101 <mavrick101@discussions.microsoft.com> wrote: >Hi, > >I have two queries that I'm using UNION on. > >The queries run fine individually but when I use UNION, it gives me an error >complaining about collation. > >In the fir...