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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,299
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,122,363
Messages
5,595,702
Members
414,013
Latest member
tnobbs

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
Top