How do you edit an excel sheet before importing it into access

[HibHib]

The company i am interning for is having me format data from an excel sheet into a filterable access graph. Im rather new to VBA and at the moment i have a button that imports an excel sheet and re-formats the information to be more fitting to what access likes. I run into an issue however with some of the things that are on the excel sheet side, being that the first 2 rows in all of the sheets are visual fluff and the third row contains what i need for field names. How can i delete the first 2 rows in the excel sheet, without saving it and have it imported into access. The other issue i come across is that the field names, and table names, from the excel sheet have spaces or periods, which from what Ive experienced error wise is a large no in VBA when they are needed to be referenced. So i would also like to know how i can replace all the spaces in the excel sheet with an underscore before importing, again without being saved to the original excel file. Due to being something that is handled by higher ups in the company absolutely none of this can be manual, aside from the button push that starts the code. Access 2007

[NeoPa]

Your last comment is possibly one of the most important and would have been ideal as the first statement. In such circumstances it is critically important that you get a commitment from those dealing with the origination of the files that the formats of these files never change. This is most easily done by explaining, very truthfully, that any code you write will be heavily dependent on finding the data exactly where you expect it. Such code cannot be designed to be flexible without high levels of skill and experience. Thus, your code will work like an automaton. Perfectly fit for the format supplied. Perfectly useless (or worse) for any other.

You will probably guess from my comments that I, as well as many others I've heard from, regularly deal with systems designed for one format of file that are then expected to deal with the file once the format's been changed. It will probably happen for you too. At least if you make a good effort to get those in charge to appreciate the issue there's a chance that will be minimised. On that point make sure you have good notes that you can refer to again when they come back complaining it doesn't work properly any more. Those notes should be somewhere associated with the file itself. Alternatively somewhere you can get to them easily and quickly when the users do come back.

Changing data in an Excel spreadsheet is one thing but importing directly from a spreadsheet into Access from Excel, as opposed to from a file, is probably a lot more complicated than you want to play with at this stage (Unless you're a very ambitious intern with oodles of self-confidence in an area you have no reason to be). Access also tends not to like importing files if they're open, and thus locked, in Excel. I suggest you make your changes and then save them in a new place, before closing the file in Excel, before, in turn, importing the file into Access.

Having read the last comment now, and returned to what I wrote before, I guess this will have to be done in VBA rather than manually. Possible but not basic I'm sorry to say. I would suggest the best place to start is in the VBA of the database you want to import into. This would have to use Application Automation. This allows you to do the work in Excel by getting Access to open Excel and the file before making the changes and saving the changed copy away somewhere else.

You'll see from what I've said that this is going to be a sizeable project. Not something that anyone here should be doing for you. OTOH if you're prepared to take it on then we can certainly assist when the complications confuse or overwhelm you.

I hope you find this helpful.

[NeoPa]

Let me just add that if your bosses, after realising just how much of a jump into deep water this would be for an intern, do encourage you to proceed with the project, then you could garner some very useful and interesting experience. I suspect you'd enjoy it even if you did get stuck a few times. Probably because you got stuck a few times and managed to get past it. Application Automation is also a very interesting, powerful and useful area. Particularly in office type environments.

[HibHib]

Thank you for the response and for leading me towards what i need to do. Luckily i'm a full time intern with this as my only current project so i have nothing but time to learn. Also luckily, this SHOULD be the last thing i have to do for the project. I will likely be asking for some help as i move further into this step of the project.

[NeoPa]

I'm happy to hear that HibHib.

To get the best responses break down your problems into small bits and ask each bit separately and clearly. Asking clear questions is tough for most people but the more effort you put into it at that stage the better responses you'll get.

A large number of questions aren't a problem in itself. Links can always be used so that you can keep a lot of the background explanation in your first question.

Good luck.

[ADezii]

As always, NeoPa offers you a wealth of professional and invaluable advice. I do agree with him in that you have a challenging task ahead of you, but somehow I do get the impression that you can handle it. I also feel that a little Jump Start may be in order and hopefully will be helpful to you. IMHO, the approach to take would be to open the Spreadsheet via Automation Code, make the necessary changes, then Save it under another Filename (in this case Original Base Filename & _2.xlsx). I created a Sample Demo for you that will Open the Spreadsheet, DELETE the first two Rows, then Save it under another Name. It will be this SaveAs File that you would then Import into Access. I have elected to use Early Binding since it is my personal preference. If you are not sure what that is, you can use Google it. Well, here goes. Should you have any questions, we are here to assist you in any way we can.

Dim strFilePath As String
Dim strWSName As String
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim ws As Excel.Worksheet
 
'********** USER DEFINED SECTION **********
strFilePath = "C:\Test\Delete Rows.xlsx"
strWSName = "Sheet1"
'******************************************
 
Set appExcel = New Excel.Application
Set wkb = appExcel.Workbooks.Open(strFilePath)
Set ws = wkb.Worksheets(strWSName)
 
With ws
  .Activate
  .Rows("1:2").Select               'Select Rows 1 and 2
   appExcel.Selection.Delete        'DELETE Rows 1 and 2
End With
 
'Extract the Base Filename, in this case C:\Test\Delete Rows, then append
'_2.xlsx. Save Delete Rows.xlsx as Delete Rows_2.xlsx
wkb.SaveAs Left$(strFilePath, InStrRev(strFilePath, ".") - 1) & "_2.xlsx"
wkb.Close False
appExcel.Quit
 
Set wkb = Nothing
Set appExcel = Nothing

[HibHib]

Thank you for the example. I *tried* to take your example slightly further but have run into a slight issue. The excel file im using for this project has 12 worksheets/tabs in the same file(all share the exact same layout, it just different values for different months)i tried to set up a loop that would go through each worksheet to delete the first 2 rows in all of them instead of just one. I established a collection of Sheetnames colWorksheets(lngCount) and set that = to strWSName and had it loop based on lngCount. I thought that would work but it only effected the last worksheet in the file and left a bunch of excel files that didn't close. Im assuming this is because i messed up the loop and had it open the excel file multiple times, but i dont know how to fix that

[ADezii]

I will see what I can do tomorrow - keep in touch. You need to Loop thru the Worksheets Collection of the Workbook Object and Set the Worksheet Object Variable equal to each member prior to each deletion.

[NeoPa]

Guys.

Please try to avoid stretching a single thread beyond its basic topic.

I can't split this one as it's already too intertwined. Allow the new user to explore and make mistakes. It will help them in the long run.

Feel free to continue helping ADezii, as I know that's who you are, but try to encourage new users to use their own two feet too. We have to ensure we don't stultify their growth by feeding them too much too soon. They can always come back when they get stuck. We're like a safety net rather than a resource to do too much for them.

Bless you for being who you are, but never forget the saying that to teach someone to fish is better than simply giving them some.

@HibHib.
You can learn a lot from ADezii. I'll leave you in his capable hands for now. Obviously I'll continue to monitor and step in if required.

[ADezii]

1. NeoPa is of course correct - I have learned more from him then you will ever learn from me. Realizing that I have not exactly made this a learning experience for you up to this point, we can continue along those lines. You have stated that you need to process (DELETE Rows 1 and 2) multiple Worksheets (actual number is irrelevant) within the Workbook. You do not need to create a Private Collection since a Worksheets Collection already exists and it consists of all the Worksheets in the active Workbook. What you need to do now is to set an Object Variable (Worksheet) to each member of this Collection, DELETE Rows 1 and 2, then move on. In its simplest form, within the context of your Code, the Logic would be:
   

   '****** CODE INTENTIONALITY OMITTED ******
   Dim wsLoop As Excel.Worksheet
    
   For Each wsLoop In wkb.Worksheets
     'Process each Worksheet here
   Next
   '****** CODE INTENTIONALITY OMITTED ******
    

2. You now need to incorporate what I have shown you into the overall Code.
3. P.S. - For now forget replacing " " with _ and . with "", once you figure out how to DELETE Rows 1 and 2 from all the Worksheets this will become a relatively simple task.

[HibHib]

I got it to work right! But then i tried to replace " ", which seemed rather straight forward. but no matter what i tried it kept popping up with an error in excel saying "Microsoft Office Excel cannot find any data to replace". Initially i thought it was because it was checking the deleted rows, so i changed it so search row 3 but the same error popped up.
Also, thank you for helping out a newby like me, i know it can get frustrating sometimes

[ADezii]

1. Be patient, this is a lot to swallow for a Newbie, especially since you are dealing with Automation Code. IMHO, the easiest approach would be to:
   1. Define a Range Object that will encompass the largest possible Range that will contain Data in the Worksheets.
   2. Use the Replace Method of the Range Object to Replace any Space (" ") with an Underscore (_), then run the Replace operation again to replace any periods (.) with a Zero Length String ("").
   3. This Code needs to applied to each Worksheet Object (within the For Each...Next) after the Deletion of Rows 1 and 2.
   4. The 'Basic' Syntax would be:
      

      Range("A1:C25").Replace What:=" ", Replacement:="_", MatchCase:=False

2. Duplicate this functionality within your Automation Code.
3. Be patient and persist!

[HibHib]

I actually had that in my code, but with with wsLoop.Rows(1) instead of range because i only needed my fields to have no spaces, my issue was actually a dumb mistake on my part, earlier when i had created a collection, it set the excel sheet to read only, which stopped the replace function from working(Tho oddly not the delete function). Now that that dumb mistake is fixed, the code works! Thank you a ton for the help

[ADezii]

You are quite welcome, good luck with your Project.

[jforbes]

I've always seen ADezii's suggestions and code work well, so you could easily continue with what he has provided and know you are going to get what you are asking for and he has provided a method to clean up the spreadsheet, which is exactly what you are asking for.

But I feel that there is another option that should be at least considered.

NeoPa touched on this when he mentioned having Access open Excel an file and modifying it, then saving it, so what I'm proposing isn't entirely unique here.

Another option is within your Access database is to build a function that both cleans up the data as it is being Imported. Both steps are going to happen anyway and as NeoPa mentions this with be an in-depth project. So what I'm suggesting:

• Is to build a Form in Access that allows you to pick the Excel file, maybe even select the import type if you find you will need to start sucking in multiple Excel files in different formats.
• When the user hits the Import button, a function is called that opens the Excel and then the Workbook using Automation Objects.
• Then the routine can parse the Workbook into a set of variables, probably a variable for each column comprising a set for the entire row.
• It can check the variable and see if they are close to valid.
• It can check the data in the variables to see what to do ,like skip the line or clear out the variable, or massage the variables so that they are formatted correctly and in the manner that started you on this project.
• When you have a full set of massaged variable, you can then perform an insert of all the variable into the table you want the values to end up in.
• You can then perform additional queries on the imported data, like if you were to give each import a batch number, but only need to keep the latest batch, you could then delete all records except the ones with the batch number you just created and imported.