469,328 Members | 1,308 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

Delete Excel Sheet Before Import to Access

283 100+
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.

9 1703
NeoPa
32,181 Expert Mod 16PB
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 100+
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,181 Expert Mod 16PB
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 100+
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, 68 views)
Jan 4 '12 #5
Mariostg
332 100+
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
32,181 Expert Mod 16PB
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,181 Expert Mod 16PB
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 100+
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,181 Expert Mod 16PB
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.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.