Object Variable or with block variable not set

[fulltime]

I think you can close and destroy the objBook to solve that problem. Just leave the Excel object open.

Don't use the QUIT to exit Excel, until the program ends, so you don't open multiple instances of it.

how do i destroy the objBook object? is this enuff??

Set objBook = Nothing

so u mean its ok for me to Set objExcel = Nothing rite?

[dglienna]

objBook can be closed and reopened.

Set objExcel = Nothing

should only be done once. After you use QUIT.

[fulltime]

I think you can close and destroy the objBook to solve that problem. Just leave the Excel object open.

Don't use the QUIT to exit Excel, until the program ends, so you don't open multiple instances of it.

everything is working fine now n i hope the problem wun resurface

phew..its a great load of my shoulder now..

thks dglienna..

FT

[fulltime]

hi all, looks like the problem has resurfaced.

This problem is intermitten and i am stuck at this for the past 2 days

 Dim objExcel As Excel.Application
 Dim objBook As Excel.Workbook

DoCmd.OutputTo acOutputQuery, langName & " Q", acFormatXLS, tbFile, False

Set objBook = objExcel.Workbooks.Open(tbFile)

'tbFile is the name of the file tat i will export my query to

SOMETIMES, i will get the error ---> "File.xls is already opened, Reopening will cause changes to be discarded. Do u wan to reopen File.xls?" It seems like the DoCmd.OutputTo acOutputQuery is causing the bulk of the prob bcos i have another sub tat does excel autommation but never had such prob.

Can anyone give me some advice? I am stuck

[fulltime]

I think you can close and destroy the objBook to solve that problem. Just leave the Excel object open.

Don't use the QUIT to exit Excel, until the program ends, so you don't open multiple instances of it.

now the question is whether the "DoCmd.OutputTo acOutputQuery, langName & " Q", acFormatXLS, tbFile, False" statement will open up an excel obj own its own and whether it is capable of closing it by itself?

[dglienna]

Sounds like you are trying to reopen Excel multiple times. I think you'd be better off putting the declaration in a Module, and it will be executed once when the app starts up. Then close it when the app ends.
Each Worksheet can be declared locally, and set to the right value. Make sure that you close it and set it to nothing in each module.

[fulltime]

Sounds like you are trying to reopen Excel multiple times. I think you'd be better off putting the declaration in a Module, and it will be executed once when the app starts up. Then close it when the app ends.
Each Worksheet can be declared locally, and set to the right value. Make sure that you close it and set it to nothing in each module.

i managed to debug where the problem lies, its an error w the statement below,

objBook.ActiveSheet.Pictures.Insert(objExcel.ActiveCell.FormulaR1C1). _
Select

After i make the ammendments, the error has not resurfaced.. however, i got another prob..

I have 2 different subs using autommation to MS EXCEL..

both subs uses the same codes to open n close MS EXCEL. the closing codes are as follows,

  objExcel.ActiveWorkbook.Close (False)
  Set objBook = Nothing
  objExcel.Quit
  Set objExcel = Nothing

However, onli 1 sub can actually KILL the EXCEL process with the codes above (it is shown in the windows task manager). the other sub DID not kill the EXCEL process.. any reason why?

[fulltime]

i think the problem lies in this statement whereby i am importing a worksheet from MS EXCEL into MS ACCESS...

 Dim objExcel As Excel.Application
 Dim objBook As Excel.Workbook

'initialisation codes...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    newLang & "_Tbl", tbFile, True, "temp!"

what should i prefix it with? ObjExcel or ObjBook??

thks

[dglienna]

Easiest way might be to record a macro, and see what it uses. I suspect that you want the sheet, but haven't imported anything for a while, so it might import the whole book at once. Last think that I imported was a single sheet anyways.

[fulltime]

Easiest way might be to record a macro, and see what it uses. I suspect that you want the sheet, but haven't imported anything for a while, so it might import the whole book at once. Last think that I imported was a single sheet anyways.

yup, i just wanted the sheet.. but even if i record the macro, it will not tell me the type of object tat i shld prefix it with right?

[dglienna]

This may help you, although it exports the opposite way that you want.

Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
Dim row As Long
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim statement As String
Dim i As Integer

    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")

    ' Uncomment this line to make Excel visible.
'    excel_app.Visible = True

    ' Open the Excel spreadsheet.
    excel_app.Workbooks.Open FileName:=txtExcelFile.Text

    ' Check for later versions.
    If Val(excel_app.Application.Version) >= 8 Then
        Set excel_sheet = excel_app.ActiveSheet
    Else
        Set excel_sheet = excel_app
    End If

    ' Open the Access database.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & txtAccessFile.Text & ";" & _
        "Persist Security Info=False"
    conn.Open

    ' Select the data.
    Set rs = conn.Execute( _
        "SELECT * FROM Books ORDER BY Title", , _
        adCmdText)

    ' Make the column headers.
    For i = 1 To rs.Fields.Count - 1
        excel_sheet.Cells(1, i) = rs.Fields(i).Name
    Next i

    ' Get data from the database and insert
    ' it into the spreadsheet.
    row = 2
    Do While Not rs.EOF
        For i = 1 To rs.Fields.Count - 1
            excel_sheet.Cells(row, i) = rs.Fields(i).Value
        Next i

        row = row + 1
        rs.MoveNext
    Loop

    ' Close the database.
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing

    ' Make the header bold.
    excel_sheet.Rows(1).Font.Bold = True

    ' Make the columns autofit the data.
    excel_sheet.Range(excel_sheet.Cells(1, 1), _
        excel_sheet.Cells(8, 6)).Select
    excel_app.Selection.Columns.AutoFit

    ' Freeze the header row so it doesn't scroll.
    excel_sheet.Rows(2).Select
    excel_app.ActiveWindow.FreezePanes = True

    ' Select the first cell.
    excel_sheet.Cells(1, 1).Select

    ' Comment the Close and Quit lines to keep
    ' Excel running so you can see it.

    ' Close the workbook saving changes.
    excel_app.ActiveWorkbook.Close True
    excel_app.Quit

    Set excel_sheet = Nothing
    Set excel_app = Nothing

    Screen.MousePointer = vbDefault
    MsgBox "Copied " & Format$(row - 2) & " values."
End Sub

[fulltime]

yup, i just wanted the sheet.. but even if i record the macro, it will not tell me the type of object tat i shld prefix it with right?

come to think of it, the DoCmd command is actually an ACCESS command to import worksheets from EXCEL. So y shld it be prefixed w anything? YET, this is the command tat is causing all the errors.. how i can solve it??

Thks....

[fulltime]

okies, managed to solve it.. its really the DoCmd statement tat gives the error

here are the setps in pseudo code

originally
'open excel application, workbook, worksheet
'create an additional worksheet to hold temp data
'import the temp data worksheet using DoCmd
'delete the temp data worksheet
'close the excel application, workbook, worksheet


NOW
1.
'open excel application, workbook, worksheet
'create an additional worksheet to hold temp data
'close the excel application, workbook, worksheet

2.
'import the temp data worksheet using DoCmd

3.
'open excel application, workbook, worksheet
'delete the temp data worksheet
'close the excel application, workbook, worksheet

[pchavezc]

see the nex page http://support.microsoft.com/kb/316478

Pedro

[frigidinferno]

I am getting the same error for this line of code:

If (fso.FileExists("C:\WINDOWS\system32\drivers\etc\dataE")) Then

I think I need to check one of the options in Project > references, but im not sure which one :-/.