Urgent Help Needed

[fulltime]

Hi all, below is a sample of my code


Sub Export()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")

DoCmd.OutputTo acOutputQuery, langName & " Q", acFormatXLS, tbFile, False 'export the query

OpenFile (tbFile) 'using shellexecute to open tbFIle, file tat was exported to excel

Call Convert 'a function tat consists of excel macro to be run on tbFile

CloseFile (tbFile) 'using shellexecute to close tbFile

objExcel.Quit '
Set objExcel = Nothing

wat i am trying to do is to output my query from MS access to excel, then in excel, run the function convert() which consists of excel macros. After convert(), the excel application will close. However, the next time i execute export(), it will show me the runtime 1004 error. saying tat some codes in the convert() failed. I need to acknowlege the error, then when i retry the export() again, it works.

I realised that there is something wrong with the way i close the excel application, this might be bcos, of 2 statments to open the excel application, - CreateObject("Excel.Application") and also the openFile () which uses shell execute to open the excel application.

can anyone chk my codes above for me pls? I am in urgent need of guidance on this issue.. thks..

Thks
FT

 

[KeithG]

Instead of using the sheel command to open and close the spreadsheet why don't you try opening and closing with excel?

objExcel.workbooks.open("FileName")

objExcel.workbooks.close

 

[fulltime]

Instead of using the sheel command to open and close the spreadsheet why don't you try opening and closing with excel?

objExcel.workbooks.open("FileName")

objExcel.workbooks.close

i tried, but under this code "ActiveSheets.Pictures.Insert(ActiveCell.FormulaR1C1).Select", the error is given as compile error, variable not defined, same error as the one i got previously... this code is found inside my convert() function.

previously, the error i encountered was Method ' ' of Object_global is undefined.

 

[fulltime]

Sub Export()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")

DoCmd.OutputTo acOutputQuery, langName & " Q", acFormatXLS, tbFile, False 'export the query

OpenFile (tbFile) 'using shellexecute to open tbFIle, file tat was exported to excel

Call Convert 'a function tat consists of excel macro to be run on tbFile

CloseFile (tbFile) 'using shellexecute to close tbFile

objExcel.Quit '
Set objExcel = Nothing

the1st time i run the above codes, it works... the second time i run it, it will return the error, run time error 91, object variable or with block variable not set. The line causing the error is under my convert (), underlined below.. i am lost.. been trying to figure out wat caused the error for the past 24hrs..


Private Sub Convert(objExcel As Object)

objExcel.Cells.Select
objExcel.Selection.RowHeight = 21.01
objExcel.Selection.ColumnWidth = 4.57
objExcel.Columns("B:B").EntireColumn.AutoFit


strLen = Len(objExcel.Cells(2, 3).Value)
tempStr = Left(objExcel.Cells(2, 3).Value, strLen - 6)


xLoop = 1 'start counting the headings
yLoop = 3

While objExcel.Cells(xLoop, yLoop).Value <> ""
tempLength = tempLength + 1
yLoop = yLoop + 1
Wend


blankPic = tempStr & "00.JPG"

codeLength = tempLength

xLoop = 2 'start from Cell(2,3)
yLoop = 3

For xLoop = 2 To 344
For yLoop = 3 To codeLength + 2
If objExcel.Cells(xLoop, yLoop).Value <> blankPic Then
objExcel.Cells(xLoop, yLoop).Select
'changed
objExcel.ActiveSheet.Pictures.Insert(ActiveCell.FormulaR1C1). _
Select

End If

Next
If objExcel.Cells(xLoop + 1, 1).Value = "" Then
GoTo Proceed
End If
Next

 

[KeithG]

That error means that VBA does not know which object you are reffering too. Where does the code stop? I would try and make objExcel a public variable instead of passing in a reference.

 

[fulltime]

That error means that VBA does not know which object you are reffering too. Where does the code stop? I would try and make objExcel a public variable instead of passing in a reference.

but its illogical tat it runs the 1st time, then second time, after it opens the new spreadsheet in Excel, it gives the error on the underlined code with the error. I will then close the opened spreadsheet. When i run it the next time, it works again. tats y i suspected its got to do with the closing of the file?

I did make it a public object, its still the same error..

 

[fulltime]

That error means that VBA does not know which object you are reffering too. Where does the code stop? I would try and make objExcel a public variable instead of passing in a reference.

the code stopped at objExcel.ActiveSheet.Pictures.Insert(ActiveCell.Fo rmulaR1C1). _
Select

 

[fulltime]

Instead of using the sheel command to open and close the spreadsheet why don't you try opening and closing with excel?

objExcel.workbooks.open("FileName")

objExcel.workbooks.close

with the definitions i had in my code, when i used the 2 lines of codes u suggested, it returned the the error : object doesn;t suport this property or method..