Automate Import Excel Info

  • Thread starter Thread starter gcutter
  • Start date Start date
G

gcutter

Right now we are opening an Excel File that is created new every morning from
an application that names the file 11May09-1.xls where date and the -1 is
cell name. We have been opening this Excel file up every morning and copy
A16:I16 to the end of data like A1442:I1442 and pasting / appending WireLog
Table in Access. Is there a way to automate this. Hopefully from a scheduled
task, if not how about a button or query in Access that will pull data out of
the previous day's Excel File. Like today I open the Access DB and open the
query and it opens 10May09-1.xls and pulls the data starting at A16 through
I16 down to end of data which could be no data or 1500 rows.
A16 = Date
B16 = Time
C16 = WireNo
D16 = AveAmps
E16 = AveVolts
F16 = AveWireSpeed
G16 = ArcOn
H16 = WireUsed
I16 = WireDeposited

Thanks,
Greg
Impulse MFG
 
gcutter,

To answer your question, "Yes", what you want to do is very much possible
from Access.

Now for a few questions and suggesstions.

First, is there any need to append the new data from the A16:I16 to the end
of existing data? While it is possible to do what you are currently doing
exactly like you are doing is, would it not be just as good if you were to
have Access open the new Excel file (you can do this because you know the
path and the naming convention for each new file) and just read the values
from each cell (A16:I16), placing each value into the proper field of a new
record in you table in Access?

To get you started, do a search here in this newsgroup on "Open Excel" or
here is a link for importing an Excel spreadsheet from code:
http://www.mvps.org/access/general/gen0008.htm

Once you have code that will open the spreadsheet, it is just a matter of
reading each value for the known locations and appending this new
informationto a new record set in you Access table.

As you have additional specific questions, just post back and someone will
be glad to try to help.

Good luck with your project.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm
 
gcutter,

To answer your question, "Yes", what you want to do is very much possible
from Access.

Now for a few questions and suggesstions.

First, is there any need to append the new data from the A16:I16 to the end
of existing data? While it is possible to do what you are currently doing
exactly like you are doing is, would it not be just as good if you were to
have Access open the new Excel file (you can do this because you know the
path and the naming convention for each new file) and just read the values
from each cell (A16:I16), placing each value into the proper field of a new
record in you table in Access?

To get you started, do a search here in this newsgroup on "Open Excel" or
here is a link for importing an Excel spreadsheet from code:
http://www.mvps.org/access/general/gen0008.htm

Once you have code that will open the spreadsheet, it is just a matter of
reading each value for the known locations and appending this new
informationto a new record set in you Access table.

As you have additional specific questions, just post back and someone will
be glad to try to help.

Good luck with your project.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm
 
Back
Top