Results 1 to 7 of 7

Thread: Loop not working

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    3

    Loop not working

    Hi there,

    I am new here and need assistance with a very simple code that sometimes wont work.

    Scenario:

    At work I cannot run an excel sheet linked to a RDT server to get online stock quotations and the solution is to do that via GOOGLE SHEET where RDT links wont work.

    I have an excel set at my computer and it is getting live data from a server (RDT) and I came up with the code below to convert results to CSV file. That file gets saved in a google drive folder from where my google sheet is connected and getting that data from.

    Code in excel: Working fine until loop is initiated.
    Code:
    Dim flag As Boolean
    
    Sub Button1_Click()
        flag = True
        sbCopyRangeToAnotherSheet
    End Sub
    
    Sub Button2_Click()
        flag = False
    End Sub
    Sub sbCopyRangeToAnotherSheet()
    On Error Resume Next
    If flag = True Then
            Application.DisplayAlerts = False
            Sheets("RDT").Range("b1").Value = DateTime.Now
            Sheets("RDT").Copy
            ActiveWorkbook.SaveAs Filename:="C:\Users\Admin\Google Drive\Opcoes Sheet\RDT.csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
            ActiveWorkbook.Close
            Application.DisplayAlerts = True
            Application.OnTime Now + TimeValue("00:01:00"), "sbCopyRangeToAnotherSheet"
    Else
    End
    End If
    
    End Sub

    CSV is getting saved perfectly, but after some loops (ramdonly) I get an error saying:

    FILE NOT FOUND 'c:\TEMP\VB34C3.TMP" (It changes the TMP file name every time the error occurs)

    Actions already taken:
    - reinstalled office 365
    - deleted temp folder
    - tried adding pause of 30 seconds (allowing the tmp file to be created)
    - tried using kill "c:\temp\*.tmp"

    Dont know what else I can do to solve this.
    Last edited by si_the_geek; Aug 9th, 2020 at 07:32 AM. Reason: added Code tags

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Loop not working

    You've posted this in the VB.NET forum but that's obviously wrong. It appears to be Excel VBA so i have asked the mods to move this thread to the Office Development forum. Please know what language you're using and post in the appropriate forum.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Loop not working

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  4. #4
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,801

    Re: Loop not working

    A few tips:
    1. Give controls and variables meaningful names.
    2. "If boolean = True Then ..." can be shortened to "If boolean Then ..." .
    3. Don't hardcode absolute paths and take a look at this: https://stackoverflow.com/questions/...environ-on-vba.
    4. Don't use "On Error Resume Next" unless you are certain you want to ignore errors.
    5. Don't use the "End" statement. It crashes your program without closing or unloading stuff.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Loop not working

    you try to list all errors to find if any specific ones are causing a problem
    Code:
            if not err.number = 0 then msgbox err.description & vbnewline & "No. " & err.number
    Application.DisplayAlerts = True
            Application.OnTime Now + TimeValue("00:01:00"), "sbCopyRangeToAnotherSheet"
    Else
    
    End If
    you could also record them in the immediate window, but for toubleshooting may be better to see them occur, you could also record the time the error occurs to see if it matches any conditions that may have occurred

    it may be better to set a new workbook to an object variable rather than relying on using the active workbook, copy the sheet to the new workbook, delete any additional existing sheets then save and close the new workbook
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    3

    Re: Loop not working

    Sorry about that. Tks for supporting.

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    3

    Re: Loop not working

    Quote Originally Posted by si_the_geek View Post
    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.
    Tks a lot.

Posting Permissions

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



Click Here to Expand Forum to Full Width