VBA Debuging and Release different When Excel Open new Files-CodePudding

When I open this excel file in VBA code,

it is working well in debug mode. ( When I put debug point to "Set tmpWB = Workbooks.Open(filepath & fileName)")

But It is not working when I execute the vba code on Excel like release mode.

File is opened. But that's all. There is no data "iiii, jjjj, ooooo".

But I already told you, The code is working well when I attached debug point at "Set tmpWB = Workbooks...."

   Set tmpWB = Workbooks.Open(filePath & fileName)
   
   wk.Sheets("sheet1").Cells(1, 1).Value = "iiii"
   
        ActiveWorkbook.SaveAs fileName:=filePath & "temp.xls", FileFormat:=xlExcel8, CreateBackup:=False
        
   wk.Sheets("sheet1").Cells(2, 1).Value = "jjjj"
   
   tmpWB.Close
   
   wk.Sheets("sheet1").Cells(3, 1).Value = "ooooo"

Why does it work when debug mode, and not release mode? How can I debug this?

CodePudding user response：

To create a new workbook file

Private Function NewFileTOZerom()
    On Error GoTo Err_Control
        Dim FilePath As String
        Dim Filename As String
        Dim wb As Workbook
    
    Set wb = Workbooks.Add
        FilePath = "C:\YOUR PATH\" 'remember to use \ at the end
        Filename = "temp.xls"
            wb.Sheets(1).Cells(1, 1).Value = "1"
            wb.Sheets(1).Cells(2, 1).Value = "22"
            wb.Sheets(1).Cells(3, 1).Value = "333"
            wb.Sheets(1).Range("A4").Value = "4444"
        Application.DisplayAlerts = False
            wb.SaveAs Filename:=FilePath & Filename, FileFormat:=xlOpenXMLWorkbook
            wb.Close
        Application.DisplayAlerts = True
    
    Err_Control:
    If Err.Number <> 0 Then
        MsgBox "Err.Description: " & Err.Description & Chr(13) & _
        "Err.Number: " & Err.Number
    End If
    End Function

CodePudding user response：

Option Explicit
Private Function WriteTOZerom()
On Error GoTo Err_Control
    Dim tmpWB As Workbook
    Dim FilePath As String
    Dim Filename As String
    
    FilePath = "C:\" 'remember to use \ at the end
    Filename = "YourFile.xlsm"
    Set tmpWB = Workbooks.Open(FilePath & Filename)
       
       tmpWB.Sheets(1).Cells(1, 1).Value = "iiii"
       tmpWB.Sheets(1).Cells(2, 1).Value = "jjjj"
       tmpWB.Sheets(1).Cells(3, 1).Value = "ooooo"
       tmpWB.Sheets(1).Range("A4").Value = "uuuuuu"
       '1(one) is the first sheet, if want use the name like you do, is work too.
        tmpWB.SaveAs Filename:=FilePath & "temp.xls", FileFormat:=xlOpenXMLWorkbook
        'tmpWB.Close SaveChanges:=True ' to save in same file this edition, or
        'tmpWB.Close SaveChanges:=false 'to close and not save in this same file edited.

Err_Control:
    If Err.Number <> 0 Then
        MsgBox "Err.Description: " & Err.Description & Chr(13) & _
        "Err.Number: " & Err.Number & Chr(13) & Chr(13) & _
        "if you want to make a paragraph: use chr(13) in between '&' "
    End If
End Function