473,591 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

import excel with macro and truncate 1000s off of time

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--
Nov 13 '05 #1
8 6544
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--

Nov 13 '05 #2
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
numberofrepor ts
905 57 3 2005-11-05
17:37:54.71 8 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 TransferSpreads heet action in Help. In a macro, you'd
select Docmd/TransferSpreads heet and fill in the arguments.
Nov 13 '05 #3
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 TransferSpreads heet action in Help. In a macro, you'd
select Docmd/TransferSpreads heet 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.

Nov 13 '05 #4
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
worksheet s 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
numberofrep orts
905 57 3 2005-11-05
17:37:54.71 8 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 TransferSpreads heet action in Help. In a macro, you'd
select Docmd/TransferSpreads heet 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 TransferSpreads heet 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."
Nov 13 '05 #5
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 TransferSpreads heet action in Help. In a macro, you'd
select Docmd/TransferSpreads heet 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 TransferSpreads heet 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!
Nov 13 '05 #6
if you've put the data into a text field, you can use LEFT and INSTR

Nov 13 '05 #7
pi********@hotm ail.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.
Nov 13 '05 #8
Nick M wrote:
pi********@hotm ail.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:Cdat e(TextDateField Name)
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.
TransferSpreads heet into the new table.
Run the append query.
Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1886
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 original table,i.e, it will overwrite current data in the table but with no change in the schema. How should I handle the issue of PKs in the current table that will be over-written. I know sqlserver dose not adjust PKs when data is over-written,...
15
4200
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 data. They are setup as follow, with a few more columns on the rights as stock prices, dividend and so on as: Date Code Country Adjust Reason Name Shares 25/02/2005 FR0000045072 FR CREDIT AGRICOLE
17
6326
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
10
11808
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 .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify...
3
8804
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 change name each month. Each file contains 13 columns and around 20k rows. In each file, the first row is the heading. I have created a database that helps me sort out the data. Prior to importing into the database, I have been copying the csv...
1
2309
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, export the data to Excel (Excel 2003) and store the Data in the tool for the users to work with. I always have to export all information for a group of users as I never know on what part of the data they want to work. This makes the Excel files very...
36
2529
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;\ unsigned int a2;\
1
2247
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 columns (both in location and name) are deleted each time. The macro checks for Column Headers/Names and if the value isn't equal to one of 8 specifc values, I want to delete the column. I am attempting to create a Macro that executes automatically when...
2
20514
DAF LAD
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 a specific Excel spread sheet from a specific location on my hard drive. This file is updated but never re-named or moved so to save time I have created the import macro to do the import steps for me. This has been working fine for me in Office...
0
8236
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8366
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7995
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8227
tracyyun
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6642
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5735
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3851
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
1467
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1202
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.