Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and SQL dump changing the cell types in other worksheets
I have written a piece of code which runs a SQL script and then outputs this
to Sheet 2 of my workbook. The code contains no cell formatting as this has all been previously applied to the columns manually. However when i run the code using a button on Sheet 1, the types which have been applied to the colmuns in sheet 2 are also applied to the same columns in sheet 1. No matter how many times i re-format the cells, each time i run the code it does the exact same thing. Any help for this would be great as it is the last problem I have to get around and it is driving me nuts. Cheers, -- Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and SQL dump changing the cell types in other worksheets
Hi Bob,
are you sure you are applying formatting to cells on sheet2 only in your code? Maybe posting part of code would be helpful. Regards, Ivan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and SQL dump changing the cell types in other worksheets
Are your sheets grouped?
-- Regards, Tom Ogilvy "Bob" wrote: I have written a piece of code which runs a SQL script and then outputs this to Sheet 2 of my workbook. The code contains no cell formatting as this has all been previously applied to the columns manually. However when i run the code using a button on Sheet 1, the types which have been applied to the colmuns in sheet 2 are also applied to the same columns in sheet 1. No matter how many times i re-format the cells, each time i run the code it does the exact same thing. Any help for this would be great as it is the last problem I have to get around and it is driving me nuts. Cheers, -- Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and SQL dump changing the cell types in other worksheets
My Sheets aren't grouped and the code does not apply any formating.
As requested by Ivan I have posted the code: Sub RunSQL() ' Create a connection object Dim cnAssyst_Dev As ADODB.Connection Set cnAssyst_Dev = New ADODB.Connection ' Provide the connection string Dim strConn As String 'Use the SQL Server OLE DB Provider strConn = "PROVIDER=SQLOLEDB;" 'Connect to the Pubs database on the local server strConn = strConn & "DATA SOURCE=CAIRN10;INITIAL CATALOG=SQLDB;" 'Use an integrated login strConn = strConn & "User Id=USER;Password=PASS;" 'Now open the connection cnAssyst_Dev.Open strConn 'Get the start and end dates from Sheet 1 Dim startDate As String Dim endDate As String startDate = Range("Sheet1!B17") endDate = Range("Sheet1!B18") 'Delete old SQL data in Sheet 2 Sheets("Sheet2").Select Range("K2:V2000").Select Selection.ClearContents Range("K2").Select Sheets("Sheet1").Select 'Set the office to be reported on Dim office As String office = "OFFICE" 'Provide the string to hold the SQL Command Dim sqlString As String 'The completed SQL Command sqlString = sqlString & "SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due, incident.inc_resolve_act, " sqlString = sqlString & "incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc, incident.date_logged, " sqlString = sqlString & "incident.time_to_resolve, inc_data.total_service_time, incident.inc_resolve_sla, inc_cat.inc_cat_sc " sqlString = sqlString & "FROM ((((incident INNER JOIN inc_data ON " sqlString = sqlString & "incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr " sqlString = sqlString & "ON incident.ass_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON " sqlString = sqlString & "incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON " sqlString = sqlString & "incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON " sqlString = sqlString & "assyst_usr.usr_group_id=usr_group.usr_group_i d WHERE sla.sla_sc<'' AND " sqlString = sqlString & "usr_group.usr_group_sc='" & office & "' AND ((incident.date_logged={ts '" & startDate & " 00:00:00'} " sqlString = sqlString & "AND incident.date_logged<{ts '" & endDate & " 00:00:00'}) OR (incident.inc_close_date={ts '" & startDate & " 00:00:00'} " sqlString = sqlString & "AND incident.inc_close_date<{ts '" & endDate & " 00:00:00'})OR(incident.inc_status = 'o')or(incident.inc_status = 'p')) ORDER BY sla.sla_sc" ' Create a recordset object Dim rsAssyst_Dev As ADODB.Recordset Set rsAssyst_Dev = New ADODB.Recordset With rsAssyst_Dev ' Assign the Connection object .ActiveConnection = cnAssyst_Dev ' Extract the required records .Open sqlString ' Copy the records into cell K2 on Sheet 2 Sheet2.Range("K2").CopyFromRecordset rsAssyst_Dev ' Tidy up .Close End With cnAssyst_Dev.Close Set rsAssyst_Dev = Nothing Set cnAssyst_Dev = Nothing End Sub As you will notice i have dumped the data into cell K2. This is so that the formating in columns A to J remain intacted for the final report shown on Sheet1 -- Bob "Tom Ogilvy" wrote: Are your sheets grouped? -- Regards, Tom Ogilvy "Bob" wrote: I have written a piece of code which runs a SQL script and then outputs this to Sheet 2 of my workbook. The code contains no cell formatting as this has all been previously applied to the columns manually. However when i run the code using a button on Sheet 1, the types which have been applied to the colmuns in sheet 2 are also applied to the same columns in sheet 1. No matter how many times i re-format the cells, each time i run the code it does the exact same thing. Any help for this would be great as it is the last problem I have to get around and it is driving me nuts. Cheers, -- Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and SQL dump changing the cell types in other worksheets
Just for fun, remove this line
Sheets("Sheet1").Select or move it to the end of the macro and change sheet2.copyfromrecordset to sheets(sheet2).copyfromrecordset But you are correct. I don't see anything obvious. -- Regards, Tom Ogilvy "Bob" wrote: My Sheets aren't grouped and the code does not apply any formating. As requested by Ivan I have posted the code: Sub RunSQL() ' Create a connection object Dim cnAssyst_Dev As ADODB.Connection Set cnAssyst_Dev = New ADODB.Connection ' Provide the connection string Dim strConn As String 'Use the SQL Server OLE DB Provider strConn = "PROVIDER=SQLOLEDB;" 'Connect to the Pubs database on the local server strConn = strConn & "DATA SOURCE=CAIRN10;INITIAL CATALOG=SQLDB;" 'Use an integrated login strConn = strConn & "User Id=USER;Password=PASS;" 'Now open the connection cnAssyst_Dev.Open strConn 'Get the start and end dates from Sheet 1 Dim startDate As String Dim endDate As String startDate = Range("Sheet1!B17") endDate = Range("Sheet1!B18") 'Delete old SQL data in Sheet 2 Sheets("Sheet2").Select Range("K2:V2000").Select Selection.ClearContents Range("K2").Select Sheets("Sheet1").Select 'Set the office to be reported on Dim office As String office = "OFFICE" 'Provide the string to hold the SQL Command Dim sqlString As String 'The completed SQL Command sqlString = sqlString & "SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due, incident.inc_resolve_act, " sqlString = sqlString & "incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc, incident.date_logged, " sqlString = sqlString & "incident.time_to_resolve, inc_data.total_service_time, incident.inc_resolve_sla, inc_cat.inc_cat_sc " sqlString = sqlString & "FROM ((((incident INNER JOIN inc_data ON " sqlString = sqlString & "incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr " sqlString = sqlString & "ON incident.ass_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON " sqlString = sqlString & "incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON " sqlString = sqlString & "incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON " sqlString = sqlString & "assyst_usr.usr_group_id=usr_group.usr_group_i d WHERE sla.sla_sc<'' AND " sqlString = sqlString & "usr_group.usr_group_sc='" & office & "' AND ((incident.date_logged={ts '" & startDate & " 00:00:00'} " sqlString = sqlString & "AND incident.date_logged<{ts '" & endDate & " 00:00:00'}) OR (incident.inc_close_date={ts '" & startDate & " 00:00:00'} " sqlString = sqlString & "AND incident.inc_close_date<{ts '" & endDate & " 00:00:00'})OR(incident.inc_status = 'o')or(incident.inc_status = 'p')) ORDER BY sla.sla_sc" ' Create a recordset object Dim rsAssyst_Dev As ADODB.Recordset Set rsAssyst_Dev = New ADODB.Recordset With rsAssyst_Dev ' Assign the Connection object .ActiveConnection = cnAssyst_Dev ' Extract the required records .Open sqlString ' Copy the records into cell K2 on Sheet 2 Sheet2.Range("K2").CopyFromRecordset rsAssyst_Dev ' Tidy up .Close End With cnAssyst_Dev.Close Set rsAssyst_Dev = Nothing Set cnAssyst_Dev = Nothing End Sub As you will notice i have dumped the data into cell K2. This is so that the formating in columns A to J remain intacted for the final report shown on Sheet1 -- Bob "Tom Ogilvy" wrote: Are your sheets grouped? -- Regards, Tom Ogilvy "Bob" wrote: I have written a piece of code which runs a SQL script and then outputs this to Sheet 2 of my workbook. The code contains no cell formatting as this has all been previously applied to the columns manually. However when i run the code using a button on Sheet 1, the types which have been applied to the colmuns in sheet 2 are also applied to the same columns in sheet 1. No matter how many times i re-format the cells, each time i run the code it does the exact same thing. Any help for this would be great as it is the last problem I have to get around and it is driving me nuts. Cheers, -- Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and SQL dump changing the cell types in other worksheets
Tom thanks very much your suggestion worked.
I had to use Range("Sheet2!K2").CopyFromRecordset rsAssyst_Dev Instead of sheets(sheet2).copyfromrecordset But this and moving the Sheets("Sheet1").select statement to the end of the code has worked. Thanks for your help -- Bob "Tom Ogilvy" wrote: Just for fun, remove this line Sheets("Sheet1").Select or move it to the end of the macro and change sheet2.copyfromrecordset to sheets(sheet2).copyfromrecordset But you are correct. I don't see anything obvious. -- Regards, Tom Ogilvy "Bob" wrote: My Sheets aren't grouped and the code does not apply any formating. As requested by Ivan I have posted the code: Sub RunSQL() ' Create a connection object Dim cnAssyst_Dev As ADODB.Connection Set cnAssyst_Dev = New ADODB.Connection ' Provide the connection string Dim strConn As String 'Use the SQL Server OLE DB Provider strConn = "PROVIDER=SQLOLEDB;" 'Connect to the Pubs database on the local server strConn = strConn & "DATA SOURCE=CAIRN10;INITIAL CATALOG=SQLDB;" 'Use an integrated login strConn = strConn & "User Id=USER;Password=PASS;" 'Now open the connection cnAssyst_Dev.Open strConn 'Get the start and end dates from Sheet 1 Dim startDate As String Dim endDate As String startDate = Range("Sheet1!B17") endDate = Range("Sheet1!B18") 'Delete old SQL data in Sheet 2 Sheets("Sheet2").Select Range("K2:V2000").Select Selection.ClearContents Range("K2").Select Sheets("Sheet1").Select 'Set the office to be reported on Dim office As String office = "OFFICE" 'Provide the string to hold the SQL Command Dim sqlString As String 'The completed SQL Command sqlString = sqlString & "SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due, incident.inc_resolve_act, " sqlString = sqlString & "incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc, incident.date_logged, " sqlString = sqlString & "incident.time_to_resolve, inc_data.total_service_time, incident.inc_resolve_sla, inc_cat.inc_cat_sc " sqlString = sqlString & "FROM ((((incident INNER JOIN inc_data ON " sqlString = sqlString & "incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr " sqlString = sqlString & "ON incident.ass_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON " sqlString = sqlString & "incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON " sqlString = sqlString & "incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON " sqlString = sqlString & "assyst_usr.usr_group_id=usr_group.usr_group_i d WHERE sla.sla_sc<'' AND " sqlString = sqlString & "usr_group.usr_group_sc='" & office & "' AND ((incident.date_logged={ts '" & startDate & " 00:00:00'} " sqlString = sqlString & "AND incident.date_logged<{ts '" & endDate & " 00:00:00'}) OR (incident.inc_close_date={ts '" & startDate & " 00:00:00'} " sqlString = sqlString & "AND incident.inc_close_date<{ts '" & endDate & " 00:00:00'})OR(incident.inc_status = 'o')or(incident.inc_status = 'p')) ORDER BY sla.sla_sc" ' Create a recordset object Dim rsAssyst_Dev As ADODB.Recordset Set rsAssyst_Dev = New ADODB.Recordset With rsAssyst_Dev ' Assign the Connection object .ActiveConnection = cnAssyst_Dev ' Extract the required records .Open sqlString ' Copy the records into cell K2 on Sheet 2 Sheet2.Range("K2").CopyFromRecordset rsAssyst_Dev ' Tidy up .Close End With cnAssyst_Dev.Close Set rsAssyst_Dev = Nothing Set cnAssyst_Dev = Nothing End Sub As you will notice i have dumped the data into cell K2. This is so that the formating in columns A to J remain intacted for the final report shown on Sheet1 -- Bob "Tom Ogilvy" wrote: Are your sheets grouped? -- Regards, Tom Ogilvy "Bob" wrote: I have written a piece of code which runs a SQL script and then outputs this to Sheet 2 of my workbook. The code contains no cell formatting as this has all been previously applied to the columns manually. However when i run the code using a button on Sheet 1, the types which have been applied to the colmuns in sheet 2 are also applied to the same columns in sheet 1. No matter how many times i re-format the cells, each time i run the code it does the exact same thing. Any help for this would be great as it is the last problem I have to get around and it is driving me nuts. Cheers, -- Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I hide unused file types from file types list in save dial | Excel Discussion (Misc queries) | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
Changing footers on all worksheets without changing print set up | Excel Discussion (Misc queries) | |||
Changing chart types with series | Charts and Charting in Excel | |||
Changing data types on Line/bar with two axes | Charts and Charting in Excel |