Retrieve Named Range Created By Import External Data

johnmeyer

New Member
Joined
Oct 23, 2011
Messages
46
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDE
1Posted DateReference NumberPayeeAddressAmount
25/9/201924427339128710032062810LUCKY #703 CARMEL CACARMEL CA-15.81
35/8/201924431069128898000148724COSTCO WHSE #0131 SEASIDE CASEASIDE CA-160.91
45/8/201924316059127548764002359SHELL OIL 57444694509 CARMEL CACARMEL CA-50.95
55/8/201924431069127975019550404SAFEWAY #2669 CARMEL CACARMEL CA-4.85
65/7/201924906419126072653887328Netflix.com netflix.com CAnetflix.com CA-21.6
75/7/201924427339126710011809472LUCKY #703 CARMEL CACARMEL CA-34.15
85/6/201924257959125030010155608Griggs Nursery Carmel CACarmel CA-12

<tbody>
</tbody>
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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."
 
Upvote 0
Would you be willing to share your code to create qif files? My bank recently removed that format from their download options.

Thanks,
 
Upvote 0
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.

1606965234560.png


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

1606965315136.png


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.

1606965356739.png


Here are the two macros. One is for my Merrill-Lynch credit card, and the other is for my Chase credit card.
VBA Code:
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
VBA Code:
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top