How to get Orientation to work in excel?

[PuddinPie]

Hello,

I am trying to get the headers of the exported data from Access97 to excel to be verticle but it does not seem to be liking my orientation line. The code itself works without error just wont flip the headers.

Here is my code:

Private Sub cmdSelect1_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qmakUsageAllDataSys"
DoCmd.OutputTo acOutputQuery, "qryCrossTabSys", acFormatXLS, "M:\DATA\EXCEL\ManualDataUsage.xls", False

Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.sheets(1).Select
objExcel.Range("A1:AA1").Select
objExcel.Range("A1:AA1").Orientation = 90
objExcel.Visible = False
objExcel.DisplayAlerts = False
objExcel.Workbooks(1).Save
objExcel.DisplayAlerts = True
objExcel.Quit
Set objExcel = Nothing

MsgBox "This file has been created"

End Sub

Thank you.

 

[the_net_2.0]

have you tried using the interface in Excel and recording a macro from mouse clicks to see what that provides?

I would also just like to point out that your Excel code is using a combination of both early and late binding. I don't think that proves to a be a problem, but for consistency's sake, you may want to change it to either of these:

dim objexcel as excel.application
set objexcel = new excel.application

dim objexcel as object
set objexcel = createobject("excel.application")[/code]That's just a technicality, but worth noting is some cases.

 

[vbaInet]

the net 2.0 is right there.

Your problem is you're not pointing to the Excel workbook you've just exported data to.

It's either you:

Dim objExcel As Object, xlWrkBk as Object
     Set objExcel = CreateObject("Excel.Application")

Set xlWrkBk = objExcel.Workbooks.Open("M:\DATA\EXCEL\ManualDataUsage.xls")

Or you use GetObject() instead of CreateObject() to get an instance of the workbook. Like:

Dim xlWrkBk as Object

     Set xlWrkBk = GetObject("M:\DATA\EXCEL\ManualDataUsage.xls")

 

[PuddinPie]

If I do it like below It creates a new file with nothing in it. Non of the data moved over.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qmakUsageAllDataSys"
DoCmd.OutputTo acOutputQuery, "qryCrossTabSys", acFormatXLS, "M:\DATA\EXCEL\ManualDataUsage.xls", False

Dim objExcel As Object, xlWrkBk As Object
Set objExcel = CreateObject("Excel.Application")
Set xlWrkBk = objExcel.Workbooks.Open("M:\DATA\EXCEL\ManualDataUsage.xls")
objExcel.Workbooks.Add
objExcel.sheets(1).Select
objExcel.Range("A1:AA1").Select
objExcel.Range("A1:AA1").Orientation = 90
objExcel.Visible = False
objExcel.DisplayAlerts = False
objExcel.Workbooks(1).Save
objExcel.DisplayAlerts = True
objExcel.Quit
Set objExcel = Nothing

MsgBox "This file has been created"

 

[vbaInet]

In that case you need to check your query and ensure that it outputs to the file before you try manipulating it. Your initial query indicated that your query was outputting to the file.

 

[PuddinPie]

It is creating the table just fine and when I don't have the extra lines in the code. It dose output just wont make the headers go verticle.

 

[vbaInet]

Try the GetObject example instead.

 

[PuddinPie]

Ok, that worked. Thank you.

 

[vbaInet]

You're welcome.

 

[the_net_2.0]

very good suggestions iNet.