Personal Workbook Macro Not Operating on Current Worksheet

[johnmeyer]

I have a CSV to Quicken QIF macro that works fine when attached to a workbook, but I want to store it in my personal workbook and then be able to simply open a CSV file by double-clicking on it and then run the macro to create the QIF file.

I am using Excel 2002.

I am pretty sure that my problem has to do with how Excel operates when the macro is not attached to the active workbook.

The highlighted code is my problem. When Excel opens the CSV file, the worksheet is named with the name of that file, and that worksheet is selected. If I step through this macro it fails on the bold line with "Run time error 438."

I have tried dozens of different ways to specify the current workbook (i.e., the CSV file), but always end up failing on this line.

This has got to be stupidly simple, but I've now spent over two hours trying dozens of things, and can't figure it out.

Thanks!!

Sub CSVtoQIF()
    Dim iFile As Integer
    Dim LastRow As Long
    Dim rng As Range, cell As Range
  [FONT=arial black][B]  Set rng = ActiveWorkbook.Range("All_Cells")[/B][/FONT]
    iFile = FreeFile
    
    Open "e:\temp Quicken BofA.qif" For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL]  'Change path to suit
    Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] , "!Type:Cash ";
    Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] , vbCrLf;
    
    For i = 1 To rng.Rows.Count
      For j = 1 To rng.Columns.Count
        cellValue = rng.Cells(i, j).Value
        If cellValue = "##EOF##" Then
           Exit For
        End If
        Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] , cellValue;
        
        If j = rng.Columns.Count Then
            Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] , vbCrLf;
            Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] , "^";
            Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] , vbCrLf;
        Else: Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] , vbCrLf;
        End If
      Next j
      If cellValue = "##EOF##" Then
         Exit For
      End If
    Next i
    Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] , vbCrLf;
    
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] 
    MsgBox "Finished"
End Sub

 

[DanteAmor]

Try this:

Set Rng = ActiveWorkbook.Sheets(1).UsedRange

 

[johnmeyer]

Try this:

Set Rng = ActiveWorkbook.Sheets(1).UsedRange

That gets me over the hump. Thanks!!!

I think my main problem is that I thought "All_Cells" was a VBA system variable when in fact it was a range name created by some action I took when developing the original macro. That was my brain freeze. By changing to "UsedRange" the macro no longer stops at that point. Now all I need to do is figure out how to get the macro to select all cells in the worksheet, something I'm sure I'll be able to do.

 

[DanteAmor]

You're welcome