Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Importing/querying data from .mdb file into Excel
Hello everyone, here's my situation... I have a Microsoft Access database file that contains information on computer systems, such as their computer names, their IP addresses, CPU model, POC name, etc. What I want to do with an Excel file is that when I type the computer name in a cell, Excel will then query the .mdb file and automatically fill in other cells with the POC name, IP address, and CPU model of that particular computer system that is contained in the .mdb file. Question #1 Is this possible? Question #2a If it is possible, what would be the best means of doing this?? Question #2b If it's not possible, what would be an alternative to doing what I need it to do (querying the .mdb for information based on a computer name)? Thanks in advance for any help you can provide. -js. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Importing/querying data from .mdb file into Excel
Hi Js, theres a way to doit either by using DAO or ADO I will like to know
how is your level of knowleadge in VBA in excel in order to direct my answer more appropiate. Jose Rojas "jimmy shaker." wrote in message .. . Forgot to mention one thing...when the Excel file queries the Access database, the POC name, IP address, etc will go to certain cells within the spreadsheet if that matters any. Thanks again, js. On Sat, 10 Jan 2004 22:56:52 GMT, (jimmy shaker.) wrote: Hello everyone, here's my situation... I have a Microsoft Access database file that contains information on computer systems, such as their computer names, their IP addresses, CPU model, POC name, etc. What I want to do with an Excel file is that when I type the computer name in a cell, Excel will then query the .mdb file and automatically fill in other cells with the POC name, IP address, and CPU model of that particular computer system that is contained in the .mdb file. Question #1 Is this possible? Question #2a If it is possible, what would be the best means of doing this?? Question #2b If it's not possible, what would be an alternative to doing what I need it to do (querying the .mdb for information based on a computer name)? Thanks in advance for any help you can provide. -js. |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Importing/querying data from .mdb file into Excel
This file might be a help:
http://www.bygsoftware.com/examples/...SqlRequest.zip It's in the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm This workbook demonstrates how to get data direct from an MS Access table, or from an open or closed MS Excel workbook using the workbook function SQL.REQUEST. Recently updated to show the use of SQL.REQUEST in the same workbook. The code is open and commented. -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "jimmy shaker." wrote in message .. . Hello everyone, here's my situation... I have a Microsoft Access database file that contains information on computer systems, such as their computer names, their IP addresses, CPU model, POC name, etc. What I want to do with an Excel file is that when I type the computer name in a cell, Excel will then query the .mdb file and automatically fill in other cells with the POC name, IP address, and CPU model of that particular computer system that is contained in the .mdb file. Question #1 Is this possible? Question #2a If it is possible, what would be the best means of doing this?? Question #2b If it's not possible, what would be an alternative to doing what I need it to do (querying the .mdb for information based on a computer name)? Thanks in advance for any help you can provide. -js. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Importing/querying data from .mdb file into Excel
Jose, Thanks for the reply back. Unfortunately, my knowledge level in VBA is at zero, but I will learn it to get this working. I just didn't know what avenue to take to get this started. I'll start researching DAO and ADO in the meantime. Thanks again! js. On Sat, 10 Jan 2004 22:59:58 -0600, "Jose Rojas" wrote: Hi Js, theres a way to doit either by using DAO or ADO I will like to know how is your level of knowleadge in VBA in excel in order to direct my answer more appropiate. Jose Rojas "jimmy shaker." wrote in message . .. Forgot to mention one thing...when the Excel file queries the Access database, the POC name, IP address, etc will go to certain cells within the spreadsheet if that matters any. Thanks again, js. On Sat, 10 Jan 2004 22:56:52 GMT, (jimmy shaker.) wrote: Hello everyone, here's my situation... I have a Microsoft Access database file that contains information on computer systems, such as their computer names, their IP addresses, CPU model, POC name, etc. What I want to do with an Excel file is that when I type the computer name in a cell, Excel will then query the .mdb file and automatically fill in other cells with the POC name, IP address, and CPU model of that particular computer system that is contained in the .mdb file. Question #1 Is this possible? Question #2a If it is possible, what would be the best means of doing this?? Question #2b If it's not possible, what would be an alternative to doing what I need it to do (querying the .mdb for information based on a computer name)? Thanks in advance for any help you can provide. -js. |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.querydao
|
|||
|
|||
Importing/querying data from .mdb file into Excel
'ok thats good
'lets begin from zero then ' 'first in a lets say the sheet where you want to type the pc name, is sheet1 'the name of your database is PCData 'the name of tha table to look up is PCInformation ' 'If none of these names are what you want then modify to represent your needs 'On sheet 1 we may have headers on row1 so we will begin from cell a2 ' 'Open the VB Editor by pressing alt+F11 ' 'goto Insert Module 'goto Tools Refences find and select "Microsoft DAO 3.51 ojbect library" or the latest one that you find in there. ' 'Once in the Module type the following Option Explicit Public Const DbName As String = "Type Databse Path and Name Here" Sub GetDataFromDB() Dim Db1 As Database Dim Rs1 As Recordset Dim POCName As String Dim IPAddress As Long Dim CPUModel As String Dim LastRow, FirstRow As Variant Dim PcName As String Dim i As Integer Set Db1 = DBEngine.OpenDatabase(DbName) 'Open Connection to Database FirstRow = Range("A1").End(xlUp).Row + 1 'Find Begin of cells because of the headers wi will begin on row 2 LastRow = Range("A10000").End(xlUp).Row 'Find where to stop For i = FirstRow To LastRow PcName = Cells(i, 1).Value Set Rs1 = Db1.OpenRecordset("Select * From PCInformation Where PCName like '" & PcName & "'", dbOpenSnapshot) 'Open The Recordset Cells(i, 2).Value = Rs1.Fields("POCName").Value Cells(i, 3).Value = Rs1.Fields("IPAddress").Value Cells(i, 4).Value = Rs1.Fields("CPUModel").Value Next i Set Db1 = Nothing Set Rs1 = Nothing End Sub "Jose Rojas" wrote in message ... Hi Js, theres a way to doit either by using DAO or ADO I will like to know how is your level of knowleadge in VBA in excel in order to direct my answer more appropiate. Jose Rojas "jimmy shaker." wrote in message .. . Forgot to mention one thing...when the Excel file queries the Access database, the POC name, IP address, etc will go to certain cells within the spreadsheet if that matters any. Thanks again, js. On Sat, 10 Jan 2004 22:56:52 GMT, (jimmy shaker.) wrote: Hello everyone, here's my situation... I have a Microsoft Access database file that contains information on computer systems, such as their computer names, their IP addresses, CPU model, POC name, etc. What I want to do with an Excel file is that when I type the computer name in a cell, Excel will then query the .mdb file and automatically fill in other cells with the POC name, IP address, and CPU model of that particular computer system that is contained in the .mdb file. Question #1 Is this possible? Question #2a If it is possible, what would be the best means of doing this?? Question #2b If it's not possible, what would be an alternative to doing what I need it to do (querying the .mdb for information based on a computer name)? Thanks in advance for any help you can provide. -js. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data from a DataCom file into an Excel workbook | Excel Discussion (Misc queries) | |||
Importing data from an excel file to another with figures and lett | Excel Discussion (Misc queries) | |||
importing text file data into excel | Excel Discussion (Misc queries) | |||
Help with data importing from txt file to excel programmatically | Excel Programming | |||
Importing data from an Excel file on a web server using ADO/VBA | Excel Programming |