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" <nutin...@yahoo.co.in> wrote in message
news:1136364469.2...@f14g2000cwb.googlegroups.com...

[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 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" <nutin...@yahoo.co.in> wrote in message

news:1136368484.8...@o13g2000cwo.googlegroups.com...

[nuti]

Hi Allen,
Canu kindly let me know as how we can use TransferSpreadsheet 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 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" <nutin...@yahoo.co.in> wrote in message

news:1136375920.0...@g43g2000cwa.googlegroups.com...

[nuti]

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

[nuti]

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

[Allen Browne]

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" <nutin...@yahoo.co.in> wrote in message

news:1136380175....@o13g2000cwo.googlegroups.com...

[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

[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 ;)

[nuti]

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

[David Gatheral]

Hi King of the lazy, one line questions,
What do you mean by 'scripts'?
cheers,
DG

[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