Delete Excel Sheet Before Import to Access

[slenish]

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

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

[slenish]

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.

[NeoPa]

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

[slenish]

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

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

[NeoPa]

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.

[NeoPa]

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

[slenish]

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

[NeoPa]

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.