Hello All,
Excellent info here Thanks!
I am very new to using access in general and I am on a learning curve.
I'm trying to import an excel workbook (with worksheets) into an access db
via a macro. (I'll get to using VB later on).
What I would like to do is import a single workbook w/three seperate
worksheets into three seperate access tables AND truncate the time stamp
that is used in the excell sheet via a macro.
three sheets in excel formatted as:
Serial# Type reportedtocount time/date
numberofreports
905 57 3 2005-11-05 17:37:54.718
15
each sheet has an individual name (sheet1, 2 , 3) and each reports time to
the 1000th of a sec.
I only need time down to the sec. (minute if easier) and I have to create
three tables.
problem I have now is if I manually import any of the sheets access
complains about the time date format.
I googled and read 40 or 50 items out on the 'net describing access and
1000th of secs...not interested in the additional math as I do not need data
that granular.
second issue if I use a macro it imports the first sheet in the book and I
cannot figure out the syntax to denote the individual sheets.
Thank You All forYour Help!
Regards
-N-- 8 6523
That should have shown excel format as:
905 57 3 2005-11-05 17:37:54.718 15
Thanks
Nick M wrote: Hello All, Excellent info here Thanks! I am very new to using access in general and I am on a learning curve.
I'm trying to import an excel workbook (with worksheets) into an access db via a macro. (I'll get to using VB later on).
What I would like to do is import a single workbook w/three seperate worksheets into three seperate access tables AND truncate the time stamp that is used in the excell sheet via a macro.
three sheets in excel formatted as:
Serial# Type reportedtocount time/date numberofreports 905 57 3 2005-11-05 17:37:54.718 15
each sheet has an individual name (sheet1, 2 , 3) and each reports time to the 1000th of a sec.
I only need time down to the sec. (minute if easier) and I have to create three tables. problem I have now is if I manually import any of the sheets access complains about the time date format. I googled and read 40 or 50 items out on the 'net describing access and 1000th of secs...not interested in the additional math as I do not need data that granular. second issue if I use a macro it imports the first sheet in the book and I cannot figure out the syntax to denote the individual sheets.
Thank You All forYour Help!
Regards
-N--
Nick M wrote: That should have shown excel format as:
905 57 3 2005-11-05 17:37:54.718 15
Thanks Nick M wrote:
Hello All, Excellent info here Thanks! I am very new to using access in general and I am on a learning curve.
I'm trying to import an excel workbook (with worksheets) into an access db via a macro. (I'll get to using VB later on).
What I would like to do is import a single workbook w/three seperate worksheets into three seperate access tables AND truncate the time stamp that is used in the excell sheet via a macro.
three sheets in excel formatted as:
Serial# Type reportedtocount time/date numberofreports 905 57 3 2005-11-05 17:37:54.718 15
each sheet has an individual name (sheet1, 2 , 3) and each reports time to the 1000th of a sec.
I only need time down to the sec. (minute if easier) and I have to create three tables. problem I have now is if I manually import any of the sheets access complains about the time date format. I googled and read 40 or 50 items out on the 'net describing access and 1000th of secs...not interested in the additional math as I do not need data that granular. second issue if I use a macro it imports the first sheet in the book and I cannot figure out the syntax to denote the individual sheets.
Thank You All forYour Help!
Regards
-N--
Check out the TransferSpreadsheet action in Help. In a macro, you'd
select Docmd/TransferSpreadsheet and fill in the arguments.
Salad wrote: Nick M wrote: That should have shown excel format as:
905 57 3 2005-11-05 17:37:54.718 15
Thanks Nick M wrote:
Hello All, Excellent info here Thanks! I am very new to using access in general and I am on a learning curve. I'm trying to import an excel workbook (with worksheets) into an access db via a macro. (I'll get to using VB later on).
What I would like to do is import a single workbook w/three seperate worksheets into three seperate access tables AND truncate the time stamp that is used in the excell sheet via a macro.
three sheets in excel formatted as:
Serial# Type reportedtocount time/date numberofreports 905 57 3 2005-11-05 17:37:54.718 15
each sheet has an individual name (sheet1, 2 , 3) and each reports time to the 1000th of a sec.
I only need time down to the sec. (minute if easier) and I have to create three tables. problem I have now is if I manually import any of the sheets access complains about the time date format. I googled and read 40 or 50 items out on the 'net describing access and 1000th of secs...not interested in the additional math as I do not need data that granular. second issue if I use a macro it imports the first sheet in the book and I cannot figure out the syntax to denote the individual sheets.
Thank You All forYour Help!
Regards
-N-- Check out the TransferSpreadsheet action in Help. In a macro, you'd select Docmd/TransferSpreadsheet and fill in the arguments.
Hello Salad,
Thanks for the reply
I tried that. I can get the workbook to import, but only the first sheet of
the book imports and with no control.
I need to import all three sheets within the book. I can't seem to find help
on that anywhere...
Also the time/date field in excel gets changed to text in access I need
time fields as once they are imported almost all queries will be based on
time comparison.
Nick M wrote: Salad wrote:
Nick M wrote:
That should have shown excel format as:
905 57 3 2005-11-05 17:37:54.718 15
Thanks Nick M wrote:
Hello All, Excellent info here Thanks! I am very new to using access in general and I am on a learning curve. I'm trying to import an excel workbook (with worksheets) into an access db via a macro. (I'll get to using VB later on).
What I would like to do is import a single workbook w/three seperate worksheets into three seperate access tables AND truncate the time stamp that is used in the excell sheet via a macro.
three sheets in excel formatted as:
Serial# Type reportedtocount time/date numberofreports 905 57 3 2005-11-05 17:37:54.718 15
each sheet has an individual name (sheet1, 2 , 3) and each reports time to the 1000th of a sec.
I only need time down to the sec. (minute if easier) and I have to create three tables. problem I have now is if I manually import any of the sheets access complains about the time date format. I googled and read 40 or 50 items out on the 'net describing access and 1000th of secs...not interested in the additional math as I do not need data that granular. second issue if I use a macro it imports the first sheet in the book and I cannot figure out the syntax to denote the individual sheets.
Thank You All forYour Help!
Regards
-N-- Check out the TransferSpreadsheet action in Help. In a macro, you'd select Docmd/TransferSpreadsheet and fill in the arguments.
Hello Salad,
Thanks for the reply
I tried that. I can get the workbook to import, but only the first sheet of the book imports and with no control. I need to import all three sheets within the book. I can't seem to find help on that anywhere... Also the time/date field in excel gets changed to text in access I need time fields as once they are imported almost all queries will be based on time comparison.
In the help for TransferSpreadsheet Action (macro), it mentioned you can
use a Range. The Range would be the workbook, and you can even get a
cell range within the workbook. From help...
"If you are importing from or linking to a Microsoft Excel version 5.0,
7.0, or Excel 97 worksheet, you can prefix the range with the name of
the worksheet and an exclamation point; for example, Budget!A1:C7."
Salad wrote: Nick M wrote:
Salad wrote:
Nick M wrote:
That should have shown excel format as:
905 57 3 2005-11-05 17:37:54.718 15
Thanks Nick M wrote:
> Hello All, > Excellent info here Thanks! > I am very new to using access in general and I am on a learning > curve. I'm trying to import an excel workbook (with worksheets) > into an access db via a macro. (I'll get to using VB later on). > > What I would like to do is import a single workbook w/three > seperate worksheets into three seperate access tables AND > truncate the time stamp that is used in the excell sheet via a > macro. three sheets in excel formatted as: > > Serial# Type reportedtocount time/date > numberofreports > 905 57 3 2005-11-05 > 17:37:54.718 15 > > each sheet has an individual name (sheet1, 2 , 3) and each reports > time to the 1000th of a sec. > > I only need time down to the sec. (minute if easier) and I have to > create three tables. > problem I have now is if I manually import any of the sheets > access complains about the time date format. > I googled and read 40 or 50 items out on the 'net describing > access and 1000th of secs...not interested in the additional math > as I do not need data that granular. > second issue if I use a macro it imports the first sheet in the > book and I cannot figure out the syntax to denote the individual > sheets. Thank You All forYour Help! > > Regards > > -N-- Check out the TransferSpreadsheet action in Help. In a macro, you'd select Docmd/TransferSpreadsheet and fill in the arguments.
Hello Salad,
Thanks for the reply
I tried that. I can get the workbook to import, but only the first sheet of the book imports and with no control. I need to import all three sheets within the book. I can't seem to find help on that anywhere... Also the time/date field in excel gets changed to text in access I need time fields as once they are imported almost all queries will be based on time comparison. In the help for TransferSpreadsheet Action (macro), it mentioned you can use a Range. The Range would be the workbook, and you can even get a cell range within the workbook. From help...
"If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7."
Thanks for your patience!
I got the import range etc working just fine.
Now I'm really challenged here.
I know I cannot create an import def for an excel file, so how do I go about
trimming off the 1000s (format: hh:mm:ss.ssss) i.e. everthing after the "."
or .sss during the import.
I do not know VB at all, but it look like I may be learning it soon enough.
Thanks again for the help!
if you've put the data into a text field, you can use LEFT and INSTR pi********@hotmail.com wrote: if you've put the data into a text field, you can use LEFT and INSTR
I have existing tables set up with a time/date formatted column. when I
import the excell sheet, I get an error:
" The contents of fields in "X" record(s) were deleted, and 0 record(s)
were lost due to key violations."
If I change the time/date field to "text" no error.
I don 't believe I can use a text field as once it is imported, all my
queries are going to based upon date time analysis.
I suppose I may to to query the import into three other tables??
I'd prefer to keep the date in date format ad not convert anything into an
integer as a few non integer reading folks are going to be lookin g at these
queries.
Thanks for your time.
Nick M wrote: pi********@hotmail.com wrote:
if you've put the data into a text field, you can use LEFT and INSTR
I have existing tables set up with a time/date formatted column. when I import the excell sheet, I get an error: " The contents of fields in "X" record(s) were deleted, and 0 record(s) were lost due to key violations." If I change the time/date field to "text" no error. I don 't believe I can use a text field as once it is imported, all my queries are going to based upon date time analysis. I suppose I may to to query the import into three other tables?? I'd prefer to keep the date in date format ad not convert anything into an integer as a few non integer reading folks are going to be lookin g at these queries. Thanks for your time.
Create a table that matches the import table. IOW, make a copy. Then
change the date field to Text.
Create a query to delete all records in the new table.
Create a query to append all records from the new table into the
production table. When you drag the TextDate field to the column enter
something like
TextToDate:Cdate(TextDateFieldName)
Now assign TextToDate to be appended into the DateField. TextToDate is
converted to a date field via Cdate.
Now run the query to delete all the records in the new table.
TransferSpreadsheet into the new table.
Run the append query. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: grawsha2000 |
last post by:
Greetings All,
I have a excel file which is originally a sqlserver table that was
exported as a excel file. I have added more data to this excel file
and now want to import it again to its...
|
by: daniroy |
last post by:
Hello everybody, your help will be very very welcome on a very classic
question...
I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of...
|
by: Mansi |
last post by:
I need to do some research on how to use excel automation from c#. Does
anyone know of any good books related to this subject?
Thanks.
Mansi
|
by: Niklas |
last post by:
Hi
Before I start coding I need to be sure that nobody else has not done it yet
and that I can use it.
I need an import utility which import data from Excel to a database or some
object in...
|
by: ninrulz |
last post by:
I will try to explain my situation. I know that it is hard to offers
solutions without fully understanding what people would like to
achieve.
I receive 2 csv files every month. The csv files...
|
by: Pauline |
last post by:
Dear all,
I have an enormous database (Access 2003) containing sales information, and an Excel tool to enable end users to do planning and forecasting. Untill now I would create several queries,...
|
by: sh.vipin |
last post by:
how to make large macro paste the code as it is
Problem Explanation '--
For example in the program below
/* a.c - starts here */
#define DECL_VARS() \
unsigned int a0;\
unsigned int a1;\...
|
by: Catbkr1 |
last post by:
I have to automatically create some Excel Spreadsheets based on automatically generated .CSV files that are produced overnight. Each .CSV has several columns that need to be deleted. The same...
|
by: DAF LAD |
last post by:
Hi.
I have been running an access Database in Office 2003 for a while now. When I upgraded to Office 2007 one of my macro buttons has stopped working.
The Button looks up to VBA code to Import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |