Strangest formatting problem in Excel worksheet

[herjari]

Dear All,

I'm having a very strange problem exporting to Excel. I have the following code (shown below this email) which will work perfectly the first time that I run it, but if I want to export it again, I need to close the program (not only the form) and run it again. If I don't do this (close/open), everything will work perfectly excepto for this 2 parts:

1) ActiveWindow.DisplayGridlines = False

2)
Dim rwindex As Long, colindex As Long
For rwindex = 6 To 17
For colindex = 2 To 15
With Worksheets("Indicador&quot.Cells(rwindex, colindex)
If .Value = "" Then .Value = "0"
.NumberFormat = "######0"
End With
Next colindex
Next rwindex

THANK YOU VERY VERY MUCH IN ADVANCE FOR YOUR HELP!!

Hernan

The relevant form's code is here:

General (Declarations)

Option Explicit
Dim sAcDocFecha As String, sAADoFecha As String
Dim xlApp As New Excel.Application
Dim xlWorkBK As Excel.Workbook


Private Sub Form_Unload(Cancel As Integer)

Set xlApp = Nothing
Set xlWorkBK = Nothing
End Sub

Private Sub cmdExportar_Click()

Dim oRecordset As ADODB.Recordset
Set oRecordset = New ADODB.Recordset
Dim CurrentRow As Long
Dim Currentcol As Integer
Dim i As Integer
Dim xlApp As New Excel.Application
Dim xlWorkBK As Excel.Workbook
Dim sSql As String

On Error Resume Next
Me.MousePointer = 11

If Not DatosCorrectos Then
Exit Sub
End If

cmdExportar.Enabled = False
cmdImprimir.Enabled = False
cmdSalir.Enabled = False

FiltrarFecha

sSql = "SELECT Museos, TotalActiv, TotalProm, CantAct1, TotalProm1, CantAct2, TotalProm2, CantAct3, TotalProm3, CantAct4, TotalProm4, CantAct5, TotalProm5, CantAcT6, TotalProm6 FROM " & _
"(SELECT 1 as ORD, 1 AS Temporal, 'Total *' AS Museos, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE 1=1 " + CStr(sAcDocFecha) + &quot AS TotalActiv, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE 1=1 " + CStr(sAADoFecha) + &quot AS TotalAsist, (TotalAsist / TotalActiv) AS TotalProm, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocTipoAcDoc = 1 " + CStr(sAcDocFecha) + &quot AS CantAct1, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoTipoAcDocID = 1 " + CStr(sAADoFecha) + &quot AS CantAsist1, (CantAsist1 / CantAct1) AS TotalProm1, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocTipoAcDoc = 2 " + CStr(sAcDocFecha) + &quot AS CantAct2, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoTipoAcDocID = 2 " + CStr(sAADoFecha) + &quot AS CantAsist2, (CantAsist2 / CantAct2) AS TotalProm2, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocTipoAcDoc = 3 " + CStr(sAcDocFecha) + &quot AS CantAct3, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoTipoAcDocID = 3 " + CStr(sAADoFecha) + &quot AS CantAsist3, (CantAsist3 / CantAct3) AS TotalProm3, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocTipoAcDoc = 4 " + CStr(sAcDocFecha) + &quot AS CantAct4, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoTipoAcDocID = 4 " + CStr(sAADoFecha) + &quot AS CantAsist4, (CantAsist4 / CantAct4) AS TotalProm4, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocTipoAcDoc = 5 " + CStr(sAcDocFecha) + &quot AS CantAct5, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoTipoAcDocID = 5 " + CStr(sAADoFecha) + &quot AS CantAsist5, (CantAsist5 / CantAct5) AS TotalProm5, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocTipoAcDoc = 6 " + CStr(sAcDocFecha) + &quot AS CantAct6, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoTipoAcDocID = 6 " + CStr(sAADoFecha) + &quot AS CantAsist6, (CantAsist6 / CantAct6) AS TotalProm6 " & _
"FROM Museos WHERE Museos.MuID = 90 UNION "

sSql = sSql & "SELECT 2 as ORD, 1 AS Temporal, Museos.MuInicial AS Museos, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocMuseoID = Museos.MuID " + CStr(sAcDocFecha) + &quot AS TotalActiv, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoMuseoID = Museos.MuID " + CStr(sAADoFecha) + &quot AS TotalAsist, (TotalAsist / TotalActiv) AS TotalProm, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocMuseoID = Museos.MuID AND AcDocTipoAcDoc = 1 " + CStr(sAcDocFecha) + &quot AS CantAct1, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoMuseoID = Museos.MuID AND AADoTipoAcDocID = 1 " + CStr(sAADoFecha) + &quot AS CantAsist1, (CantAsist1 / CantAct1) AS TotalProm1, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocMuseoID = Museos.MuID AND AcDocTipoAcDoc = 2 " + CStr(sAcDocFecha) + &quot AS CantAct2, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoMuseoID = Museos.MuID AND AADoTipoAcDocID = 2 " + CStr(sAADoFecha) + &quot AS CantAsist2, (CantAsist2 / CantAct2) AS TotalProm2, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocMuseoID = Museos.MuID AND AcDocTipoAcDoc = 3 " + CStr(sAcDocFecha) + &quot AS CantAct3, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoMuseoID = Museos.MuID AND AADoTipoAcDocID = 3 " + CStr(sAADoFecha) + &quot AS CantAsist3, (CantAsist3 / CantAct3) AS TotalProm3, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocMuseoID = Museos.MuID AND AcDocTipoAcDoc = 4 " + CStr(sAcDocFecha) + &quot AS CantAct4, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoMuseoID = Museos.MuID AND AADoTipoAcDocID = 4 " + CStr(sAADoFecha) + &quot AS CantAsist4, (CantAsist4 / CantAct4) AS TotalProm4, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocMuseoID = Museos.MuID AND AcDocTipoAcDoc = 5 " + CStr(sAcDocFecha) + &quot AS CantAct5, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoMuseoID = Museos.MuID AND AADoTipoAcDocID = 5 " + CStr(sAADoFecha) + &quot AS CantAsist5, (CantAsist5 / CantAct5) AS TotalProm5, " & _
"(SELECT COUNT(AcDocID) AS TotalActividad FROM ActividadDocente WHERE AcDocMuseoID = Museos.MuID AND AcDocTipoAcDoc = 6 " + CStr(sAcDocFecha) + &quot AS CantAct6, " & _
"(SELECT SUM(AADoCantBecados + AADoCantArancelados) AS TotalActividad FROM AsistActDoc WHERE AADoMuseoID = Museos.MuID AND AADoTipoAcDocID = 6 " + CStr(sAADoFecha) + &quot AS CantAsist6, (CantAsist6 / CantAct6) AS TotalProm6 " & _
&quot;FROM Museos WHERE Museos.MuID <90 ORDER BY ORD, MUSEOS)&quot;

oRecordset.Open sSql, oEstadisticas, adOpenForwardOnly, adLockPessimistic

Set xlApp = New Excel.Application
xlApp.Visible = False
Set xlWorkBK = xlApp.Workbooks.Add
xlWorkBK.Sheets(1).Name = &quot;Indicador&quot;
xlWorkBK.Worksheets(&quot;Indicador&quot.Activate
'.Cells(numero, letra)

With xlWorkBK.Worksheets(&quot;Indicador&quot
'.Cells(numero, letra)
.Range(&quot;A1:O1&quot.Merge
.Range(&quot;A1:O5&quot.Font.Bold = True
.Range(&quot;A1&quot.WrapText = True
.Range(&quot;A2:O2&quot.Merge
.Range(&quot;A3:A5&quot.Merge
.Range(&quot;D3:O3&quot.Merge
.Range(&quot;B3:C4&quot.Merge
.Range(&quot;B3&quot.WrapText = True
.Range(&quot;D4:E4&quot.Merge
.Range(&quot;F4:G4&quot.Merge
.Range(&quot;H4:I4&quot.Merge
.Range(&quot;J4:K4&quot.Merge
.Range(&quot;L4:M4&quot.Merge
.Range(&quot;N4:O4&quot.Merge
.Range(&quot;A2&quot.Font.Bold = True
.Range(&quot;A1:O5&quot.Borders.LineStyle = xlDouble
.Range(&quot;A6:A17&quot.Borders.LineStyle = xlDouble
.Range(&quot;B6:O17&quot.Borders.LineStyle = xlContinuous
.Range(&quot;A1:O17&quot.VerticalAlignment = xlCenter
.Range(&quot;A1:O5&quot.HorizontalAlignment = xlCenter
.Range(&quot;A6:A17&quot.HorizontalAlignment = xlCenter

.Cells(1, 1) = &quot;Actividades Docentes organizadas por la DGM y los Museos de la Ciudad por tipo de Actividad y Asistentes, según Organismo&quot;
If .Cells(1, 1).Width < Len(&quot;Actividades Docentes organizadas por la DGM y los Museos de la Ciudad por tipo de Actividad y Asistentes, según Organismo&quot Then
If Len(&quot;Actividades Docentes organizadas por la DGM y los Museos de la Ciudad por tipo de Actividad y Asistentes, según Organismo&quot / CLng(.Cells(1, 1).Width) > 1 And Len(&quot;Actividades Docentes organizadas por la DGM y los Museos de la Ciudad por tipo de Actividad y Asistentes, según Organismo&quot / CLng(.Cells(1, 1).Width) < 2 Then
.Cells(1, 1).RowHeight = .Cells(1, 1).Height * 2
ElseIf Len(&quot;Actividades Docentes organizadas por la DGM y los Museos de la Ciudad por tipo de Actividad y Asistentes, según Organismo&quot / CLng(.Cells(1, 1).Width) > 2 And Len(&quot;Actividades Docentes organizadas por la DGM y los Museos de la Ciudad por tipo de Actividad y Asistentes, según Organismo&quot / CLng(.Cells(1, 1).Width) < 3 Then
.Cells(1, 1).RowHeight = .Cells(1, 1).Height * 3
ElseIf Len(&quot;Actividades Docentes organizadas por la DGM y los Museos de la Ciudad por tipo de Actividad y Asistentes, según Organismo&quot / CLng(.Cells(1, 1).Width) > 3 And Len(&quot;Actividades Docentes organizadas por la DGM y los Museos de la Ciudad por tipo de Actividad y Asistentes, según Organismo&quot / CLng(.Cells(1, 1).Width) < 4 Then
.Cells(1, 1).RowHeight = .Cells(1, 1).Height * 4
End If
End If

.Cells(3, 1) = &quot;Organismo&quot;
.Cells(3, 4) = &quot;Tipo de Actividad Docente&quot;
.Cells(3, 2) = &quot;Total de Actividades Docentes&quot;
.Cells(4, 4) = &quot;AcIE&quot;
.Cells(4, 6) = &quot;CAyFD&quot;
.Cells(4, 8) = &quot;Cursos&quot;
.Cells(4, 10) = &quot;Seminarios&quot;
.Cells(4, 12) = &quot;TdeE&quot;
.Cells(4, 14) = &quot;Jornadas&quot;

.Cells(5, 2) = &quot;CantADoc&quot;
.Cells(5, 4) = &quot;CantADoc&quot;
.Cells(5, 6) = &quot;CantADoc&quot;
.Cells(5, 8) = &quot;CantADoc&quot;
.Cells(5, 10) = &quot;CantADoc&quot;
.Cells(5, 12) = &quot;CantADoc&quot;
.Cells(5, 14) = &quot;CantADoc&quot;

.Cells(5, 3) = &quot;PromAs&quot;
.Cells(5, 5) = &quot;PromAs&quot;
.Cells(5, 7) = &quot;PromAs&quot;
.Cells(5, 9) = &quot;PromAs&quot;
.Cells(5, 11) = &quot;PromAs&quot;
.Cells(5, 13) = &quot;PromAs&quot;
.Cells(5, 15) = &quot;PromAs&quot;

.Cells(19, 1) = &quot;AcIE: Actividad con Institución Educativa&quot;
.Cells(19, 7) = &quot;CAyFD: Capacitación y Formación Docente&quot;
.Cells(20, 1) = &quot;TdE: Taller de Enseñanza&quot;
.Cells(21, 1) = &quot;CantADoc: Cantidad de Actividades Docentes&quot;
.Cells(21, 7) = &quot;PromAs: Promedio de Asistentes a las Actividades Docentes&quot;
.Cells(23, 1) = &quot;* Suma el total de los valores de cada Organismo&quot;
.Cells(24, 1) = &quot;Fuente: Dirección General de Museos de la Ciudad de Buenos Aires&quot;

CurrentRow = 6
'Currentcol = oRecordset.Fields.Count

ActiveWindow.DisplayGridlines = False
.Cells(CurrentRow, 1).CopyFromRecordset oRecordset
'CurrentRow = .Range(&quot;C65536&quot.End(xlUp).Row
End With

Dim rwindex As Long, colindex As Long
For rwindex = 6 To 17
For colindex = 2 To 15
With Worksheets(&quot;Indicador&quot.Cells(rwindex, colindex)
If .Value = &quot;&quot; Then .Value = &quot;0&quot;
.NumberFormat = &quot;######0&quot;
End With
Next colindex
Next rwindex


cmdExportar.Enabled = True
cmdImprimir.Enabled = True
cmdSalir.Enabled = True

Me.MousePointer = 0

xlApp.Visible = True
Set xlApp = Nothing
Set xlWorkBK = Nothing
CurrentRow = &quot;&quot;
Currentcol = &quot;&quot;
i = &quot;&quot;

Set oRecordset = Nothing

End Sub