Macro help

[LizJohnson]

I set up an Access database. I am going to be getting up loads from our IT department that I will have to upload into my Access database on a daily basis. This is going to upload tax information that the tax payer is going to file on-line. The upload will be from an Excel file to Access.

When the developer created his database, he used different headings for his column names than what I am using for my Access fields. Therefore, instead of changing my Access database to match his fields, I want to just create a Macro in Excel that will delete certain columns that I do not need and then rename the columns to match my Access fields. This way, all I have to do each day is run this Macro.

I created this Macro in Excel and saved the Macro in a different Excel sheet so that I could open this sheet each day that would have the Macro.

I tested this with my first test data and it is running perfectly. It is deleting the correct columns and changing the name.

When I test this with my 2nd data sheet, it is stopping at my first name change macro. I've checked the name that the developer is using and it is the same spelling, etc. Nothing has changed for his layout of information.

Any ideas why this won't work on a different spreadsheet?

This is the line that the Macro get hung up on.

Range("Table134[[#Headers],[LICENSE_NUMBER]]").Select

 

[Minty]

This line

Range("Table134[[#Headers],[LICENSE_NUMBER]]").Select

refers to a specific table number, I would place a bet that the table number changes every time the spreadsheet is created at the other end of the process.

Personally, I think you would be better off linking / importing the whole lot to a "staging table" and then only using the columns you need from that. Deleting things seems an unnecessary step.

 

[LizJohnson]

So, what I do is get all of my information on just 1 tab in Excel. I then copy all of that information 3 times onto 3 separate tabs in Excel because when I load the information into Access, they have to go onto 3 separate tables. I am making sure that when I name my sheets that they are in the exact same order with the same names.

Since I have copied the exact information each time, the order of the columns in Excel has not changed.

Also, the Macro is running the first 2 steps, which is deleting the first two columns. It stops on the 3rd step.

Here is what it does before it stops:
Sub CustInfo()
'
' CustInfo Macro
' Prepare headers for Access Customer Info
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Columns("A:A").Select
Selection.ListObject.ListColumns(1).Delete
Selection.ListObject.ListColumns(1).Delete
Range("Table1[[#Headers],[LICENSE_NUMBER]]").Select

 

[Minty]

I'm pretty certain you are making this much harder than you need to.
You don't need to copy the data 3 times. Simply link to the spreadsheet, then query the linked spreadsheet to get your three sets of data.

The query can provide an Alias to the column names to "line up" with your existing tables.

 

[LizJohnson]

Thank you for your suggestion. I'm not sure how that would be easier to do on a daily basis than hitting a hot key to run a Macro? I do appreciate the help, though.

 

[Minty]

If you replace the linked spreadsheet with the new one daily (Which I'm guess you do with the macro adjusted one) , you would then simply run the other updates / queries from Access.

It would automatically use the current version. No processing at all once its set up.