Hello all,
I have going crazy trying to figure this one out. I have used the copy record set many times and it has worked. Now for some reason I can not get past the copy recordset without getting that error 430. If I give the range an absolute value it will populate the excel sheet and work properly. I have checked my references and they have not changed.
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.7 Library
Microsoft ActiveX Data Objects Recordset 2.7 Library
Microsoft Excel 11.0 Object Library
for the key ones
Below is a copy of my code. Again it was working before but not now. Any help would be greatly appreciated
I have going crazy trying to figure this one out. I have used the copy record set many times and it has worked. Now for some reason I can not get past the copy recordset without getting that error 430. If I give the range an absolute value it will populate the excel sheet and work properly. I have checked my references and they have not changed.
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.7 Library
Microsoft ActiveX Data Objects Recordset 2.7 Library
Microsoft Excel 11.0 Object Library
for the key ones
Below is a copy of my code. Again it was working before but not now. Any help would be greatly appreciated
Code:
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim X As Integer
Dim ddbase As Database
Dim rs As Object
Dim sql As String
Dim GetMonth As Integer
Dim SiteBox As String
Dim myolapp As Object
Dim myItem As Variant
Dim olMailTem As Variant
sql = "SELECT tblProperty.GPNbr, tblProperty.Category, tblProperty.Description,"
sql = sql & " tblProperty.UI, tblProperty.SN, tblProperty.AssignedPerson, tblProperty.RecDate,"
sql = sql & " tblProperty.TurnInDate, tblProperty.SiteID, tblProperty.TransferSite,"
sql = sql & " tblProperty.CatID, tblProperty.Comments, tblProperty.Status"
sql = sql & " FROM tblProperty;"
Set ddbase = CurrentDb
Set rs = ddbase.OpenRecordset(sql, dbOpenSnapshot)
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Activate
Sheets("Sheet1").Name = "Property"
For X = 0 To rs.Fields.Count - 1
xlSheet.Cells(1, X + 1).Value = rs.Fields(X).Name
Next
xlApp.Range("A2").CopyFromRecordset rs
xlSheet.Cells.Select
xlSheet.Cells.EntireColumn.AutoFit
xlSheet.Columns("A:I").Select
xlApp.Selection.EntireColumn.Hidden = True
xlApp.Worksheets("Sheet2").Delete
xlApp.Worksheets("Sheet3").Delete
xlSheet.Cells(3, 11).Value = "This is proprietary information. Please only import this into your property database."
xlSheet.Protect PassWord:="escape"
xlApp.ActiveWorkbook.Protect PassWord:="escape"
xlApp.ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\" & fOSUserName & "\My Documents\PMOProperty" & Format(Now(), "mmddyyyyhnn") & ".xls "