1

I'm trying to pull some data from a .csv file on a network drive into a worksheet in a macro-enabled workbook on a different network drive using a VBScript, but I'm getting the following error:

"Unknown runtime error."

The goal is to copy all the data from the data worksheet in the data workbook to the test1 worksheet in the spreadsheet workbook. Here's my code:

Set app1 = CreateObject("Excel.Application")
Set wb1 = app1.WorkBooks.Open("Q:\sample_dir\spreadsheet.xlsm")
Set ws1 = app1.ActiveWorkBook.WorkSheets("test1")
Set app2 = CreateObject("Excel.Application")
Set wb2 = app2.WorkBooks.Open("M:\sample_dir\data.csv")
Set ws2 = app2.ActiveWorkBook.WorkSheets("data")

i = 1

Do Until ws2.cells(i,1).value = "" 'Find how many rows there are
    i = i + 1
Loop

For j = 1 to 5000 'Find how many columns there are
    If ws2.cells(1,j).value = "" Then
        Exit For
    End If
Next

For k = 1 to i
    For m = 1 to j
        ws1.cells(k,m).value = ws2.cells(k,m).value
    Next
Next

app1.ActiveWorkBook.Save
wb1.Close
wb2.Close
app1.Quit
app2.Quit
WScript.Quit

I had originally used a For loop where the Do Until exists (and it didn't have the error), but I looked up the looping in VBScript and saw this as an opportunity to clean up and simplify the code.

EDIT: The error says as follows:

excel.vbs(10,1) Microsoft VBScript runtime error: Unknown runtime error

SOLUTION:

I looked into the directory that held my .vbs script and found that a copy of the spreadsheet.xlsm file had been saved there. I deleted that spreadsheet and ran the code again, and it worked just fine (didn't save it to the script directory and it updated the spreadsheet on the network drive).

This issue came back up again and my first solution didn't actually work. I found out the cause was due to my indexing when continuing to debug. In my code, I had used i = j = 1 to initialize the variables for the two Do Until loops, which apparently doesn't work here (curse you, C background!)

Seth
  • 43
  • 1
  • 6

2 Answers2

0

app2 is a reference to the Excel Application object, which does not have a Cells collection. I think you mean to use ws2:

Do Until ws2.cells(i,1).value = "" 'Find how many rows there are
    i = i + 1
Loop

You're making this mistake throughout your code.

Bond
  • 16,071
  • 6
  • 30
  • 53
  • While this does fix some other problems with the code, it doesn't get rid of the runtime error. Thanks! – Seth Jul 22 '15 at 13:46
  • When you step thru your code in the debugger, what line causes the error? Can you update your question with your new code? – Bond Jul 22 '15 at 13:47
  • I just added it. It is on the line that starts the `Do Until` loop. – Seth Jul 22 '15 at 13:51
  • What's in `A1` on the `data` worksheet of your `data.csv` file? – Bond Jul 22 '15 at 13:57
  • The cell contains the string "PRODUCT" – Seth Jul 22 '15 at 14:00
  • You may want to try rebooting and trying your script again. I just ran it myself and it worked fine. – Bond Jul 22 '15 at 14:03
  • Interesting. I rebooted and I'm still getting the error. – Seth Jul 22 '15 at 14:14
  • I found the solution to the problem and have updated the question to explain it. – Seth Jul 22 '15 at 14:20
  • Interesting. Wonder why that would effect your _other_ workbook (`data.csv`) that you were looping thru. I don't get it, but I'm glad you got it going! – Bond Jul 22 '15 at 14:39
0

I believe the problem is going to be that "" is not the same as BLANK in Excel. So you're not running until you hit a blank cell, you're running until you hit a cell which returns "" (which is often used to create a cell with a blank appearance in a formula, ie: if a condition fails, show "").

You'll want to use the IsEmpty condition, like so: Excel: How to check if a cell is empty with VBA?

Community
  • 1
  • 1
Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46