1

I'm trying to use the following code to copy some values from an opened csv file to the sheet I'm working on. It works sometimes but will occasionally go through awb.Sheets("Target").Range("A2:C" & lrow).Value = wb.Sheets("csvSheetName").Range("A2:C" & lrow).Value without actually copying any values.

I've stepped through the code and there doesn't see to be any problem. I've tried debugging it: awb.Sheets("Target").Range("A2:C" & lrow).Select and wb.Sheets("csvSheetName").Range("A2:C" & lrow).Select both grab the correct ranges, debug.print wb.Sheets("csvSheetName").Range("A1").Valueoutputs what it should. lrow has value.

On Error Resume Next
Sheets("Target").ShowAllData
On Error GoTo -1
Sheet5.Range("A2:A7000").Cells.ClearContents
Sheets("Target").Range("A2:C7000").Cells.ClearContents

    Dim wb As Workbook
    Dim awb As Workbook
    Dim myfilename As String


    Set awb = Workbooks("TargetBook")
    myfilename = "www.csvwebsite.com/download.do?query=string"
    Set wb = Workbooks.Open(myfilename)

`Application.Wait (Now + TimeValue("0:00:02"))
 Application.Wait (Now + TimeValue("0:00:10"))
    lrow = wb.Sheets("csvSheetName").Cells(Sheets("csvSheetName").Rows.Count, "A").End(xlUp).Row


    awb.Sheets("Target").Range("A2:C" & lrow).Value = wb.Sheets("csvSheetName").Range("A2:C" & lrow).Value
Application.Wait (Now + TimeValue("0:00:02"))
    wb.Close

The destination range is still blank after that line passes with no error. Usually I can rerun the sub and it will work but this is for a project that's effectively a server backend and is running 24/7. When I tried to debug it yesterday, I wasn't able to get it to work at all. The whole range is usually about 3000 cells total so I don't think it's overloading the memory.

I'm open for other ways to do this but it can't copy-paste as the computer is usually locked when doing this, I found that using the clipboard is finicky in that case. I'm also curious as to why this would only work sometimes... I hate it when something works some of the time.

EDIT: Set awb = ActiveWorkbook to Set awb = Workbooks("TargetBook") I think that was the reason it didn't work yesterday when I manually stepped through it. The issue was still present after this change, but when I see it, stepping through fixes it.

EDIT2: Increased waiting time after opening workbook to 10 seconds for now.

EDIT3: No issues with either the increased wait time or the change in the accepted answer. Likely something to do with VBA skipping over the object if it wasn't completely loaded.

Sigil
  • 25
  • 5
  • What value does `lrow` return when you `Debug.Print`? – Dean Aug 10 '19 at 12:19
  • `lrow` returns 1179 – Sigil Aug 10 '19 at 12:21
  • It was originally `set awb = ActiveWorkbook`, I change it to define it specifically and it did the same thing. Tried again without changing it and it's running... I've seen this run for a week straight with no problem, then I'll come in over the week end and it's stuck on this. – Sigil Aug 10 '19 at 12:27
  • It should be related to the Time taken while opening the Workbook. Increase the Waiting time to 10 secs and run it as a Test. Though that should not be the case as assigning it to a Excel object, as VBA would only go on next step when the workbook has been assigned. Could it be that link sometimes work, and sometimes don't ? or Sheet Name Changes ? – Mikku Aug 10 '19 at 12:31
  • The sheetname does not change. It's possible that the link doesn't work, but there's error handling to run it again for that reason, I noticed it'll fail to resolve the link once in a while and works the next time. I added a longer wait time... might take a while to see the problem again. While it _shouldn't_ move on until it's done loading the csv, I've seen that issue before which is why it has the waits. I'll post back in a week or so if it doesn't bug out. Added an edit. – Sigil Aug 10 '19 at 12:40
  • https://stackoverflow.com/a/14159999/11736976 `GoTo -1` clears active error handling while `GoTo 0` disables error handling... in retrospect I'm not sure which is better here - I've needed to use -1 quite often while inside of loops. – Sigil Aug 10 '19 at 18:11
  • Well `On Error Resume Next` doesn't really handle the error, so `On Error GoTo -1` doesn't make sense I think. You're potentially hiding an error somewhere further along. What happens if you change to `0` instead of `-1`? – BigBen Aug 10 '19 at 19:09

1 Answers1

1

Try opening the second workbook first, and then setting the wb variable to that workbook name.

So this tidbit here

Set awb = Workbooks("TargetBook")
myfilename = "www.csvwebsite.com/download.do?query=string"
Set wb = Workbooks.Open(myfilename)

Try changing it to 

Set awb = Workbooks("TargetBook")
myfilename = "www.csvwebsite.com/download.do?query=string"
Workbooks.Open(myfilename)
Set wb = Workbooks(myfilename)
abbsichel
  • 156
  • 1
  • 11
  • This makes sense, could be why the wait was required. I haven't had any issue since increasing the wait time to 10 seconds. Still monitoring it. A few more days of running it I'll add this and bring the wait time down. – Sigil Aug 13 '19 at 15:11