importing spreadsheet to a database that's not the CurrentDB

Howard

I am trying to use DoCmd.TranferSp readsheet 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 TransferSpreads heet 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 TransferSpreads heet
command? Is there a way of temporarily changing the CurrentDB to be
the other database so the TransferSpreads heet command will do what I
want? Any other ideas? Thanks.

Nov 12 '05 #1

Subscribe Reply

4954

Steve Jorgensen

On 7 Jan 2004 14:29:05 -0800, hh*****@harris. com (Howard) wrote:

I am trying to use DoCmd.TranferSp readsheet 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 TransferSpreads heet 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 TransferSpreads heet
command? Is there a way of temporarily changing the CurrentDB to be
the other database so the TransferSpreads heet command will do what I
want? Any other ideas? Thanks.

Either create a link to the table in the other database, and import into that
(if importing into an existing table), or use Automation, to open another
instance of Access, and do the import using <application
instance>.DoCmd .TransferSpread sheet.

Nov 12 '05 #2

Rich P

Just suggesting one more alternative: you could link a table from the
external mdb to your current mdb and use TransferSpreads heet to the
linked table. And something even a little more reliable than that would
be to Transferspreads heet to a local table in the current mdb and use
Insert Into to fill the linked table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #3

Howard

Thanks for the help. I got it working now, but of course, now I have
another question...

I have a column in the spreadsheet that has mostly numbers but has a
couple of text entries at the end. The Access Table that I'm
importing this data into is defined to have all text fields. When I
do the import function (TransferSpread sheet), I get an error (Numeric
Field Overflow). Now, if I change the text entries to numbers (I
don't mean the formating - I mean the actual values), then it works
fine.

It seems like the import function assumes that the spreadsheet field
is a number field (maybe because that's what was in all the previous
rows) and when it gets to the text data, it messes up. Shouldn't it
use the table definition from Access to determine how to import the
data from the spreadsheet? Changing the format of the spreadsheet to
all text fields had no affect.

Any ideas as to why this is happening? Any suggestions as to how to
fix it?

Rich P <rp*****@aol.co m> wrote in message news:<3f******* *************** *@news.frii.net >...

Just suggesting one more alternative: you could link a table from the
external mdb to your current mdb and use TransferSpreads heet to the
linked table. And something even a little more reliable than that would
be to Transferspreads heet to a local table in the current mdb and use
Insert Into to fill the linked table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #4

Pieter Linden

Create an import specification, and set the field types there. Then
pass the specification name as an argument in the TransferSpreads heet
command. Then it should use the spec and know that you intended that
field to be text.

Nov 12 '05 #5

Similar topics

4689

Best way of importing Excel to SQL Server

by: Little PussyCat | last post by:

Hello, I nee to write something that will transfer excel data into an SQL Server table. I have for another database application I wrote have it importing Excel spreadsheet data using cell by cell, row by row method. This is fully automated so the user can choose whatever spreadsheet they want to import and press a button which sits on a VB6 frontend. This has been good for that situsation but it can be very slow when there

Microsoft SQL Server

4029

Need help on importing an Excel Sheet into Access 2000

by: Edward S | last post by:

I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then need to input this in an Access database, where I do a comparison with the Actual cost. The table “TblBudget” in Access is made of 4 fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$. At the moment this method is very cumbersome....

Microsoft Access / VBA

3171

Importing Hidden files

by: dixie | last post by:

If I sent a user an empty database container - dB with no tables and I needed them to import their tables into it and one of their tables was a hidden table with the prefix Usys, is there any way in code I can get that table imported without them having to go to options and show hidden tables and then import it manually? dixie

Microsoft Access / VBA

3910

Importing Excel

by: jillandgordon | last post by:

I am trying to import an excel file into Access 97. It looks perfectly all right but, every time I try to import it, I get to the lst step and am told that it was not imported due to an error. There is no further explanation. What are the kinds of things that make this happen? Thanks from an obvious rookie. Gordon

Microsoft Access / VBA

2451

Q:Importing an Excel file into a VB application

by: Geoff Jones | last post by:

Hi I have a question which I hope somebody can answer. I have written a VB application with which I want to import an Excel file, analyze the data within it and do some calculations. There are in fact five sheets in the Excel file. My original idea was to import the file into access and create a database file; which I did and worked beautifully. It generated five tables in the database as expected. However, I then thought why not...

Visual Basic .NET

6360

Getting data from excel spreadsheet column into access table

by: syvman | last post by:

Hi everyone... I am pulling my hair out trying to do this, and was wondering if someone could give me some assistance... I have an Excel spreadsheet containing several worksheets. I'd like to be able to take all of the data from the second column (B) of each worksheet and append that raw data to an access table. The columns in the spreadsheet do not have headers for use as field names (it's my assumption that my table should have a...

Microsoft Access / VBA

767

Importing Excel SpreadSheets into Access, from Access, only if not hidden.

by: DrewYK | last post by:

I have the project that may never end in front of me. I am creating a routine that will take SpreadSheets from Excel and bring them into Access. I am not using any "DoCmd"s because the goal is for the import code to be moved to a stand alone VB app which will use the Access DB as a workspace to process the data from the spreadsheets. Quite honestly, done right this may not even require Access or Excel to be on the users machine. ...

Microsoft Access / VBA

5452

Importing dynamic range into Access

by: Bongard | last post by:

I have a dynamic range that I would like to use as a linked table into Access. The problem is that Access doesn't seem to want to to recognize the dynamic range when you click on "show named ranges" in the Link Spreadsheet wizard. The reason I want to use a dynamic range is because when the data in my excel spreadsheet changes the linked table will import blank rows that were previously used from the bottom of the spreadsheet. I can't...

Microsoft Access / VBA

2240

Need Help Importing 4 Different Excel Files With The Same Format to Access

by: anoble1 | last post by:

I have a button in my database that when you hit tit, it imports just 1 excel sheet and puts it in a table. Works great. I am wanting to grab 4 more excel sheets from different locations and store them in the same table. Here is my code. I pasted my code in a .txt file so it can be easily read. Thanks!! Private Sub Command35_Click() On Error GoTo Err_Command0_Click Dim appExcel As Object Dim workBook As Object Dim workSheet...

Microsoft Access / VBA

8253

What is ONU?

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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8354

The easy way to turn off automatic updates for Windows 10/11

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

Windows Server

8497

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

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

General

7182

AI Job Threat for Devs

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

Career Advice

6116

Access Europe - Using VBA to create a class based on a table - Wed 1 May

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

Microsoft Access / VBA

5570

Couldn’t get equations in html when convert word .docx file to html file in C#.

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();...

C# / C Sharp

2621

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1802

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1499

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

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

General