473,289 Members | 1,959 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

reading data in to Access From Excel

Hi all,
I am fairly new to VB.I am trying to figure out as how to write a
script so that i can read the data from an excel sheet to Access.

can u guys please help me out?

cheers,
nuti

Jan 4 '06 #1
13 13188
The simplest way is to connect to the Excel data via:
File | Get External
You can link if the data is regularly updated in Excel and you want the
latest data, or import if you want to operate on the data in Access without
affecting the spreadsheet.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"nuti" <nu********@yahoo.co.in> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi all,
I am fairly new to VB.I am trying to figure out as how to write a
script so that i can read the data from an excel sheet to Access.

can u guys please help me out?

Jan 4 '06 #2
Hi,
Thanks Allen ....But i want to automate it by writing a macro in the
excel sheet.
On running the macro it should write the data in to access.
can you send me the code for it?

cheers,
Nuti

Jan 4 '06 #3
From in Access, you would use the TransfrerSpreadsheet action to get stuff
from Excel

If you want do know how to do it in Excel, you might ask in one of the Excel
groups.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"nuti" <nu********@yahoo.co.in> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Hi,
Thanks Allen ....But i want to automate it by writing a macro in the
excel sheet.
On running the macro it should write the data in to access.
can you send me the code for it?

Jan 4 '06 #4
Hi Allen,
Canu kindly let me know as how we can use TransferSpreadsheet to import
the data from excel?

cheers,
Nuti

Jan 4 '06 #5
This works from inside Access.

Choose the Macros tab of the Database window, and click New.
Choose the TransferSpreadsheet action.
In the lower pane, enter the parameters.

If you prefer to work in VBA code rather than the Access macros, the code
would be something like this:
DoCmd.TransferSpreadsheet acImport, , "Table1", "C:\MyFile.xls"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"nuti" <nu********@yahoo.co.in> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Hi Allen,
Canu kindly let me know as how we can use TransferSpreadsheet to import
the data from excel?

cheers,
Nuti

Jan 4 '06 #6
Hi Allen,

Its working fine with trasferspreadsheets action.

Is it so that the code snippet that u hav send works from the excel
macro?

Thanks A lot

cheers,
Nuti

Jan 4 '06 #7
HI Allen,

DoCmd.TransferSpreadsheet acImport, , "Table1", "C:\MyFile.xls"

The code is working fine.But i want to give the path of the excel
dynamically.
i mean we should give the name of the excel sheet.

cheers,
Nuti

Jan 4 '06 #8
If you want to programmatically pop up a dialog asking for the file name,
there's a bit more to it:
http://www.mvps.org/access/api/api0001.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"nuti" <nu********@yahoo.co.in> wrote in message
news:11********************@o13g2000cwo.googlegrou ps.com...
HI Allen,

DoCmd.TransferSpreadsheet acImport, , "Table1", "C:\MyFile.xls"

The code is working fine.But i want to give the path of the excel
dynamically.
i mean we should give the name of the excel sheet.

cheers,
Nuti

Jan 4 '06 #9
Red
Okay, I'm ALL over this one :D

Sorry for taking so long to reply... Excel to Access is my specialty...
I may not know anything about anything else, but, if you want to get
data in between access and excel, I'm an expert :D
The idea of using the transferspreadsheet is a good one, if your
spreadsheet is a spreadhseet that looks like a copy of a table......
but as we all know, noob boses don't know anything about making a table
looking spreadsheet.. they just want to see pretty colors, and
lines....

Depending on if the spreadsheet will be the same or not, your code will
differ...

I will head you in the right direction though... to start....

First, start excel with a :
Dim XLS
Set XLS = CreateObject("Excel.Application")
XLS.Application.Visible = True

This opens Excel, and makes it visible....

Next you need to open the file with a
XLS.application.workbooks.open "Filename here"

You have to use the whole path for the file name, and the extension...
i.e. "c:\program files\msoffice\templates\myfile.xls"

After you have the file open, you can easily maniuplate what you need
with if's and where, and do until's...
Like this will find the first box that contains nothing (in the first
column):

Dim X as integer
do until wkb.cells(X,1) = ""
x = x + 1
loop

another note, when using cells, it's ALWAYS cells(ROWS, COLUMNS)...
lord knows I'vehad my mishaps switching them around :D

Once you get all that down, feel free to ask more questions about excel
interfacing

~Red

Jan 4 '06 #10
Red
oh ya, I forgot to mention....

using the transferspreadsheet method, you CAN access a certain
worksheet like this:

DoCmd.TransferSpreadsheet acImport, , myTable, strInputFileName, ,
"WORKSHEET-NAME-HERE!A1:M31"

mytable = table you want it to go to
strInputFileName = name & path of file you are importing
"WORKSHEET-NAME-HERE" = the name of your worksheet
"!" - don't forget the exclamation point ;)
"A1:M3" the range you wish to import
Now, if your range is going to change, you really should try the method
I mentioned above.. it's easier to use for spreadsheets that like to
change ;)

Jan 4 '06 #11
Hi Red,
How to export data from access to excel using scripts.
cheers,
Nuti

Jan 5 '06 #12
Hi King of the lazy, one line questions,
What do you mean by 'scripts'?
cheers,
DG

nuti wrote:
Hi Red,
How to export data from access to excel using scripts.
cheers,
Nuti


Jan 5 '06 #13
Red
If you are using google groups, look at my reply (#10).. this explains
how to manipulate data from Excel into access...

but long story short (using the examples I used in that reply), you can
do something like:
wkb.cells(1,1) = "Hello world"

~Red

nuti wrote:
Hi Red,
How to export data from access to excel using scripts.
cheers,
Nuti


Jan 5 '06 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: piero | last post by:
Hi, I have a Excel sheet that have a column with mixed data: column can contain data like "892-234-32A" or like "892298343233432" I need to get all data column such as "text" to avoid numeric...
4
by: Ajay Tiku | last post by:
Hi every one, I am trying to build an applicatiuon which is supposed to read some data from excel spread sheet. can some one please help me in showing how. The excel file is created by other...
2
by: thysonj | last post by:
I know the basic way of doing this when you have a static set of spreadsheets but I need to be able to process different spreadsheets every day. These spreadsheets are sent to us by an outside...
1
by: madhu sudhan | last post by:
HI, I want to read data from excel file and export this data to dataset. I know abt the basics of opening a file using application, workbook and worksheet classes. But i don't know how to read...
1
by: syam217 | last post by:
Please help me out in reading data from Excel file and storing it in SQL Server 2000 database. Its very urgent. Thanks in advance.
5
by: aamax | last post by:
I have to read data from an excel spreadsheet. the first column has a variable number of rows that are associated with one value (which is in only one of the rows, and it's variable about which...
1
by: dixon | last post by:
Hi please help i'm trying to read data from excel file using VB 2005 and display it to a listbox and when name clicked on a list box details of that name should be displayed on the labels such as...
1
by: =?Utf-8?B?U2hlZXMgQWJpZGk=?= | last post by:
I read an article on the link: http://support.microsoft.com/default.aspx?scid=kb;en-us;306572 related to reading data from Excel using OLEDB The topic's heading is: How to query and display excel...
8
ammoos
by: ammoos | last post by:
Hi Friends I am getting the following error when I am trying to read data from an excel sheet using sql script OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not...
3
by: Harbin2010 | last post by:
hi, I am trying to read data from an excel file. in one column data is like this: 1 1.1 1.1.1 1.1.2 1.2 1.2.1 1.2.2
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.