ExcelBanter - VBA and SQL dump changing the cell types in other worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VBA and SQL dump changing the cell types in other worksheets (https://www.excelbanter.com/excel-programming/360429-vba-sql-dump-changing-cell-types-other-worksheets.html)

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

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

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

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

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

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