Question: Editing Excel file from Access (1 Viewer)

[odin1701]

Question: Editing Excel file from Access (Still needing assistance)

I am running an export to an excel file from access and after the export I need to open the file and apply formatting to it (format height/width of cells, do an auto filter, etc.)

I'm not sure how to get Access to open the file so that I can work with it. I know all the code for formatting the excel file, all I'm trying to figure out is how to get Access to interface.

I had found another thread here and this code was supposed to work but is erroring out with a user-defined type not defined error. Anyway here is just that code, minus all the excel macro stuff.


Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbook.Open("c:\rmexport.xls")

Any help would be appreciated.

 

[Bat17]

you need to set a reference
in any module- Tools>Reference... and scroll down and select the Microsoft Excell object libary.


HTH

Peter

 

[boblarson]

Check out this post here - I posted a sample on how to do it.
http://www.access-programmers.co.uk/forums/showthread.php?t=125039&highlight=Excel

 

[odin1701]

Cool, thanks guys, this should get it to work.

Just need to fix up my export module so that it can pass the name of the exported file on to this module.

 

[odin1701]

Still having problems. I got it to work, it completes the export and formats the .xls file on the first run. However, if you run it a second time I get an error when it tries to apply the formatting to the .xls file. Also after the export completes the first time, excel.exe is still listed in my task manager. Here is the code that I have, if you guys could see if there is some issue that I'm not seeing I would appreciate it.

BrowseForXLS is just a function for opening a dialog box used to export the file.


Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim CurrentLoc As String

CurrentLoc = BrowseForXLS(CurDir(), "Select the Export Location:")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportValues", CurrentLoc

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(CurrentLoc)
Set xlWS = xlWB.Worksheets("qryExportValues")


With xlWS
columns("A:A").ColumnWidth = 12
columns("B:B").ColumnWidth = 12
columns("C:C").ColumnWidth = 8.5
columns("D").ColumnWidth = 8
columns("E:E").ColumnWidth = 9.25
columns("F:F").ColumnWidth = 40
columns("G:G").ColumnWidth = 8
columns("H:H").ColumnWidth = 13.5
columns("I:I").ColumnWidth = 12
columns("J:J").ColumnWidth = 15.5
columns("K:K").ColumnWidth = 9
Range("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
Rows.RowHeight = 100
End With
Rows("1:1").RowHeight = 12.75
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Selection.AutoFilter
Range("A1:A1").Select
End With

xlWB.Save
xlWB.Close
xlApp.Application.Quit

Set xlWB = Nothing
Set xlWS = Nothing
Set xlApp = Nothing

 

[odin1701]

The error I get is the following:

Run-Time Error 1004

Method 'Columns' of '_Object' failed

It fails on the first line after "With xlWS"

 

[odin1701]

Check out this post here - I posted a sample on how to do it.
http://www.access-programmers.co.uk/forums/showthread.php?t=125039&highlight=Excel

I checked out this file and it does not work either. I get a similar error 1004 when it tries to select rows 1:1.

The code in there is the same as how I am doing the opening/closing in my access app.

 

[pbaldy]

Missing something important here. Note this:

With xlWS
columns("A:A").ColumnWidth = 12

will be evaluated as

xlWScolumns("A:A").ColumnWidth = 12

Notice anything missing?

 

[boblarson]

Okay Paul - spell it out

P E R I O D

 

[odin1701]

Well if it needed the period, why did it work fine and perform everything correctly the first time it was run, and then the next time it errored out?

I put some error handling code in to see what was going on and for some reason the active workbook was set to nothing on the second run through instead of being set to the file I exported. The first run through the active workbook was set to the exported file as it should be, and all the operations were performed as expected with no errors.

I did get it working now, though I had to preface some of the code with Excel.Application rather than use xlapp.whatever. Not sure why. xlapp.quit wasn't quitting the process.

 

[boblarson]

Don't use ActiveWorkbook - use the explicit reference and just as an FYI, sometimes using With Selection will have a problem. Use the explicit reference.

 

[odin1701]

Don't use ActiveWorkbook - use the explicit reference and just as an FYI, sometimes using With Selection will have a problem. Use the explicit reference.

The only thing I was using the ActiveWorkbook for was to pop up a message box which would tell me what the active workbook was. It would tell me the correct file on the first run, then on the second run it would be set to nothing. That's the only thing I used it for was for some simple debugging.

I eliminated the With statements completely so perhaps that was part of the problem, although it should work that way I set them all to the explicit references. Of course the code works flawlessly as an excel macro, just seems to be a bit more picky running the code through access.

Thanks for your help though guys.

 

[boblarson]

Of course the code works flawlessly as an excel macro, just seems to be a bit more picky running the code through access.

yes, that is truly the case. Outside of it's natural environment, using another object model sometimes takes more than when used in it's natural state. And using the Excel Object Model in Access is one of those cases.