Results 1 to 5 of 5

Thread: Error Handling

  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Error Handling

    I have had quite a lot of help building my database from this forum, Thank You in advance for all your help. I have been having Sinndho help me get to where i can open an excel file, create sheets, and write data to the file. I should have stated my problem entirely in the beginning so i wouldn't have to backtrack to get to where i want to be. Here goes:
    1. I need to look to see if an excel file exists and if it doesn't create it and save the name as "Recordset.[PartNumber]".xls then then create a new sheet from the template "sheet1" and name if from the Recordset.[FO#].
    2. If the file exists open it to the predetermined sheet called from Recordset.[FO#].
    3. If the predetermined file doesn't exist but the sheet doesn't exist then create a new one from the template "sheet1" and name if from the Recordset.[FO#].

    The last item is what i have working now, Here is the code for that:
    Code:
    Private Sub Command57_Click()
    '
        Dim xlhwnd As Long
        Dim xlapp As Excel.Application
        Set xlapp = New Excel.Application
    '
        With xlapp
            xlhwnd = .hWnd
            .Visible = False
        
    '
            .Workbooks.Open "U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx"
    '
    ' Copy sheet1 and rename with selected FO number.
        Sheets("Sheet1").Select
        Sheets("Sheet1").Copy After:=Sheets(1)
        Sheets("Sheet1 (2)").Select
        Sheets("Sheet1 (2)").Name = Recordset.[FO#]
    '
    ' End of process.
    '
    ' Save the file.
    '
            .ActiveWorkbook.Save
        
    ' Then close it.
    '
            .Workbooks.Close
            .Quit
            EndTask xlhwnd, 0, 1
        End With
     
        '
        Set xlapp = Nothing
    '
    'Reopen the Excel workbook for further editing.
    '
        ShellExec "U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx"
        
    End Sub
    So i'm looking at three scenarios. File and sheet exists just open for viewing, File does not exist. File exists but sheet doesn't. I have been browsing the web to find a solution to this and i have found lot of ways to achieve this but all the posts are from old posts and I don't want to learn an outdated way to handle this. Since i have never done anything like this at all i don't want to learn bad habits.

    Thank you in advance for all you help. If i can get this it will complete this upgrade for this database.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Does file exist?
    Code:
    If Len(Dir("FullPathToFileName")) > 0 Then    ' --> File exists.
    2. Does WorkSheet exist in the open WorkBook? (new lines are in red)
    Code:
        Dim xlhwnd As Long
        Dim xlapp As Excel.Application
        Dim xlSheet As Excel.Worksheet
        Dim SheetExists As Boolean
        
        ' Create a new instance of Excel.
        '
        Set xlapp = New Excel.Application
        
        ' Then use it.
        '
        With xlapp
        
            xlhwnd = .hWnd
        
        ' Useful when debugging.
        '
            .Visible = True
        
        ' Open the Excel file you want to work with.
        '
            .Workbooks.Open "C:\Documents and Settings\Sinndho\My documents\Excel\Billing_Metadata_20100405.xls"
            For Each xlSheet In .ActiveWorkbook.Sheets
                If xlSheet.Name = "Sheet1" Then
                    SheetExists = True    ' --> Sheet "Sheet1" exists.
                    Exit For              ' No need to keep looping through the sheets.
                End If
            Next xlSheet
            If SheetExists = True Then
                
                ' Do whatever you have to do when the sheet exists.
                '
            Else
                 
                ' Do whatever you have to do when the sheet does not exist.
                '
           End If
    Not sure to correctly understand #3.
    Have a nice day!

  3. #3
    Join Date
    May 2009
    Posts
    104
    Its just poor grammar, what i meant was #3 If the predetermined file does exist but the sheet does not exist then copy template "sheet1" and name if from the Recordset.[FO#]. I think i understand the second suggestion. I see the code
    Code:
    If Len(Dir("FullPathToFileName")) > 0 Then    ' --> File exists.
    but what is the syntax for creating a new file and saving it under the Recordset.[PartNumber] file name. Essentially what i think will have to happen is when i doesn't find a file with the predetermined file name it will open the template file, then save a copy as Recordset.[PartNumber], and the close and quit. Then the rest of the code can run to reopen the newly created file and create a new sheet when i needs it. It will only have to create a new file once for each [PartNumber]. Lets say the template file is Insp_Sheet_Template.xlsx.
    Last edited by dbshaft; 08-24-11 at 18:41.

  4. #4
    Join Date
    May 2009
    Posts
    104

    Not Recognizing sheet already existing

    The following code works up to the point of looking to see if the file exists, if the file doesn't exist it creates it, but when i it looks to see if the sheet exists is tries to copy and rename the sheet even if the sheet already exists.
    Code:
    Private Sub Command57_Click()
    '
    '    On Error GoTo EndOfProcess
        '
        Dim xlhwnd As Long
        Dim xlApp As Excel.Application
        Dim xlSheet As Excel.Worksheet
        Dim SheetExists As Boolean
        
        ' Create a new instance of Excel.
        '
        Set xlApp = New Excel.Application
        With xlApp
        
            xlhwnd = .hWnd
        '
        'Check to see if .xlsx file exists.
    If Len(Dir("U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx")) > 0 Then GoTo FileExists   ' --> File exists.
        ChDir "U:\QC\FinalInspection\InspectionSheets\"
        Workbooks.Open FileName:= _
            "U:\QC\FinalInspection\InspectionSheets\Template.xlsx"
        ActiveWorkbook.SaveAs FileName:= _
            "U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
        .Range("C2").Select
        .ActiveCell.FormulaR1C1 = Recordset.[Part Number]
        .Range("E2:H2").Select
        .ActiveCell.FormulaR1C1 = Recordset.[Part Description]
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
        .Quit
    '
    FileExists:
    '
        ' Open the Excel file you want to work with.
        '
          .Workbooks.Open "U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx"
            For Each xlSheet In .ActiveWorkbook.Sheets
                If xlSheet.Name = "Recordset.[FO#]" Then
                    SheetExists = True    ' --> Sheet "Sheet1" exists.
                    Exit For              ' No need to keep looping through the sheets.
                End If
            Next xlSheet
            If SheetExists = True Then
            
                ' Do whatever you have to do when the sheet exists.
        .Sheets("Recordset.[FO#]").Select
                '
            Else
                 
                ' Do whatever you have to do when the sheet does not exist.
                            ' Copy sheet1 and rename with selected FO number.
        .Sheets("Sheet1").Select
        .Sheets("Sheet1").Copy After:=Sheets(1)
        .Sheets("Sheet1 (2)").Select
        .Sheets("Sheet1 (2)").Name = Recordset.[FO#]
        '
        End If
    EndOfProcess:
    
            .ActiveWorkbook.Save
            .Workbooks.Close
            .Quit
            EndTask xlhwnd, 0, 1
            
        End With
     
        '
        Set xlApp = Nothing
    ReOpenWorkBook:            '
        ShellExec "U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx"
    '
    End Sub
    It's very possible i have the code so messed up it will never work. The first part of the code works fine, if the file doesn't exists it creates it and names if correctly but if the file does exist it skips to searching to see if the sheet exists, but when the sheet already exists it tries to create a sheet even though one with the same name already exists and errors out. Any help will be greatly appreciated. If you have an pointers for trouble shooting these types of problems i always appreciate those as well.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you write:
    Code:
    If xlSheet.Name = "Recordset.[FO#]" Then
    It means that you look for a sheet that is named "Recordset.[FO#]", literally and the same is true for:
    Code:
    .Sheets("Recordset.[FO#]").Select
    Such a sheet cannot exist ([ and ] characters are not allowed in a sheet name).

    If you're looking for a sheet the name of which is similar to the contents of the field named FO# of RecordSet (the recordset of the form from where the procedure is run, I suppose) you must remove the quotes:
    Code:
    If xlSheet.Name = Me.Recordset.[FO#].Value Then
    Remember that everything between quotes (simple or double) is not interpreted, it is treated literally.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •