By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,824 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

Delete Excel Sheet Before Import to Access

100+
P: 283
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

✓ answered by Mariostg

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 transferspreadsheet function, put a "!" at the end of the sheet name to import "Sheet1!". Works in Access 2003.

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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

100+
P: 283
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
Expert Mod 15k+
P: 31,494
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

100+
P: 283
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, 54 views)
Jan 4 '12 #5

100+
P: 332
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 transferspreadsheet function, put a "!" at the end of the sheet name to import "Sheet1!". Works in Access 2003.
Jan 4 '12 #6

NeoPa
Expert Mod 15k+
P: 31,494
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
Expert Mod 15k+
P: 31,494
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

100+
P: 283
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
Expert Mod 15k+
P: 31,494
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

Post your reply

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