I wrote a bunch of VB/VBA and it seems to be running a little slow. The following process takes 20ish seconds to complete and I was wondering if anyone see's any areas that could be improved for better performance.

vb Code:
  1. Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
  2.         Dim datenow As String = DateTime.Now.ToString("MMM dd")
  3.         xlApp = New Excel.Application
  4.         xlbook = xlApp.Workbooks.Open(xlspath)
  5.         xlsheet1 = xlbook.Worksheets("Sheet1")
  6.         xlbook.Sheets("Sheet1").Name = "Summary"
  7.         xlbook.Sheets("Sheet2").delete()
  8.         xlbook.Sheets("Sheet3").delete()
  9.         With xlsheet1
  10.              .Columns("K:J").delete()
  11.              .Columns("A:A").delete()
  12.              .Columns("B:B").insert()
  13.              .Cells(1, 1).Copy()
  14.              .Cells(1, 2).PasteSpecial(Paste:=Excel.Constants.xlFormats, Operation:=Excel.Constants.xlNone, SkipBlanks:=False, Transpose:=False)
  15.              .Cells(1, 2) = "Pic"
  16.              .Cells(1, 8) = "APP"
  17.              .Cells(1, 9) = "APP Version"
  18.         End With
  19.  
  20.         Dim lastsheet = "Summary"
  21.         For x = 2 To xlsheet1.Rows.Count
  22.             If xlsheet1.Cells(x, 1).value = Nothing Then
  23.                 Exit For
  24.             Else
  25.                 xlbook.Worksheets.Add(After:=xlbook.Worksheets(lastsheet))
  26.                 lastsheet = CStr(xlsheet1.Cells(x, 1).value)
  27.                 xlbook.Sheets(CStr("Sheet" & CStr(x - 1))).Name = lastsheet
  28.                 For y = 1 To xlsheet1.Columns.Count
  29.                     If xlsheet1.Cells(1, y).value = Nothing Then
  30.                         Exit For
  31.                     Else
  32.                         xlsheet1.Cells(1, y).Copy()
  33.                         With xlbook.Sheets(lastsheet)
  34.                             .cells(1, y).PasteSpecial(Paste:=Excel.Constants.xlFormats, Operation:=Excel.Constants.xlNone, SkipBlanks:=False, Transpose:=False)
  35.                             .cells(1, y) = xlsheet1.Cells(1, y).value
  36.                             .cells(2, y) = xlsheet1.Cells(x, y).value
  37.                         End With
  38.                     End If
  39.                 Next
  40.                 With xlbook.Sheets(lastsheet)
  41.                     .columns("B:B").delete()
  42.                     .columns("A:k").HorizontalAlignment = Excel.Constants.xlLeft
  43.                     .columns("A:k").VerticalAlignment = Excel.Constants.xlTop
  44.                     .columns("A:k").wraptext = True
  45.                     .columns("A:k").autofit()
  46.                     .columns("i:i").columnwidth = 20
  47.                     .columns("j:j").columnwidth = 60
  48.                     .columns("K:K").columnwidth = 60
  49.                     .rows("1:100").autofit()
  50.                 End With
  51.                 xlsheet1.Hyperlinks.Add(Anchor:=xlsheet1.Cells(x, 1), Address:="", SubAddress:="'" & lastsheet & "'!A1", TextToDisplay:=(lastsheet))
  52.                 Dim pix = 100
  53.                 Dim txt = 215
  54.                 For Each foundFile As String In My.Computer.FileSystem.GetFiles(ComboBox1.SelectedItem, FileIO.SearchOption.SearchTopLevelOnly, "*" & lastsheet & "*")
  55.                     Dim extension = Path.GetExtension(foundFile).ToLower.ToString
  56.                     If extension = ".log" Or extension = ".txt" Then
  57.                         xlbook.Sheets(lastsheet).OLEObjects.Add(FileName:=foundFile, Link:=False, DisplayAsIcon:=True, IconFileName:=Environment.GetFolderPath(Environment.SpecialFolder.Windows) & "\Notepad.exe", IconIndex:=0, IconLabel:=Path.GetFileName(foundFile), Left:=265, Top:=txt, Width:=75, Height:=75)
  58.                         txt += 80
  59.                         xlsheet1.Cells(x, 2) = xlsheet1.Cells(x, 2).value + "X"
  60.                     ElseIf extension = ".png" Or extension = ".jpg" Or extension = ".jpeg" Or extension = ".gif" Then
  61.                         xlbook.Sheets(lastsheet).Shapes.AddPicture(foundFile, Microsoft.Office.Core.MsoTriState.msoCTrue, Microsoft.Office.Core.MsoTriState.msoCTrue, 25, pix, 215, 350)
  62.                         pix += 350
  63.                         xlsheet1.Cells(x, 2) = xlsheet1.Cells(x, 2).value + "X"
  64.                     End If
  65.                 Next
  66.             End If
  67.         Next
  68.         With xlsheet1
  69.             .Columns("D:D").delete()
  70.             .Columns("J:L").delete()
  71.             .Columns("C:C").delete()
  72.             .Columns("A:K").autofit()
  73.             .Columns("A:G").HorizontalAlignment = Excel.Constants.xlCenter
  74.             .Cells(1, 8).HorizontalAlignment = Excel.Constants.xlCenter
  75.             .Columns("A:H").VerticalAlignment = Excel.Constants.xlTop
  76.         End With
  77.         Dim sheet As Excel.Worksheet
  78.         Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls"
  79.         Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value
  80.         For Each sheet In xlbook.Worksheets
  81.             sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
  82.             sheet.PageSetup.LeftHeader = "&D &T"
  83.             sheet.PageSetup.CenterHeader = header.ToString
  84.             sheet.PageSetup.LeftFooter = title.ToString
  85.             sheet.PageSetup.RightFooter = "&P/&N"
  86.             sheet.PageSetup.Zoom = False
  87.             sheet.PageSetup.FitToPagesTall = 2
  88.             sheet.PageSetup.FitToPagesWide = 1
  89.         Next
  90.         xlsheet1.Activate()
  91.         xlbook.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) & "\" & title.ToString)
  92. CloseExcel()
  93. End Sub

What the program does is load an exported excel report from another program and reconfigures it. First thing it does is it makes a new tab for each row, then it looks through a file for pictures or text documents containing the same name and attaches them. Finally, it changes the print settings and saves it to the desktop. I seem to have hit a small wall, and other then some syntax here and there Im not seeing much else.

Any and all suggestions are appreciated.

Zach

*edit - If anyone would like to see a sample unedited report or the finished product, let me know.