I have the below code (borrowed) that reformats a query that was exported to excel. I need to define a range that would be all the data on the spreadsheet. Each output of this query changes so it needs to find the range. Can anyone help me adjust my Set of "myrange1" since all the definitions I have tried is not working?
Code:
Public Sub ModifyExportedExcelFileFormats(sFile As String)
Dim xlApp As Object
Dim xlSheet As Object
Dim myrange1 As Object
Set xlApp = GetObject(, "Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
Set myrange1 = xlSheet.Range("A1", xlSheet.Range("A" & Rows.Count).End(xlUp))
With xlApp
.Application.Sheets("C500 Provider Directory Query").Activate
.Columns("A:J").Select
.Selection.EntireColumn.AutoFit
.Columns("A:J").Interior.ColorIndex = xlNone
.Columns("A:J").Borders.LineStyle = xlLineStyleNone
.Range("A1:J1").Interior.Color = 12611584
.Range("A1:J1").Font.Color = vbWhite
myrange1.Borders(xlDiagonalDown).LineStyle = xlNone
myrange1.Borders(xlDiagonalUp).LineStyle = xlNone
With myrange1.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With myrange1.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With myrange1.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With myrange1.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With myrange1.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With myrange1.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
.Call AlternatRowColors
.Sheets("C500 Provider Directory Query").Name = "500 Series Provider Network"
.ActiveWorkbook.Save
End With
End Sub
Last edited: