473,883 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Delete Excel Sheet Before Import to Access

283 Contributor
Hello,

I'm trying to write a small VBA code that will delete the first sheet (named Sheet1) from an Excel workbook before I import it. I have the import code working just fine but the information I need is on the second sheet and I cant figure out how to either reference the second sheet or just delete the first sheet.

Appreciate the help,

Slen :D
Jan 3 '12 #1
9 1980
NeoPa
32,584 Recognized Expert Moderator MVP
The Help system wasn't too much help with this Slenish, but I managed to find that the Range parameter can take a range string value which includes a reference to the actual worksheet too. Say your data is held in a worksheet called [Sheet3], then the value for Range would be something like "Sheet3!A1:V99" .
Jan 3 '12 #2
slenish
283 Contributor
Hi NeoPa,

hmm I have tried the Range command but that has been getting me no where. Do you want me to post what I have? Maybe we can figure out where to put something in to the code I already have.

I can make it loop threw the sheets to each one but the problem is that sheet 1 and sheet 2 have the same headers so it pulls in information for both sheets when I only need the second one. That is why i want to just delete the first one.

I'll put together a test db and post it.
Jan 3 '12 #3
NeoPa
32,584 Recognized Expert Moderator MVP
A better idea would be to post the code you've tried in a normal post, but if you want to attach a ZIP file with your database and spreadsheet too, then that won't be a problem. See Attach Database (or other work).
Jan 3 '12 #4
slenish
283 Contributor
Hi NeoPa,

Sorry it took me a little bit to get this set up. I kept messing with it more and I actually got the delete feature to work, but there is still a problem. It opens and asks you if you want to delete the sheet. Was wondering if you know anyway to make it just delete with out asking?

Appreciate you taking a look at this

Slen :D
Attached Files
File Type: zip Test DB.zip (43.8 KB, 98 views)
Jan 4 '12 #5
Mariostg
332 Contributor
NeoPa:
The Help system wasn't too much help with this Slenish, but I managed to find that the Range parameter can take a range string value which includes a reference to the actual worksheet too. Say your data is held in a worksheet called [Sheet3], then the value for Range would be something like "Sheet3!A1:V99" .
You don't need to delete any sheets. for the Range argument for the transferspreads heet function, put a "!" at the end of the sheet name to import "Sheet1!". Works in Access 2003.
Jan 4 '12 #6
NeoPa
32,584 Recognized Expert Moderator MVP
Slenish:
Appreciate you taking a look at this
You seem to have misunderstood my earlier post where I explained you should post the code that you've tried or are working on, with a clear description of the problems involved with the code. When I have some code to work with I may look for more detail in the attachment, but I never look for the question in attachments. That has never struck me as a sensible way to approach things.
Jan 4 '12 #7
NeoPa
32,584 Recognized Expert Moderator MVP
Mario has kindly filled in the gap from my post #2, where I was unable to work out (guess - as the Help System had no info on it) the syntax of the Range parameter in order to tell it to use a particular sheet alone (IE. without specifying a range within the sheet as well).
Jan 4 '12 #8
slenish
283 Contributor
Appreciate the help on this I got it working for what I needed it to do.

Sorry I forgot the post the code in question to go along with the attachment. I'll make sure to do that going forward.

Thanks again :D
Jan 4 '12 #9
NeoPa
32,584 Recognized Expert Moderator MVP
Good enough Slenish. It was an interesting question, and I learned something while looking at it. You might want to select one of the posts that provided the answer for you. In fact, I'll change post #6 to include a quote of post #2 then you can select it (post #6) as Best Answer and it will reflect both parts of the answer for anyone else looking for help on any similar problem.
Jan 5 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

9
4038
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....
5
1780
Zerin
by: Zerin | last post by:
I have to develop a database in access and VB 2005 will be in front view. Some results are send to this software in .csv format and .jpg format and I have to store it in the database.Now, for .csv may be they'll use Excell sheet.........I'm not sure about it..........I'm new in this kind of arrangement. Can anyone advice me about how can I read from an Excell Sheet and .jpg format files and store it in the database?
0
1801
by: jj | last post by:
I'm going mad! I'm exporting a query to excel, and afterwards calculating a field, and a sum of all fields in Excel from a sub procedure in a Module in Access. First I used xlSheet.Range("L" & nNoRows + 2).Select 'ActiveCell.FormulaR1C1 = "=SUM(R2C:RC)"
1
3607
by: gpgp | last post by:
I have a excel macro like this: 1. Range("C2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.NumberFormat = "d-mmm-yy;@" Selection.TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote,...
2
1664
by: saddist | last post by:
Hello, I have and excel sheet with fields: Name | Surname | Dept. When I execute the following code, it imports 16 empty rows + filled ones DoCmd.TransferSpreadsheet acImport = 0, acSpreadsheetTypeExcel9 = 8, _ "importowaneDane", sciezkaPliku, True In the excel sheet the only difference between those 16 empty rows and others is formating (different font and size). Any idea what can be wrong? I also tried to...
3
5596
by: inepu | last post by:
I have some excel sheets that I would like to manage in access. I've created the access tables and relationships; the excel sheet is only one table and with duplicate data, I'd like to make sure that when I import the sheet, the proper tables are filled correctly. For example, the excel sheet is in the format clientID Name phone City Country In acces, I have a "Cities" table and a "Coutries" table, with the fields (e.g. for cities) ID...
0
2771
by: satenova | last post by:
Hello Friends, I am newbie when it comes to vb.net and i need to import excel sheet into access db using vb.net application. Here is the code i use for now to upload excel sheet and it works perfectly but i when i upload the file again it should include only the new records by checking the existence of ID field which i use as primary key. For now it pops up the message"Table Exists already"Could anyone help me pls? Dim...
0
1262
by: sadhana86 | last post by:
Hi There, I have a excel sheet which is a report, which I run in a SAP application. Now I have so many fields, below is the example of the report: User_iD Action Info_type Sub_Info_Type Date_of_Action 12345 Start 1 4 20/09/2010 ABCDE Start 1 5 21/09/2010 12345 Start 1 4 20/09/2010 12345 Start 1 6 21/09/2010 12345 ...
3
13308
by: Keith Faulkner | last post by:
I am trying to add a logo from a bitmap file to an Excel report which is created using MS Access 2010. This code works, but it inserts the bitmap as a linked file. So when I view the excel file on another workstation which does not have the bitmap, the logo is shown with the message "The Linked Image cannot be displayed" Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim PicLocation As String...
2
1498
by: Sally O Askar | last post by:
Please help me, I am designing a small application that takes an excel sheet , this sheet contains my students marks inside a table named grade1. I want to import this table to my access table I use this command: DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Grade1A", varFile, True, "grade1A" but I dont want to put the name of the range " grade1A" , I just want access to retrive the name from the excel sheet, or open the...
0
9781
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,...
0
11123
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
10734
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
10836
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
9567
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
7960
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
5794
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...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3230
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.