reading data in to Access From Excel

[nuti]

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

[Allen Browne]

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********@yah oo.co.in> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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?

[nuti]

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

[Allen Browne]

From in Access, you would use the TransfrerSpread sheet 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********@yah oo.co.in> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.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?

[nuti]

Hi Allen,
Canu kindly let me know as how we can use TransferSpreads heet to import
the data from excel?

cheers,
Nuti

[Allen Browne]

This works from inside Access.

Choose the Macros tab of the Database window, and click New.
Choose the TransferSpreads heet 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.TransferS preadsheet 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********@yah oo.co.in> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. .

Hi Allen,
Canu kindly let me know as how we can use TransferSpreads heet to import
the data from excel?

cheers,
Nuti

[nuti]

Hi Allen,

Its working fine with trasferspreadsh eets action.

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

Thanks A lot

cheers,
Nuti

[nuti]

HI Allen,

DoCmd.TransferS preadsheet 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

[Allen Browne]

If you want to programmaticall y 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********@yah oo.co.in> wrote in message
news:11******** ************@o1 3g2000cwo.googl egroups.com...

HI Allen,

DoCmd.TransferS preadsheet 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

[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 transferspreads heet 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("E xcel.Applicatio n")
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\myfil e.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