Olá pessoas!
O programa que estou desenvolvendo exporta arquivos para o excel.
Ele exporta os dados normalmente quando eu peço para exportar pela primeira vez, mas quando eu peço para exportar pela segunda vez (sem ter fechado o programa), ele dá o seguinte erro, na linha With ActiveSheet.PageSetup:
Run-time error '91':
Object variable or with block variable not set.
--
Public Function Exporta()
Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim i As Integer
Dim j As Integer
Dim X As Integer
Dim Y As Integer
Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.Workbooks.Add
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = _
"&""Arial,Negrito""&14Relatório de Acompanhamento dos Servidores do Canteiro"
.RightHeader = ""
.LeftFooter = "&D &T"
.CenterFooter = ""
.RightFooter = "&P de &N"
.LeftMargin = Application.InchesToPoints(0.787401575)
.RightMargin = Application.InchesToPoints(0.787401575)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.492125985)
.FooterMargin = Application.InchesToPoints(0.492125985)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.MinPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
Set xlSh = xlApp.Workbooks(1).Worksheets(1)
With xlSh.Cells(1, 1).Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
xlSh.Cells(1, 1).Font.Bold = True
xlSh.Cells(1, 1).Borders.LineStyle = xlContinuous
With xlSh.Cells(1, 1)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With xlSh.Cells(1, 2).Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
xlSh.Cells(1, 2).Font.Bold = True
xlSh.Cells(1, 2).Borders.LineStyle = xlContinuous
With xlSh.Cells(1, 2)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With xlSh.Cells(1, 3).Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
xlSh.Cells(1, 3).Font.Bold = True
xlSh.Cells(1, 3).Borders.LineStyle = xlContinuous
With xlSh.Cells(1, 3)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With xlSh.Cells(1, 4).Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
xlSh.Cells(1, 4).Font.Bold = True
xlSh.Cells(1, 4).Borders.LineStyle = xlContinuous
With xlSh.Cells(1, 4)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With xlSh.Cells(1, 5).Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
xlSh.Cells(1, 5).Font.Bold = True
xlSh.Cells(1, 5).Borders.LineStyle = xlContinuous
With xlSh.Cells(1, 5)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With xlSh.Cells(1, 6).Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
xlSh.Cells(1, 6).Font.Bold = True
xlSh.Cells(1, 6).Borders.LineStyle = xlContinuous
With xlSh.Cells(1, 6)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
For i = 1 To MSFlexGrid1.Rows - 1
For j = 1 To 6 'quantidade de colunas no excel
xlSh.Cells(i + 1, j).Borders(xlDiagonalDown).LineStyle = xlNone
xlSh.Cells(i + 1, j).Borders(xlDiagonalUp).LineStyle = xlNone
With xlSh.Cells(i + 1, j).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSh.Cells(i + 1, j).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSh.Cells(i + 1, j).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSh.Cells(i + 1, j).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
If MSFlexGrid1.TextMatrix(i, 3) = "Servidor desligado ou fora da rede." Then
With xlSh.Cells(i + 1, j).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
xlSh.Cells(1, 1).Value = "Código"
xlSh.Cells(1, 2).Value = "IP"
xlSh.Cells(1, 3).Value = "Nome"
xlSh.Cells(1, 4).Value = "Resposta"
xlSh.Cells(1, 5).Value = "Data"
xlSh.Cells(1, 6).Value = "Horário"
xlSh.Cells(i + 1, 1).Value = MSFlexGrid1.TextMatrix(i, 0)
xlSh.Cells(i + 1, 2).Value = MSFlexGrid1.TextMatrix(i, 1)
xlSh.Cells(i + 1, 3).Value = MSFlexGrid1.TextMatrix(i, 2)
xlSh.Cells(i + 1, 4).Value = MSFlexGrid1.TextMatrix(i, 3)
xlSh.Cells(i + 1, 5).Value = CDate(MSFlexGrid1.TextMatrix(i, 4))
xlSh.Cells(i + 1, 6).Value = MSFlexGrid1.TextMatrix(i, 5)
Next j
Next i
For X = 1 To MSFlexGrid1.Rows - 1
For Y = 1 To 6
xlSh.Cells(X + 1, Y).Borders(xlDiagonalDown).LineStyle = xlNone
xlSh.Cells(X + 1, Y).Borders(xlDiagonalUp).LineStyle = xlNone
With xlSh.Cells(X + 1, Y).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSh.Cells(X + 1, Y).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSh.Cells(X + 1, Y).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSh.Cells(X + 1, Y).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next Y
Next X
xlSh.Columns(1).AutoFit
xlSh.Columns(2).AutoFit
xlSh.Columns(3).AutoFit
xlSh.Columns(4).AutoFit
xlSh.Columns(5).AutoFit
xlSh.Columns(6).AutoFit
'Columns("A:A").EntireColumn.AutoFit
'Columns("B:B").EntireColumn.AutoFit
'Columns("C:C").EntireColumn.AutoFit
'Columns("D:D").EntireColumn.AutoFit
'Columns("E:E").EntireColumn.AutoFit
'Columns("F:F").EntireColumn.AutoFit
'ActiveWindow.WindowState = xlMaximized
Set xlSh = Nothing
Set xlApp = Nothing
End Function
--
Vi algo parecido com meu erro neste tópico, mas não achei a resposta =/
Alguém poderia me ajudar?
Obrigada,
Michelle Pacheco
