Open Existing Excel Workbooks from Access module 2

[lube8]

I am having the toughest time opening existing workbooks in excel from within an access module. I can open a new workbook but I cannot open an existing workbook.

I am trying to apply formatting to several worksheets within several workbooks. My original vba code to transfer data to multiple sheets is a loop (transferspreadsheet procedure within Access module). My toughest problem is applying the formatting into the Excel worksheets and workbooks, once created, using script within the Access module.

Do I need to write code in my Access module to open each excel workbook and apply this formatting to each worksheet (and how)? ... Or can this formatting occur without me opening each workbook and be included within my loop for the transferspreadsheet procedure? Thanks.

 

[duncansancho]

lube8
Could you have the formatting code in the worksheet, either triggered by event, or auto-open?
Duncan

 

[jnspres]

lube8
I am assuming you have create an object variable to hold your instance of Excel. I normally call mine objExcel. To open a particular workbook write:

objexcel.workbooks.open pathname

Once you have the workbook open you can access that workbook by using objExcel. Including formatting. I usually include my formatting within the workbook and then send my data to the pre-formatted workbook.

Once your finished:

objexcel.close

Open another if need be.

Hope this helps.

 

[xlbo]

Using the code I have already provided you with and the objExcel variable as suggested by jnspres:

Dim objExcelApp As New Excel.Application
Dim objExcel As Object
'create Excel
Set objExcelApp = New Excel.Application
'open file
Set objExcel = objExcelApp.Workbooks.Open("D:\booknew.xls&quot
objExcelApp.Visible = True

with objExcel
For each sht in .worksheets
With sht.Columns("A:G&quot
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A1=""Hours Variance"""
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.FormatConditions(1).Interior.ColorIndex = 36
End With
Next
end with

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[lube8]

xlbo,

I will dim the objExcelApp As New Excel.Application tommorrow. I have not tried this yet.

I cannot format first because the workbooks and worksheets are generated through an access module and created tables that get transferred to excel with filenames created by records within tables. Thats why I must format after the files have been created.

Should this procedure allow me to open my created workbooks through the module in access without opening a blank workbook? I will let you know if I have success in this. Thank you all for your help.

 

[xlbo]

Yes - this line:

Set objExcel = objExcelApp.Workbooks.Open("D:\booknew.xls&quot

tells excel to open a specified workbook from a specified path


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[lube8]

This worked great ... the New Excel.Application object does sound somewhat familiar when I put it in use. Thank you very much ... it has been a great help. The only finishing touches I have is trying to format the pages for each sheet within each workbook.

Do you have a simple procedure for doing formatting page setup and is there any way to avoid making each sheet within the workbook active? I basically need to set the zoom to 80% on all worksheets and, in page setup, I would like to repeat the first row, change the left and right margins, change the page setup to landscape, and "fit to 1 page tall" or "best fit". I fiddled around with this with limited success.

 

[PHV]

lube8, in Excel launch a Macro recorder and do all your formatting stuff. Then copy/paste the resulting code in your Access code, prefixing all object references by objExcel.
Make this any sense ?

Hope This Help
PH.

 

[lube8]

I cannot get the formatting to work for more than one worksheet. I have several workbooks with several worksheets and each workbook contains a different number of worksheets.

I see the code either running away (only formatting the first sheet) or giving me errors with object definition ... (but I think I now have that corrected).

I am having a tough time getting the code to format for each sheet within the workbook. I believe I can only do formatting on an active sheet so I think I need the code to cycle through each sheet and apply the formatting. Do you have any ideas how to make this work?

 

[xlbo]

Look at the code I have already given you - it's all in there - my code already loops through all the worksheets to set up your formatting for where there is "Hours Variance" in the cell.

with objExcel
For each sht in .worksheets

'Do stuff

Next

This is not a helpdesk - please try looking and trying to understand what the help ou are being given does. Record the actions for setting up the page as you desire and apply to the code structures that have been provided

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244