Retrieve Named Range Created By Import External Data

[johnmeyer]

Here's the question: is there a way to retrieve the range name created by the Import External Data feature?

Now, here's a little background:

I am creating a spreadsheet that will let me retrieve my bank credit card transactions in CSV format and then output a QIF file that I can import into Quicken (they eliminated this feature last month and want me to pay money each month to keep using it).

I am using the "Import External Data" feature. When I open the workbook, it asks to refresh the data, and I then navigate to this month's CSV download, open it, and Excel puts the data in a worksheet tab all by itself.

CSV_Download[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Posted Date[/TD]
[TD]Reference Number[/TD]
[TD]Payee[/TD]
[TD]Address[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]5/9/2019[/TD]
[TD]24427339128710032062810[/TD]
[TD]LUCKY #703 CARMEL CA[/TD]
[TD]CARMEL CA[/TD]
[TD="align: right"]-15.81[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]5/8/2019[/TD]
[TD]24431069128898000148724[/TD]
[TD]COSTCO WHSE #0131 SEASIDE CA[/TD]
[TD]SEASIDE CA[/TD]
[TD="align: right"]-160.91[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]5/8/2019[/TD]
[TD]24316059127548764002359[/TD]
[TD]SHELL OIL 57444694509 CARMEL CA[/TD]
[TD]CARMEL CA[/TD]
[TD="align: right"]-50.95[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]5/8/2019[/TD]
[TD]24431069127975019550404[/TD]
[TD]SAFEWAY #2669 CARMEL CA[/TD]
[TD]CARMEL CA[/TD]
[TD="align: right"]-4.85[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]5/7/2019[/TD]
[TD]24906419126072653887328[/TD]
[TD]Netflix.com netflix.com CA[/TD]
[TD]netflix.com CA[/TD]
[TD="align: right"]-21.6[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]5/7/2019[/TD]
[TD]24427339126710011809472[/TD]
[TD]LUCKY #703 CARMEL CA[/TD]
[TD]CARMEL CA[/TD]
[TD="align: right"]-34.15[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]5/6/2019[/TD]
[TD]24257959125030010155608[/TD]
[TD]Griggs Nursery Carmel CA[/TD]
[TD]Carmel CA[/TD]
[TD="align: right"]-12[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2003

Here is the question: Excel automatically assigns a range name to this imported data. The range name is simply the name of the CSV file. While I am using macros to create the QIF file, at this point I'd prefer to find a way to use this range name in formulas without having to enter it.

 

[johnmeyer]

Since it appears I can't edit or delete my post after just a few minutes, let me say that I have found out that the name created by the first query actually does NOT change and it DOES automatically resize the number of rows.

So, in the spirit of Emily Litella, "never mind."

 

[marteb]

Would you be willing to share your code to create qif files? My bank recently removed that format from their download options.

Thanks,

 

[johnmeyer]

Would you be willing to share your code to create qif files? My bank recently removed that format from their download options.

Thanks,

 

[johnmeyer]

I would be happy to share it, but I don't think I can do so on this site because I don't think Mr. Excel allows me to upload complete spreadsheets, especially those which contain macros.

If someone knows how to share this, let me know.

Let me describe what I did, just to make sure you really want it. My workbook has three worksheets and two macros. The first worksheet is the output that is used by my macros to create the QIF file.

This worksheet is driven by the second tab which has the CSV data on it.

This data is exactly what the bank provides. I simply go to my credit card bank (BofA for one and Chase for the other) and download the month's transactions in CSV format. (You will have to modify the spreadsheet and the macros slightly if your CSV data isn't exactly the same). I have the spreadsheet set up so it prompts me to retrieve the latest CSV data. Once retrieved, the QIF is automatically created on the first worksheet. On that worksheet I have a button which, after I inspect the QIF and make any changes (like the categories), will actually export that information in true QIF format. The macros are used for that because I have to add the proper header at the beginning of the QIF, and then add a format character in front of each line, plus line separator characters, etc. (See the macros below).

The final worksheet is a little window dressing. I put all my common transactions in this sheet, including the categories for those transactions. It renames and shortens the transaction name, just like the native Quicken download does. However, this is the one thing that is still a little crude compared to directly downloading the transactions using Quicken because they usually provide categories for you. However, since I chose to make my categories different, this actually is no big deal for me, and I have come to like this better.

Here are the two macros. One is for my Merrill-Lynch credit card, and the other is for my Chase credit card.

Sub CSVtoQIF()
    Dim iFile As Integer
    Dim LastRow As Long
    Dim rng As Range, cell As Range
    Set rng = Worksheets("QIF").Range("All_Cells")
    iFile = FreeFile
   
    Open "e:\temp Quicken BofA.qif" For Output As #iFile 'Change path to suit
    Print #iFile, "!Type:Cash ";
    Print #iFile, 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 #iFile, cellValue;
       
        If j = rng.Columns.Count Then
            Print #iFile, vbCrLf;
            Print #iFile, "^";
            Print #iFile, vbCrLf;
        Else: Print #iFile, vbCrLf;
        End If
      Next j
      If cellValue = "##EOF##" Then
         Exit For
      End If
    Next i
    Print #iFile, vbCrLf;
   
    Close #iFile
    MsgBox "Finished"
End Sub

Sub CSVtoQIF_Chase()
    Dim iFile As Integer
    Dim LastRow As Long
    Dim Prefix As String
    Dim rng As Range, cell As Range
    ActiveCell.CurrentRegion.Select
    Set rng = Selection
    iFile = FreeFile
   
    Open "e:\temp Chase.qif" For Output As #iFile 'Change path to suit
    Print #iFile, "!Type:Cash ";
    Print #iFile, 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
      
        Select Case i
            Case 1
                Prefix = "D"
            Case 2
                Prefix = "N"
            Case 3
                Prefix = "P"
            Case 4
                Prefix = "L"
            Case 5
                Prefix = "A"
            Case 6
                Prefix = "T"
        End Select
        Print #iFile, Prefix & cellValue;
       
        If j = rng.Columns.Count Then
            Print #iFile, vbCrLf;
            Print #iFile, "^";
            Print #iFile, vbCrLf;
        Else: Print #iFile, vbCrLf;
        End If
      Next j
      If cellValue = "##EOF##" Then
         Exit For
      End If
    Next i
    Print #iFile, vbCrLf;
   
    Close #iFile
    MsgBox "Finished"
End Sub

One of the great things about this is that I was able to go way back to an eight year old version of Quicken that is 10x faster and free from all the bugs in the later Quicken garbage releases. Each new version in the last decade has been slower and buggier, to the point that I was having a tough time getting things done.

I also never have to upgrade again!! (They haven't added a feature that I actually need since the last century).

So, if someone has an idea of how I can share this spreadsheet, I'd be happy to do so.

 

[marteb]

Hi johnmeyer,

Thank you, This looks like it might work for me. I'll give it a try and get back to you although it may be a few days before I have time. Thank you so much and have a great day.

marteb

 

[marteb]

Hi johnmeyer,

Thank you so much for your help. Sorry for the delay, it has been a busy few weeks.

I was able to setup worksheets after looking at your examples to prep my data. Once I started digging into your VBA code, I realized I had already written some code that would work after some modifications. It worked perfectly to format my data in the correct format. My files are now easily loaded into my software.

Thanks again and take care, marteb

 

[johnmeyer]

Glad it worked. You do have to be able to program in VBA, but other than that, there isn't much to it. It helps to look at the code to see how to create the headers, so hopefully my post helped you get to your solution.

Have a great holiday!