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 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?
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
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?
Hi Allen,
Canu kindly let me know as how we can use TransferSpreadsheet to import
the data from excel?
cheers,
Nuti
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
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
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
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
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
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 ;)
Hi Red,
How to export data from access to excel using scripts.
cheers,
Nuti
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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.
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
| |