-
April 8th, 2006, 05:23 AM
#1
Object Variable or with block variable not set
Hi all,
I got a very weird scenario here, i am using VBA in my MS access forms to do automation to export a table to exceln run a excel macro from MS access. however, the 1st time i open the db n run this form, it works. The 2nd time, it fails, then i need to go to VB editor n acknowledge the error shown on the thread title, after which, the code will run smoothy infinitly... i cannot tell where is my error.. Can anyone pt out my mistake?
Code:
Private Sub Export_Button_Click() 'export
On Error GoTo Err_Export_Button_Click
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim partName As String
Dim langName As String
Dim tempPath As String
Dim strLen As String
Dim userInput As String
Dim userPath As String
Dim blankPic As String
Dim codeLength As String
Dim tempStr As String
Dim tempLength As Integer 'counts length of code
Dim xLoop, yLoop, codeCounter, i As Integer
codeCounter = 1 'counts the total no of codeword for a particular language set
tempLength = 0
langName = ""
If IsNull(Me![ListInput]) Or (Me![ListInput]) = "" Then 'checks if user has selected any language
MsgBox "Please select one of the available languages.", vbExclamation
Exit Sub
End If
If IsNull(Me![tbFile]) Or (Me![tbFile]) = "" Then 'checks for empty export path
MsgBox "Please enter the destination where the output file will be saved!", vbExclamation, "Empty Path"
Me![tbFile].SetFocus
Exit Sub
End If
Select Case ListInput 'assign the filename based on the listbox input
Case "Malay"
langName = "Malay"
Case "Chinese Simplified"
langName = "CH Simplified"
End Select
If Right(tbFile, 3) <> "xls" Then
MsgBox "File to be exported has to be of .xls extension.", vbExclamation
Exit Sub
End If
' On Error GoTo Err_Export_Button_Click
DoCmd.OutputTo acOutputQuery, langName & " Q", acFormatXLS, tbFile, False
MsgBox langName & " successfully exported to " & tbFile & ". Conversion of codes will now begin in the background. Depending on the file size, this process may take a few minutes.", vbInformation, "Exporting of File Completed"
' DoCmd.Hourglass (True)
Set objBook = Nothing
Set objExcel = Nothing
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open(tbFile)
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
objBook.ActiveSheet.Pictures.Insert(ActiveCell.FormulaR1C1). _
Select
End If
Next
If objExcel.Cells(xLoop + 1, 1).Value = "" Then
GoTo Proceed
End If
Next
Proceed:
objExcel.Range("C2:U343").Select
objExcel.Range("U2").Activate
objExcel.ActiveWindow.SmallScroll Down:=-15
objExcel.Application.CutCopyMode = False
objExcel.Selection.ClearContents
For yLoop = 3 To codeLength + 2 'create row headings for 1st row
objExcel.Cells(1, yLoop).Value = codeCounter
codeCounter = codeCounter + 1
objExcel.Cells(1, yLoop).Select
With objExcel.Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
objExcel.Selection.Font.Bold = True
With Selection.Font
.Name = "MS Sans Serif"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With objExcel.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Next
objExcel.Range("A1:B1").Select 'create the heading for 1st row
With objExcel.Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
objExcel.Selection.Font.Bold = True
With Selection.Font
.Name = "MS Sans Serif"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With objExcel.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
objExcel.ActiveWorkbook.Save 'saves the file after conversion
objExcel.Workbooks.Close
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing
' DoCmd.Hourglass (False)
MsgBox langName & " has been successfully converted to codes in " & tbFile, vbInformation, "Conversion Successfully"
Exit_Export_Button_Click:
Exit Sub
Err_Export_Button_Click:
objExcel.Workbooks.Close
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing
'DoCmd.Hourglass (False)
MsgBox "Error - " & Err.Description, vbExclamation
Resume Exit_Export_Button_Click
End Sub
i been trying to figure this out since yest, can anyone pls guide me along? thks
FT
Last edited by fulltime; April 8th, 2006 at 10:00 PM.
-
April 8th, 2006, 01:05 PM
#2
Re: Object Variable or with block variable not set
Which line give the error? This error happens when you try to use an object that was not initialised, for example, if you would try to use objExcel without using the line Set objExcel = CreateObject("Excel.Application").
JeffB
-
April 8th, 2006, 01:38 PM
#3
Re: Object Variable or with block variable not set
You dim as an Excel App, then set it to nothing, and then set it to an Excel App.
I'd dim as Objects instead, then you can set them to nothing each time.
Otherwise, create them when the app opens, and close them when it's finished and unloading.
-
April 8th, 2006, 10:02 PM
#4
Re: Object Variable or with block variable not set
Originally Posted by JeffB
Which line give the error? This error happens when you try to use an object that was not initialised, for example, if you would try to use objExcel without using the line Set objExcel = CreateObject("Excel.Application").
JeffB
hi jeff,
sorry, i forgot to indicate which line induced the error... I have bolded it in the codes above..
this is the line..
objBook.ActiveSheet.Pictures.Insert(ActiveCell.FormulaR1C1). _
Select
i did initialised the object as an workbook , so i was wonmdering why the error will occur onli on the 2nd time of executiomn, thereafter, no nore errors will occur?
thks
-
April 8th, 2006, 10:05 PM
#5
Re: Object Variable or with block variable not set
Originally Posted by dglienna
You dim as an Excel App, then set it to nothing, and then set it to an Excel App.
I'd dim as Objects instead, then you can set them to nothing each time.
Otherwise, create them when the app opens, and close them when it's finished and unloading.
hi dglienna,
care to elaborate on this statement, Otherwise, create them when the app opens, and close them when it's finished and unloading.??
how do i open the application? n close?
thks
-
April 8th, 2006, 10:12 PM
#6
Re: Object Variable or with block variable not set
Originally Posted by dglienna
You dim as an Excel App, then set it to nothing, and then set it to an Excel App.
I'd dim as Objects instead, then you can set them to nothing each time.
Otherwise, create them when the app opens, and close them when it's finished and unloading.
i tried w ur suggestions,
Dim objExcel As Object
Dim objBook As Object
without making changes to the rest of the codes, but it still gives me the same error..
-
April 8th, 2006, 11:52 PM
#7
Re: Object Variable or with block variable not set
Use DIM in a module, and make tham PUBLIC. Sorry if I forgot to include that.
Just DIM them once, open the connection once, and close it once. You can open and close WorkVBooks in between.
In Module1.bas
Code:
Public objExcel As Object
Public objBook As Object
-
April 9th, 2006, 09:55 PM
#8
Re: Object Variable or with block variable not set
Originally Posted by dglienna
Use DIM in a module, and make tham PUBLIC. Sorry if I forgot to include that.
Just DIM them once, open the connection once, and close it once. You can open and close WorkVBooks in between.
In Module1.bas
Code:
Public objExcel As Object
Public objBook As Object
So u mean i have a seperate module, just to DIM these 2 variables?
just add the 2 lines of codes u shown above in a new module?
Thks, i will try it out and let u know if it works..
-
April 9th, 2006, 11:09 PM
#9
Re: Object Variable or with block variable not set
I was working on a mailmerge from within VB. I found that I had the same problem if we ran the same report a second time. It was suggested that I open the connection once (to MsWord) and keep it opened thruout the program.
Not only did that resolve my error, but it kept multiple processes of MsWord from opening. Not to mention, but Word wasn't running when my app shut down.
-
April 9th, 2006, 11:30 PM
#10
Re: Object Variable or with block variable not set
Originally Posted by dglienna
I was working on a mailmerge from within VB. I found that I had the same problem if we ran the same report a second time. It was suggested that I open the connection once (to MsWord) and keep it opened thruout the program.
Not only did that resolve my error, but it kept multiple processes of MsWord from opening. Not to mention, but Word wasn't running when my app shut down.
oh, so i open MS Excel once, n i onli close it when i wan to exit my access database? is tat wat u mean?
-
April 9th, 2006, 11:33 PM
#11
Re: Object Variable or with block variable not set
Originally Posted by dglienna
I was working on a mailmerge from within VB. I found that I had the same problem if we ran the same report a second time. It was suggested that I open the connection once (to MsWord) and keep it opened thruout the program.
Not only did that resolve my error, but it kept multiple processes of MsWord from opening. Not to mention, but Word wasn't running when my app shut down.
hi,
sorry if i am requesting for too much, but i tried wat u mentioned, and it still wun work the 2nd time.. isit possible for u to put wat u mean down in codes?? cos i am still relatively new to VB, so i dun realli understand wat u mean by the term connection.. thks
FT
-
April 9th, 2006, 11:35 PM
#12
Re: Object Variable or with block variable not set
Only open Excel once, and Access once. Use one object for each.
Just don't close them, and re-open tham. You can check for errors the first time, and if they open, than you can keep using them with different objects, which you can close, destroy, and re-use.
-
April 10th, 2006, 12:09 AM
#13
Re: Object Variable or with block variable not set
Originally Posted by dglienna
Only open Excel once, and Access once. Use one object for each.
Just don't close them, and re-open tham. You can check for errors the first time, and if they open, than you can keep using them with different objects, which you can close, destroy, and re-use.
but i am using ACCESS to call the spreadsheet in EXCEL...
Dim objExcel As Object
Dim objBook As Object
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open(tbFile)
[b] macro codes here[b]
objExcel.Workbooks.Close
Set objBook = Nothing
objExcel.Quit <---------- u mean dun do this?
Set objExcel = Nothing <---- how abt this?
-
April 10th, 2006, 12:25 AM
#14
Re: Object Variable or with block variable not set
hi, now my program can run smoothly without any errors, but there seems to be someting wrong with my objBook variable, as its supposed to insert a picture based on the forrmula in the cell, but it always inserts the same pic even though the formula in the cell varies, i suspect there is something wrong with the way i initialise or destroy the objBook object? can u advise me?
objBook.ActiveSheet.Pictures.Insert(ActiveCell.FormulaR1C1). _
Select
-
April 10th, 2006, 12:29 AM
#15
Re: Object Variable or with block variable not set
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|