Hi,
I have a procedure that transfer data from an Excel spreadsheet to an Access
2000 table. There is a start date and an end date in the range specified.
One of the date fields transfers correctly and the other one transfers as a
text field containing a number like 37917. In the spreadsheet both date
cells are formatted as a date. The data in both fields are entered as dates
(i.e. 10/27/03).
The TransferSpreads heet Command creates a temporary table because it does
not exist at runtime. the previous table is deleted before the new transfer
is performed.
DoCmd.TransferS preadsheet acImport, 8, "tempQualit y", stFileName, True,
"Quality!A3:J10 00"
If anyone can tell me where I might have gone astray or what is causing one
to transfer correctly and the other one not too, I would be greatly
appreciative of your help.
Nathan Bloom
1  7281 
Nathan,
When Access has to create a new table this way it has to estimate
datattype and format. The data probably are just not in the correct
format. You can get a litle more control over the way data are
imported if you import it in a existing table (made empty in stead of
deleted) with the right data-formats for the columns. Other
suggestions:
- don't use range-expressions but use a rangename defining the size of
the table.
- when the referred sheet is always on the same location make a link
using the file menu from the table view in the database window.
Eventually you could switch to other sheets using the tools/database
utilities menu to change the link to another file.
Marc.
"Nathan Bloom" <na*******@eart hlink.net> wrote in message news:<fn******* *********@newsr ead3.news.pas.e arthlink.net>.. . Hi,
I have a procedure that transfer data from an Excel spreadsheet to an Access
2000 table. There is a start date and an end date in the range specified.
One of the date fields transfers correctly and the other one transfers as a
text field containing a number like 37917. In the spreadsheet both date
cells are formatted as a date. The data in both fields are entered as dates
(i.e. 10/27/03).
The TransferSpreads heet Command creates a temporary table because it does
not exist at runtime. the previous table is deleted before the new transfer
is performed.
DoCmd.TransferS preadsheet acImport, 8, "tempQualit y", stFileName, True,
"Quality!A3:J10 00"
If anyone can tell me where I might have gone astray or what is causing one
to transfer correctly and the other one not too, I would be greatly
appreciative of your help.
Nathan Bloom This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Howard |
last post by:
I am trying to use DoCmd.TranferSpreadsheet to import a spreadsheet
into an Access table that's not the CurrentDB. I have the database
open, but I don't see how to tell the TransferSpreadsheet command that
the table is not in the CurrentDB. Is there a way to specify a
filename and table within the table string of the TransferSpreadsheet
command? Is there a way of temporarily changing the CurrentDB to be
the other database so the...
|
by: JMCN |
last post by:
hello,
i have two worksheets that i need to import from a workbook that has a
total of 5 worksheets. i tried to use this line of code but i run into
an error message that it cannot find the object/worksheet "1989" due
to invalid parameter or name?
how does one import the specific worksheets without including the
ranges since the whole worksheet needs to be imported?
|
by: Bill Agee |
last post by:
I am having difficulting importing an Excel spreadsheet into my Access
program using VBA
I want to use the last argument to specify the worksheet name which is RTA.
I get an error which
says that the name worksheet or path is incorrect. However, if I "rem" out
the last parameter and make RTA
the 1st sheet in the Excel workbook, everything is imported correctly.
Unfortunately, I have several
worksheets in the workbook which need to be...
|
by: Sara |
last post by:
Hi -
I've been reading the posts for a solution to my query, and realize
that I should ask an "approch" question as well.
We receive our production data from a third party, so my uers import
the data from Excel into the appropriate tables. There are 6
different databases that receive data, though 4 of them only get one
table each.
I have learned how to automate the data import through
|
by: nutthatch |
last post by:
I want to be able to import an Excel spreadsheet into Access 2K using
the macro command Transferspreadsheet.
However, the file I am importing (over which I have no control)
contains some records that are "dirty" i.e. the field contents do not
comply with the expected format (date/time) and they end up in a
seperate table of import errors. (The records in "error" are actually
empty fields.)
This is a regular event and I do not want to...
| | |
by: Janelle.Dunlap |
last post by:
When I import an Excel file containing hyperlinks into Access, the
hyperlinks turn into text and no longer hold their links. I am using
the TransferSpreadsheet function in Access to import my table. I have
done a bit of searching a come across this code:
UPDATE tblTest SET tblTest.hyperdocs = "#" & & "#";
(where tblTest = name of table and hyperdocs = name of hyperlink
field in spreadsheet
This is supposed to add pound signs...
|
by: JC |
last post by:
Hey there,
I am trying to import an excel spreadsheet using the code below. Every
time it gives me a "cannot locate object" error.
Does anyone know what I am doing wrong?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest",
"H:\everyone\Shared\ASC Processes\State Aid Processing
Databases\Lexington Hurns.xls", 0, "Lexington Hurns!Total Cvue Data"
|
by: kkadakia |
last post by:
I get a daily excel file for a entire month which I want to transfer into Access at the end of the month. So, there are around 20-25 excel files I get by the end of the month, and I would like to import them into Access using a button. The data contained in the excel files is similar, so there should no formatting issues while importing.
I searched through the forums and found the code by mmccarthy for importing excel files. I tried using...
|
by: D.Stone |
last post by:
I'm trying to import an Excel spreadsheet into an existing Access
table using Office 2003. Ultimately, the plan is to do it
programmatically using TransferSpreadsheet, but to check that the file
has no problems, I've done it manually with the Import Spreadsheet
wizard.
The worksheet has 43 rows, and I import a named range defined as
"=Sheet1!$C:$E". The import works, but I get a table with 64K rows,
all but 43 being blank!
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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...
| | |
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...
|
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...
|
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
