Access to Excel problem...

[Dave Eyley]

I've used a routine that I found here to export data from an Access table into a formatted Excel Spreadsheet.
But a few problems have cropped up...

The routine is supposed to create a table from the sql query and then export the data to a buffer spreadsheet, strFile, and then copy it across to the formatted spreadsheet, strFile2, as I understand it.

The Code is -

DoCmd.SetWarnings False
DoCmd.RunSQL ("SELECT Property.Site, Property.BuildingCode, Property.Property, Space_Room_Data.Block, Space_Room_Data.Floor, Space_Room_Data.Rm_No, [Faculties and Depts].FacDiv, [Faculties and Depts].DeptName, Space_Room_Type.TypeDesc, Space_Room_Use.RmUsDesc, Space_Room_Data.DisabledAccess, Space_Room_Data.OpenAccess, Space_Room_Data.Condition, Space_Room_Data.Area, Space_Room_Data.Capacity INTO ExportTableDept FROM Space_Room_Type RIGHT JOIN (Space_Room_Use RIGHT JOIN ([Faculties and Depts] RIGHT JOIN (Property LEFT JOIN Space_Room_Data ON Property.BuildingCode = Space_Room_Data.BuildingCode) ON [Faculties and Depts].Dept_Code = Space_Room_Data.DataDept) ON Space_Room_Use.RmUs_Code = Space_Room_Data.DataUse) ON Space_Room_Type.Type_Code = Space_Room_Data.DataType WHERE " & Searchstr & " ORDER BY Property.Property, Space_Room_Data.Block, Space_Room_Data.Floor, Space_Room_Data.Rm_No;")
DoCmd.SetWarnings True
Dim ObjExcel As Object
Dim strFile As String
Dim strFile2 As String
strFile = "D:\TestBuffer.xls"
strFile2 = "D:\TestReal.xls"

If DCount("*", "[ExportTableDept]") > 0 Then
DoCmd.OutputTo acOutputTable, ("ExportTableDept"), acFormatXLS, strFile, False
Set ObjExcel = GetObject(strFile)
ObjExcel.Application.Visible = True
ObjExcel.Windows(1).Visible = True
ObjExcel.Windows(1).WindowState = xlMinimized

Set ObjExcel = GetObject(strFile2)
ObjExcel.Application.Visible = True
ObjExcel.Windows(1).Visible = True
ObjExcel.Windows(1).WindowState = xlMaximized

The line: ObjExcel.Windows(1).WindowState = xlMinimized produces an error: Variable not defined. Is there a reference missing?

When the spreadsheets open strFile, the buffer file, contains the data, as expected in unformatted style. strFile2, which is a preformatted XLS file, should end up with the data but doesn't.

Any ideas, anybody?

Dave Eyley

 

[Bat17]

xlMinimized and xlMaximized are probably excel constants. You should be able to look them up in Excel and then just use the numbers in the code.

Peter

 

[Dave Eyley]

Can you explain the method for doing that?

Dave

 

[Bat17]

in XL hit Ctr-G to bring up the Immediate window and ?Variable

?xlMinimized
-4140
?xlMaximized
-4137


HTH
Peter

 

[Dave Eyley]

Thanks Bat17

I'll give that a try...

Dave E