RE: [AccessDevelopers] Change Excel Column Name

[George Oro]

Thanks for a detailed solution Tom! I will try the Excel Object model first because I just remember that I done this field collection before using ADO.

 

 

cheers,

George

 

 

-----Original Message-----
From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED]On Behalf Of Tom Oakes
Sent: Monday, January 16, 2006 11:25 PM
To: AccessDevelopers@yahoogroups.com; 'YG Access'
Subject: RE: [AccessDevelopers] Change Excel Column Name

Sorry, sent this prematurely:

 

 

You could do it before the import (via the Excel object model), or after the import (via DAO or ADO).

 

'***** code start *****'

Dim objApp As Excel.Application

Dim objBook As Excel.Workbook

Dim objSheet As Excel.Worksheet

 

Set objApp = New Excel.Application

Set objBook = objApp.Workbooks.Open("C:\MyExcelFile.xls")

Set objSheet = objBook.Worksheets(1)

 

'-- rename your header cells: this assumes that they'll always be in the same spot --'

objSheet.Cells(1, 9) = "PrevReading"

objSheet.Cells(1, 10) = "NewReading"

 

objBook.Close True

Set objApp = Nothing

'***** code end *****'

 

 

...or via DAO after your import (to temp table, presumably):

 

'***** code start *****'

Dim db As DAO.Database

Dim td As DAO.TableDef

Dim fld As DAO.Field

Set db = CurrentDb

Set td = db.TableDefs("tblMyExcelImport")

 

'-- fields are zero-based, so you're looking for 8 and 9, not 9 and 10 --'

Set fld = td.Fields(8)

fld.Name = "PrevReading"

 

Set fld = td.Fields(9)

fld.Name = "NewReading"

'***** code end *****'

 

Hope that helps...

 

Tom Oakes
Personal PC Consultants, Inc.
[EMAIL PROTECTED]
503.230.0911 (O)
402.968.6946 (C)
734.264.0911 (F)

---

From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Tom Oakes
Sent: Monday, January 16, 2006 10:44 AM
To: AccessDevelopers@yahoogroups.com; 'YG Access'
Subject: RE: [AccessDevelopers] Change Excel Column Name

You could do it before the import (via the Excel object model), or after the import (via DAO or ADO).

 

 

Dim objApp As Excel.Application

Dim objBook As Excel.Workbook

Dim objSheet As Excel.Worksheet

 

Set objApp = New Excel.Application

Set objBook = objApp.Workbooks.Open("C:\MyExcelFile.xls")

 

---

From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of George Oro
Sent: Monday, January 16, 2006 4:19 AM
To: YG Access Dev; YG Access
Subject: [AccessDevelopers] Change Excel Column Name

I want to import one excel file but before my code will execute, I want to change first the last two columns heading into PrevReading & NewReading.

 

The reason is, the excel file is extracted from a different software using a pre-defined template but the last two columns heading is the Previous Reading & New Reading Date with their respective data. Basically each export from the other software, the last two columns heading will change according to date reading so its not fixed at all.

 

The exported excel file is always composed of 10 columns and save in the same directory and file name.

 

Any tips would be greatly appreciated.

 

TIA

George

 

 

 

Please zip all files prior to uploading to Files section.

SPONSORED LINKS

Microsoft access developer	Microsoft access help	Microsoft access database
Microsoft access training	Microsoft access training course	Microsoft access programming

---

YAHOO! GROUPS LINKS

•  Visit your group "AccessDevelopers" on the web.
   
•  To unsubscribe from this group, send an email to:
   [EMAIL PROTECTED]
   
•  Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

---