fileToOpen = lblDateRange.Text
fileToOpen = fileToOpen.Replace(" ", "").Replace("-", "") & ".xls"
'for now the file path is C:\Documents and Settings\William\My Documents\
Dim filePath As String = folderToCreate
Dim fullFilePath As String = filePath & fileToOpen
' check to see if the file exists, if not create a new file
If Not File.Exists(fullFilePath) Then
' declared globally
'Dim xlApp As excel.Application
'Dim xlWorkBook As excel.Workbook
'Dim xlWorkSheet As excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = New excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)
xlApp.Visible = True
' Whole bunch of formatting goes here then
' name for the worksheet
wsName = lblAmPmPosition.Text
wsName = wsName.Replace(" ", "").Replace("'", "")
' name for the workbook
wbName = lblDateRange.Text
wbName = wbName.Replace(" ", "").Replace("-", "") & ".xls"
xlWorkSheet.Name = wsName
xlWorkBook.SaveAs(folderToCreate & wbName)
' if the file exists open the file and add the rest of the worksheets
' by exporting them to the existing workbook
ElseIf File.Exists(fullFilePath) Then
'MessageBox.Show("it exists " & fullFilePath)
' this line is wrong becasue it opens a new instance of excel witha new workbook.
' have to get the correct code to fix it
Dim xlApp As excel.Application = DirectCast(CreateObject("Excel.Application"), excel.Application)
Dim xlWorkBook As excel.Workbook = xlApp.Workbooks.Open(fullFilePath)
Dim xlWorkSheet2 As excel.Worksheet
xlWorkSheet2 = CType(xlWorkBook.Worksheets.Add(), excel.Worksheet)
xlWorkSheet2.Name = "test"
Dim misValue As Object = System.Reflection.Missing.Value
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlApp.Visible = True
' more formatting
' if the worksheet doesn't exist create it
wsName = lblAmPmPosition.Text
wsName = wsName.Replace("/", "")
wbName = fileToOpen
wbName = wbName.Replace(" ", "").Replace("-", "")
' check to see if worksheet already exists
' code provided by koolsid from forum post
' http://www.vbforums.com/showthread.php?t=509588&highlight=excel+worksheet+exists
Dim existingWS As String, Found As Boolean, i As Integer
Found = False
'Replace Sheet3 with the name of the relevant sheet
existingWS = wsName
'loop thru the names of the sheet
For i = 1 To xlApp.ActiveWorkbook.Sheets.Count
If existingWS = xlApp.ActiveWorkbook.Sheets(i).ToString Then
Found = True
Exit For
Else
Found = False
End If
Next i
'Display Status
If Found = False Then
MsgBox("Sheet Doesn't exist")
xlWorkSheet2.Name = wsName
Else
'MsgBox("Sheet exists")
End If
'xlWorkSheet2.Name = wsName
xlWorkBook.Save()
end if
'releasing object references
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
releaseObject(xlWorkSheet2)
xlWorkBook = Nothing
xlWorkBook = Nothing
xlApp.Quit()
xlApp = Nothing