Dim xlObj As Object 'New Excel.Application -- only used with Excel reference Dim wkbOut As Object 'Excel.Workbook Dim wksOut As Object 'Excel.Worksheet Dim rngOut As Object 'Excel.Range Dim sngStart As Single 'forperformance measurement Dim start_time As Long Dim end_time As Long Dim total_time As Long 'output to Excel workbook lblStatus.Caption = "Begin Excel Data Export" Set xlObj = CreateObject("Excel.Application") Set wkbOut = xlObj.Workbooks.Add Set wksOut = wkbOut.Worksheets("Sheet1") 'can skip this step Set rngOut = wksOut.Range("A1") 'by replacing with wkbOut.Worksheets("Sheet1").Range("A1") Me.MousePointer = vbHourglass Me.Enabled = False xlObj.ScreenUpdating = False xlObj.Calculation = -4135 '=xlCalculationManual 'BulkLoad rngOut, sngData Clipboard.clear 'Clear the Clipboard With MSHFlexGrid_crow_fly_loads .Col = 0 .Row = 0 .ColSel = .Cols - 1 .RowSel = .Rows - 1 Clipboard.SetText .Clip End With With xlObj.ActiveWorkbook.ActiveSheet .Range("A1").Select .Range("A1:S1").Interior.Color = RGB(205, 197, 191) .Columns("A:S").NumberFormat = "@" .Paste .Columns("A:S").AutoFit .Range("C2").Select ActiveWindow.FreezePanes = True End With xlObj.Calculation = -4105 '=xlCalculationAutomatic xlObj.ScreenUpdating = True xlObj.Visible = True Set rngOut = Nothing Set wksOut = Nothing Set wkbOut = Nothing Set xlObj = Nothing lblStatus.Caption = "Finished Excel Data Export." Me.MousePointer = vbDefault Me.Enabled = True
Private Sub Command_Click() On Error GoTo eh With CommonDialog1 .CancelError = True .Filter = "Text files (*xls) |*.xls" .ShowSave Open CommonDialog1.FileName For Output As #1 Print #1, xlObj Close #1 End With Exit Sub eh: If Err <> cdlCancel Then MsgBox "Error #" & Err.Number & " - " & Err.Description End If End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE