ATM Machine (pulling data from secondary source?)

CyTipped

New Member
Joined
Sep 8, 2020
Messages
1
Office Version
  1. 365
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!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows
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! ):

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,076
Messages
5,570,059
Members
412,309
Latest member
marry956
Top