Hi All,
I am using the following code to import a spreadsheet into my Access 2003 tblWeekly table: - DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblweekly", strOutFile, True, "impData"
The strOutFile contains the path and file name of the spreadsheet. The "impData" is the range that contains the data I need to import. It is a fluid range (changes from week to week). The spreadsheet is created using Access Automation (reading a txt file report and converts it to a spreadsheet). My user community has requested I add a specific field to the spreadsheet in a specific location, which is not a problem. The issues is that I then import the sheet into a table in the db. This added field will cause the data to break normalization rules as there is already a primary key field linked (one-to-many) to the added field data in the spreadsheet. So, I just want to ignore column "C" in the "impData" range.
I can't find any thread showing how to do this or if it's possible. I have not tested, but if the table field names match the field names in the spreadsheet, will it just be automatically omitted?
The only other thing I can think of would be to do one of the following: - - a. save the workbook, b. delete the column in question, c. import the sheet, and c. close the workbook without saving
- - a. create a new sheet in the workbook, b. copy "impData" to the new sheet, c. Delete the column in question, d. name the range of data on the new sheet without the added column to "impDataRea l", e. import "impDataRea l", f. delete the added sheet.
- - build the impData range omitting the column. Something like:
- With xlSht
-
strRng = .Range(xlSht.Cells(1, 1), xlSht.Cells(2, 1058)).Address & "," & .Range(xlSht.Cells(4, 1058)).Address
-
End With
Then do the import with "strRng" vs "impData". - not sure how this would work either, because "strRng" would be "$A$1:$B$1058,$ C$1:$C$1058" - Does the importsheet support non-continuous ranges?
Methods 1 & 2 above seem kind of "clunky" to me. I was hoping someone out there had a better way! I'll have to try #3, but something tells me Access isn't going to like it.
Thanks in advance...
1 3252 nico5038 3,080
Recognized Expert Specialist
In general I would separate the import from the actual data to be processed.
So in a similar situation I've created a tblImport holding all columns and with an ImportSessionNu mber to keep the imported files "separated" .
Next I fill the actual table using a query selecting the proper columns from the tblImport for the highest ImportSessionNu mber.
This has an additional advantage that (depending on the number of sessions you store) you can "go back" when some data wasn't OK or you want to review historical info.
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Allison Bailey |
last post by:
Hi Folks,
I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....
I would like to open an existing MS Excel spreadsheet and extract
information from specific worksheets and cells.
I'm not really sure how to get started with this process.
I ran the COM Makepy utility from my PythonWin (IDE from ActiveSTate),
|
by: palgrave |
last post by:
Hi,
How do you create an html link from a web page to a read-only excel
spreadsheet without the browser asking if the user wants to open it or
save it?
|
by: Colleyville Alan |
last post by:
I need to import a spreadsheet into an Access table. The spreadsheet has
performance for mutual funds for various periods. The problem is that if
there is no info for a particular period, the spreadsheet contains a dash in
the cell (for example if the fun is only 4 years old and I look in the
columns for 5-year and 10-year performance).
I cannot change the dashes to blank with a global change, the negative
numbers would lose the...
|
by: Ken Hall |
last post by:
Is it possible to create an Excel spreadsheet using VB.NET without
opening the Excel application or having to have the Excel application
on the operating computer?
KH
|
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...
| |
by: Uma Muthu |
last post by:
Hi,
I have an ASP.NET (2.0) webform. I want to import an excel spreadsheet.
In that Excel spreadsheet are specific cells that I want to capture
specific
data from. Is it doable?
Thanks,
Uma
|
by: ssrirao |
last post by:
There is an Excel Spreadsheet containing data, residing in an internet site.
It’s very easy to Import data from a local Excel Spreadsheet into SQL Server Database Table using DTS.
But in my case the Excel spreadsheet resides an internet site which has a static name (Example xyz.xls) but varying data inside it.
The spreadsheet contains three columns a, b, c and data below it.
Only data get updated weekly
So basically I have to automate...
|
by: Problematic coder |
last post by:
Hi,
I have just been asked to create an app that will import a specific
column from an excel spreadsheet, something like sheet1 columnD or a
column with the first row with a value of 'ID' whichever would be
easier. The rest of what I need to do once I have the data I am ok
with but the connecting to the spreadsheet and extracting a column
into an array or dataset or whatever would work best I have no clue.
I would appreciate some...
|
by: rmmiller44 |
last post by:
I have a web page with an HTML dropdown box where users select from among various cities, e.g. Atlanta, New York, Los Angeles, etc. The desired output is a table and charts with data for that city.
I already have a spreadsheet called City.xls set up with the underlying data.
In City.xls, cell C3 of the worksheet called "Output" runs the whole show. If you put "Atlanta" in that cell, it uses lookup, other functions, and pivot tables to...
|
by: Lipper |
last post by:
Is there a way to automate a process to import an excel spreadsheet without the first and last couple of rows into Access?
|
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...
| |
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: 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,...
|
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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |