Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-13-2018, 03:22 AM   #1
LizJohnson
Newly Registered User
 
Join Date: Mar 2014
Posts: 98
Thanks: 10
Thanked 0 Times in 0 Posts
LizJohnson is on a distinguished road
Macro help

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

LizJohnson is offline   Reply With Quote
Old 06-13-2018, 03:27 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,540
Thanks: 133
Thanked 1,495 Times in 1,467 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Macro help

This line
Code:
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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-13-2018, 03:45 AM   #3
LizJohnson
Newly Registered User
 
Join Date: Mar 2014
Posts: 98
Thanks: 10
Thanked 0 Times in 0 Posts
LizJohnson is on a distinguished road
Re: Macro help

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

LizJohnson is offline   Reply With Quote
Old 06-13-2018, 06:07 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,540
Thanks: 133
Thanked 1,495 Times in 1,467 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Macro help

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-13-2018, 07:16 AM   #5
LizJohnson
Newly Registered User
 
Join Date: Mar 2014
Posts: 98
Thanks: 10
Thanked 0 Times in 0 Posts
LizJohnson is on a distinguished road
Re: Macro help

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.
LizJohnson is offline   Reply With Quote
Old 06-13-2018, 07:46 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,540
Thanks: 133
Thanked 1,495 Times in 1,467 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Macro help

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.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
convert an Outlook macro to Excel macro smiler44 Excel 5 11-20-2014 01:54 PM
Running a Macro inside a macro in excel lobop3 Excel 2 11-22-2013 12:02 PM
Running excel macro as part of my Access 2007 macro. pete6256 Macros 2 10-28-2011 04:30 AM
How to use a macro to change the macro linked to a command button event? travisbicks Macros 6 12-03-2008 06:15 AM
Macro/VBA Code Open CSV file, save as xls, run excel macro elliotth123 Modules & VBA 7 11-08-2004 01:19 PM




All times are GMT -8. The time now is 05:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World