formating excel columns from access

[Mcgrco]

I need some code that opens all excel files in a directory and formats columns j to l to have no decimal places. i need to do this from VBA in access as the files are created via transferspreadsheet method and are new files. I cannot use ouput to as i need to have several worksheets.

Any help would be apprciated

 

[Smee]

Does anyone have a solution to this one - I have the same requirement now.

 

[Jack Skeleton]

I'm a little bit in a hurry, so I'll write only the code "once known" the Excel file. I hope you know how to do a Loop to find every Excel file in a directory.

To run my code you need two strings: first (call it "mypath") with the whole path where the files are stored (f.e.: "C:\TEMP\EXCELFILES\"), second (call it "myfile") with the whole name of the file ("MYFILE.XLS").

Function FormatExcelFiles (mypath as string, myfile as string)
Dim Exceltmp as object, i as integer

Set Exceltmp = GetObject(mypath & myfile, "Excel.Sheet")

for i = 1 to Exceltmp.application.workbooks(myfile).sheets.count
[COLOR=SeaGreen]' this "for... next" is only if you have more than one sheet to format in every Excel workbook[/COLOR]
        Exceltmp.worksheets(i).columns("J:L").NumberFormat = "#,##0"
next i
Exceltmp.Save
Exceltmp.Close SaveChanges:=False
set exceltmp = nothing

So the event can be:

FormatExcelFiles "C:\TEMP\EXCELFILES\" , "MYFILE.XLS"

 

[Smee]

Thanks Jack - very helpful.

 

[ghudson]

hiding excel manipulations and showing a progress bar