import excel with macro and truncate 1000s off of time

Nick M

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

Nick M

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

Salad

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.

Nov 13 '05 #3

Nick M

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.

Nov 13 '05 #4

Salad

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."

Nov 13 '05 #5

Nick M

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!

Nov 13 '05 #6

pietlinden

if you've put the data into a text field, you can use LEFT and INSTR

Nov 13 '05 #7

Nick M

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.

Nov 13 '05 #8

Salad

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.

Nov 13 '05 #9