Macros run time problem with workbook closing itself.

  • Hello everyone,

    I would be grateful for any help with workbook closing itself in the middle of macros run-time. The macros downloads list of stocks historical price data and inserts them in the corresponding to each stock CSV file in the folder C:\VBA\. Names of CSV files are equal to their corresponding stock symbols. If stock symbol is XYZ then its file will be XYZ.csv. Whenever corresponding to stock symbol CSV file can’t be found in the folder with CSV files, workbook closes itself in the middle of run-time. I can't figure out how to solve this problem. Thanks in advance for any help.


  • Which workbook are you trying to close? You are opening myfile , inserting a at B2 of the csv then closing the csv by the looks of the code.


    I can't see why it would close Excel

  • I'm not trying to close workbook. It closes by itself if it can't find CSV file corresponding to stock symbol. For example, if you place stock symbol in the cell "S5", like XYZ. If the file XYZ.csv is available in the folder C:\VBA\, then macros works without problems, but if it is not there in the folder, instead of giving error message workbook closes by itself, i.e. macros closes workbook in which it is located. I hope someone will be able to figure out how to sort out this problem. Thank you.

  • Possibly...

    The On Error Resume Next line may be hiding a problem in the called macro...?

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Thank you so much! It is working now without any problems. I was wandering, is there any way to record stock symbols (lets say somewhere in the column M) that cause errors because their corresponding files do not exist in the folder? When it comes to hundreds of stock symbols sometimes it is difficult manually to figure out new stock, file for which is not available. Thank you in advance and highly appreciate your help.


    Possibly...

    Code
        On Error Resume Next                          'MIGHT BE HIDING AN ERROR CAUSING YOUR PROBLEM
  • You can test if a file exists by using this Function. Place the Function in Standard Module, then adapt the demo to us in your code


    Code
    Public Function FileExists(fil As String)
    FileExists = CreateObject("Scripting.FileSystemObject").FileExists(fil)
    End Function
    
    
    Sub test()
    MsgBox FileExists("full path & file name here")
    End Sub



    Your code


  • Quote

    ...is there any way to record stock symbols (lets say somewhere in the column M) that cause errors because their corresponding files do not exist in the folder? ...


    Try changing the if statement to...

    Code
                    If Not wb Is Nothing Then         'IF FILE EXISTS THEN
                        wb.ActiveSheet.Range("B2").Insert Shift:=xlShiftDown 
                        wb.Close True
                    Else
                        cell.Offset(, 2).Value = "File not found for " & cell.Value
                    End If

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Thanks for suggestion Roy. I heard about FileExists function, but I have no experience of using it whatsoever. Apart from that the sub I'm trying to create will be updating thousands of files, with possibly dozens of them missing, so I'm trying to automate as many functions as it is possible. Interruption for message box will delay everything. Is it possible with this function not only to confirm file existence (or absence) but to create CSV file if it does not exist? Thanks in advance.


    Dilshod


    Dilshod

  • Try changing the if statement to...

    Code
                    If Not wb Is Nothing Then         'IF FILE EXISTS THEN
                        wb.ActiveSheet.Range("B2").Insert Shift:=xlShiftDown 
                        wb.Close True
                    Else
                        cell.Offset(, 2).Value = "File not found for " & cell.Value
                    End If

    Thanks a lot, dangelor! It works. It was so simple, but it would take me ages to figure out how to do that. Appreciate it.

  • Possibly...

    The On Error Resume Next line may be hiding a problem in the called macro...?

    Hi dangelor! I was so happy that there was no problem with workbook self closing, that I have not noticed that the new code stopped updating historical price data in CSV files. Instead of pasting (or inserting) historical price data it just makes Cell B2 blank. Any chance to find out where is the error in the code? Thanks again.

  • I would need to see the code that downloads the data

    The problem is solved. Modification of the code deleted copied data in the RAM, that is why it was pasting nothing. I just moved line with data copy closer to the block you modified. Initially lt was incorporated within GetYahooDataFromJSON function.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!