VBA rename table column (almost there)

C

Coatezy

Registered User.
Local time
Yesterday, 16:47
Joined
Jan 8, 2009
Messages
39
Hi

I have a bit of a strange one.. Have to query a column in a table that is imported from an excel spreadsheet in to access. problem is a new column is added each week so the column name changes.. (I know really bad, but its what I have to work with from a 3rd party)

The one thing that never changes is a column called "total" that always follows after the newly added column each week. So far I have found the vba below which lets me look for a set column name and rename it.

What I was wondering is could I look for column name "total" and then effectively do -1 so it then looks at the column before and renames that column to a set name so I can query it.


Sub RenameField(strTableName As String, _
strFieldFrom As String, _
strFieldTo As String)

Dim dbs As DAO.Database
Dim tDef As DAO.TableDef
Dim fDef As DAO.Field

Set dbs = CurrentDb()
Set tDef = dbs.TableDefs(strTableName)
Set fDef = tDef.Fields(strFieldFrom)

fDef.Name = strFieldTo

Set fDef = Nothing
Set tDef = Nothing
Set dbs = Nothing

End Sub


By the way the previous table it deleted before the new data is imported so the previous rename would not be in the table still. So wouldn't cause an issue when running queries.

Thanks guys! :)
 
Hmmm - this begs the question WHY? Sorry to say that but renaming fields in tables or adding fields in tables should almost never happen if the database is designed properly and is properly normalized. So, what's up?
 
This will tell you how many fields are in your table. Since your total field is always on the end, you should be able to do your -1 to find the new column name.

Code: 
MsgBox CurrentDb.TableDefs("YourTable").Fields.Count
Code: 
MsgBox CurrentDb.TableDefs("YourTable").Fields.Count -1
 
It really is a case of I have to do it like this... unfortunately another large company is supplying some very messy data and this is the only way I can get around this.. This database is not being used to hold any long term data... purely to import some data run a few queries and pump them out as a csv to be sent off to print letters... All data is thing deleted bar one static customer table. I know it isnt the correct way of doing things but unless I have someone manually clean up the spreadsheet before importing this is my only option. (Trust me I dont like it!) :(

Cheers
Tom
 
ghudson said:
This will tell you how many fields are in your table. Since your total field is always on the end, you should be able to do your -1 to find the new column name.

Code: 
MsgBox CurrentDb.TableDefs("YourTable").Fields.Count
Code: 
MsgBox CurrentDb.TableDefs("YourTable").Fields.Count -1
Click to expand...

Thats the another problem.. My total field isnt on the end. There are 4 other columns after the total column that are added each month! :mad: So I think my only option is to look for "total" and -1. :(

Cheers
Tom
 
Last edited:
That's why I asked. I was curious to know whether it was something worth tackling more than just providing a quick answer.

ghudson I think gave you what you need. But post back if not.
 
boblarson said:
That's why I asked. I was curious to know whether it was something worth tackling more than just providing a quick answer.

ghudson I think gave you what you need. But post back if not.
Click to expand...

As above Bob, ghudson's method would work if it was the last column but unfortunately there are other columns that follow and a new one is also added each month.

Thanks for your help so far guys! :)
 
So this is happening in a spreadsheet first and you are importing this spreadsheet and then want to rename the column to the appropriate date? Is that essentially correct?

I'm wondering if we can't manipulate the spreadsheet before importing to make it easier. Do you have a copy of the spreadsheet (without data is find, just the column headers) so we can see what it looks like?
 
Yeah all data is coming from a spreadsheet first. I would ask someone to rename before import but would like to avoid any manual intervention. The sample sheet with the data headers can be found here.

coatezy.co.uk/sample.xlsx

Thanks!
Tom
 
Okay, this should make it easy:

Code: 
Function RenameColumn(strWorkbookNameAndPath As String)
   Dim objXL As Object
   Dim xlWB As Object
 
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(strWorkbookNameAndPath)
xlWB.ActiveSheet.Range("A1").Select
 
Do Until objXL.ActiveCell.Value = "Total"
    objXL.ActiveCell.Offset(0, 1).Select
Loop
 
objXL.ActiveCell.Value = Format(Date, "m/d/yyyy")
 
xlWB.Save
xlWB.Close

objXL.Quit
Set objXL = Nothing
 
End Function
And that should rename the column for you and then you can import it.
 
Thanks,

Just trying now..

Added code to a new module and then in my form added RenameColumn "Path to sheet"

but I receive this error

1004 - Application-Defined or Object-Defined Error

Any ideas?
 
Did you

1. Not name the module the same as the function? The name of the module should be different from the name of the function.

2. How are you calling the function? And on what line does it land if you click DEBUG on the error message?
 
boblarson said:
Did you

1. Not name the module the same as the function? The name of the module should be different from the name of the function.

2. How are you calling the function? And on what line does it land if you click DEBUG on the error message?
Click to expand...

Module is currently just named module2 until working and it stops on line

objXL.ActiveCell.Offset(0, 1).Select

Cheers
Tom
 
Hmmm, strange because I tested it on your sample from within my own test database and it worked great.

So, I know this is probably a pointless request, but can you post the code you currently have in the module and in the place that calls it?
 
boblarson said:
Hmmm, strange because I tested it on your sample from within my own test database and it worked great.

So, I know this is probably a pointless request, but can you post the code you currently have in the module and in the place that calls it?
Click to expand...


I now have it updating the spreadsheet output is current date but it is updating the total column not the column before. Would you still like the above code?
 
Oh, you need the column BEFORE the Total? Sorry, I was mistaken on that. So, how did you get it to work? Post the code here and I'll see what modification might be necessary to get it to do the field before Total. Normally just before the update after the loop I would put

objXL.Activecell.Offset(0,-1).Select

which would move it back one from the Total.
 
Ok, Just changed to -1 but now getting the same error as before.

so I now have in Module2

Code: 
Function RenameColumn(strWorkbookNameAndPath As String)
   Dim objXL As Object
   Dim xlWB As Object
 
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(strWorkbookNameAndPath)
xlWB.ActiveSheet.Range("A1").Select
 
Do Until objXL.ActiveCell.Value = "Total"
    objXL.ActiveCell.Offset(0, -1).Select
Loop
 
objXL.ActiveCell.Value = Format(Date, "m/d/yyyy")
 
xlWB.Save
xlWB.Close

objXL.Quit
Set objXL = Nothing
 
End Function

in my form I just have

Code: 
Private Sub Command3_Click()
RenameColumn "C:\Path to Sheet.xlsx"

MsgBox "finished!!"
End Sub
 
Ok done it! :) Now have (as you said above!) :o

Code: 
Function RenameColumn(strWorkbookNameAndPath As String)
   Dim objXL As Object
   Dim xlWB As Object
 
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(strWorkbookNameAndPath)
xlWB.ActiveSheet.Range("A1").Select
 
Do Until objXL.ActiveCell.Value = "Total"
    objXL.ActiveCell.Offset(0, 1).Select
Loop
objXL.ActiveCell.Offset(0, -1).Select
 
objXL.ActiveCell.Value = Format(Date, "m/d/yyyy")
 
xlWB.Save
xlWB.Close

objXL.Quit
Set objXL = Nothing
 
End Function
 
No, No, No - You don't change the existing one to -1. You add a new one AFTER THE LOOP:

Code: 
...truncated for brevity
 
Do Until objXL.ActiveCell.Value = "Total"
    objXL.ActiveCell.Offset(0, 1).Select
Loop
    objXL.ActiveCell.Offset(0, -1).Select
 
objXL.ActiveCell.Value = Format(Date, "m/d/yyyy")
 
...rest of code here
 
boblarson said:
No, No, No - You don't change the existing one to -1. You add a new one AFTER THE LOOP:

Code: 
...truncated for brevity
 
Do Until objXL.ActiveCell.Value = "Total"
    objXL.ActiveCell.Offset(0, 1).Select
Loop
    objXL.ActiveCell.Offset(0, -1).Select
 
objXL.ActiveCell.Value = Format(Date, "m/d/yyyy")
 
...rest of code here
Click to expand...

Lol, Yes I soon realized that and amended (see above post!) :o I think I'm almost there.. :) Just need to point to correct sheet on final workbook and should be good to go! :D

One other thing.. While making the sample sheet I saved as xlsx. But the sheet sent to us is a xls. Is there a function to turn off the compatibility check on save? As this I'm sure is going to cause as issue when saving. If not I'm sure I can get it to saveas a .xlsm. :cool:

Edit:- Just added xlWB.CheckCompatibility = False and job done!

Thanks very much for your help :D This hopefully means no human errors now! :rolleyes:
 
You must log in or register to reply here.

Similar threads

S
Solved Weird Visual Bug in my Code that Conditionally Hides Columns
Replies
8
Views
293
SparklySpartan
S
T
Problem with NAME Statement
Replies
2
Views
170
Mark_
M
U
Solved Exporting Image Files from Table - Having File Naming Issues
Replies
11
Views
251
GaP42
G
N
Solved Receiving Emails in Access Database using CDO 2000 library
Replies
10
Views
276
sonic8
S
H
Solved Search across multiple fields
Replies
8
Views
543
Harry Paraskeva
H

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom