Hi arrow.
Not dissimilar to what I have done, except the data didnt format on the push, so did a pull kicked off by a push.
- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Reply to: SQL Server to Excel Question
Collapse
You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:
- You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
- You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
- If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
Logging in...
Previously on "SQL Server to Excel Question"
Collapse
-
It's quite a while since I did this, but I recall that I created an empty fomratted template. Then in DTS vbscript copy the template overwriting the old spreadsheet and then populated the worksheets. From memory get the headers in the template correct and then make sure you format each cell in the blank row below the headers. I populated the template with live data, made sure the first row formatted correctly, then delete all the data leaving the headers in place.
Leave a comment:
-
The SSIS in 2005 isn't any cleverer, i've had exactly the same problem when needing to generate Excel spreadsheets, there's no ability to format or do anything except for just dumping the data in.
At the initial pre-contract consultation stage I actually advised against 2005 as after listening to their requirements i thought it was a waste of money. Day 1 of the contract, they show me the spanking new 2005 Database server! Good for me though, it gets added to the CV now
Leave a comment:
-
Yep, thats what Im doing. I had hoped to do everything in DTS but having read up on the jet engine I cannot do a delete plus it 'cleverly' changes everything to text thus screwing up the refresh macro in Excel.
I have created a table called sqlReports that contains the sql statemets I want to run and a destination workbook, sheet & cell.
I have four workbooks to do like this and forty statements in total, so Im going to write a generic macro to pull the data using the a select from the sqlReports table. I know I have a connection as it's DTS that is kicking off the Excel Macro.
Seems long winded to me but hey ho, should have used 2005.
Cheers
Leave a comment:
-
run the sql nightly via a job in SQL Server to populate some staging tables
write a short VBA in excel to pull the data from the staging tables. The SQL Server user the ADO connection from VBA uses only has select access to the staging tables.
Add a refresh button to the worksheet so the user can refresh when they like, or add something to refresh the data whenever the spreadsheet is opened.
PM me if you need example VBA scriptsLast edited by Spacecadet; 6 December 2006, 10:25.
Leave a comment:
-
SQL Server to Excel Question
Right. Got 20 reports in Business Objects, which I have converted to SQL Statements.
Want to populate a spreadsheet with 20 formatted reports on a daily basis automatically as part of a DTS function run fom a scheduled Job. The Openrowset using the OLE DB Jet Engine is proving a pain in the arse as you cannot delete the data within the sheet, you have to define the table output and everything comes out as Varchar.
Short of writing an VB Script from scratch , my more preferred method of an ADO Macro in Excel calling the 20 SQL Statements from my reports table in the main DB, what are my options bearing in mind the client is too tight to pay for a reporting scheduler.
Anyone?Tags: None
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: