Formatting cells in Excell from within Access 1

[scottian]

Is it possible to format columns in Excell from within Access?

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[PHV]

Yes, with Automation.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scottian]

Sorry, i may not have given enough information. I need to automate the import of excell files into a database. But the files arent formatted very well and i end up with errors.
I end up with '7.35063E+12' instead of the correctly formatted number. I thought it may be possible to formatt the columns first and then import, all from within the database, all automated. But i havent a clue where to start, im assuming i need to set up references,,,!!!

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[SkipVought]

"...I end up with '7.35063E+12' instead of the correctly formatted number..."

A NUMBER is a NUMBER regardless of it's DISPLAY FORMAT.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[JoeAtWork]

Research "OLE Automation Excel".

Here's a tip. To figure out the code you would need to change the formatting of a column:
1. Open an Excel sheet
2. Start recording a macro
3. Do the formatting changes you want (in your case how it displays the number)
4. Stop recording the macro
5. Now open the macro you recorded in the Excel VBA environment. You can adapt that code for your own program. You can problem condense the code, as the recorder tends to be more verbose than is needed.

 

[scottian]

Ive managed to come up with this :-

Function FormatExcellCells()
Dim DBPos, FileDir, FileNameID, stFileName As String
Dim oXL As Object 'Excel.Application
Dim oWb As Object 'Excel.Workbook
Dim oSh As Object 'Excel.Worksheet
DBPos = InStr(CurrentDb.Name, "SLOP_SUSB_BHAD_Backup.mdb")
'FileDir is the path to the folder
FileDir = Left(CurrentDb.Name, DBPos - 1)
FileNameID = ""
FileNameID = Dir(FileDir & "*.xls")
Set oXL = CreateObject("Excel.Application")
Set oWb = oXL.Workbooks.Open(FileDir & FileNameID)
Set oSh = oWb.Sheets("ACM008_Pkey2")
With oWb.ActiveSheet
Columns("", "B:B").Select
Selection.NumberFormat = "0"
End With
oWb.Close
End Function

Cobbled together from other bits and bobs. but i get an error -
Runtime error '1004'Application defined or Object defined error. Can anyone help with this and tell me what i need to do.


"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[SkipVought]

    Set oSh = oWb.Sheets("ACM008_Pkey2")[b]
    oSh.Columns("B:B").NumberFormat = "0"[/b]
'don't you want to SAVE here???
    oWb.Close

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[scottian]

SkipVought,

I get Subscript out of range. Runtime error '9'. Is this to do with the number of records in the file?

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks