ATM Machine (pulling data from secondary source?)

[CyTipped]

Hello all,

So I'm pretty new to Excel VBA but with the help of YouTube I've been learning a lot.
I want to make an interactive executable for my math students.

I have an ATM Machine already created with buttons and the like. What I'm trying to do is access information from another Excel file. For example, the other Excel document will have Column A1-A9 (with different 6 digit Account #s) and B1-B9 (with different Balance amounts). So in the ATM Machine VBA interface if a user typed in 882412 it would locate that account # somewhere between A1-A9 then display the corresponding value from Column B (so if 882412 was located in cell A6 it would give the balance from cell B6, etc.).

For the most part my VBA ATM Machine is done. Just need help with coding to grab the necessary info.
If someone has the coding set that'd be great and/or links to sites would help too.

*Ideally I'd like it to grab that Balance info from Google Sheets but the forum states not to ask so hoping someone can steer me in the right direction to using an Excel doc. then?

Thanks!

 

[Rijnsent]

Hi CyTipped,
you could do google sheets (no clue why asking that would be forbidden here...). Open a google sheet, put in your info and go for "file->publish online" and e.g. select CSV. Next, go to your excel, go to Data->add data source from Web and use the URL you got from your google sheet. Now you only need to refresh that query with VBA if you want to update the data.
If you want to go the Excel way, this code can get you started (from Open Existing Workbook using Excel VBA - Example Codes from a Folder - Learn Excel, VBA, SQL, SAS and Data Analysis - ANALYSISTABS! ):

Sub ExampleOpenAnExistingWorkbookSet()
        
        'Declaration - declare an object for setting the workbook
    Dim wkb As Workbook

    'Open Workbook and set an object(wkb)
    Set wkb = Workbooks.Open("C:\WorkbookName.xls")
    'Search for the value here, e.g. with a loop

    'Close opened workbook using object
    wkb.Close SaveChanges:=True

End Sub

If you have a prototype of your code/sheet you want to share, do feel free to do so (through e.g. google drive, dropbox, etc).

Cheers,
Koen