Hello
I am working on a project to export data from a query into an Excel file. I have a form with a button on it and the button when clicked runs a function that is supposed to run the query and store it into a recordset, create a new Excel file, dump the contents into the Excel file and do some cell formatting on it to make it look decent.
I have put it together piece by piece, and it works fine up until I try to add cell formatting. I get the error "Object variable or With block variable not set" in my code. I first got "Method 'Range' of object '_Global' failed" on the line when I selected a range of cells, but only after it ran successfully once. Looking around online it seemed that I needed to set my objects that I created to Nothing, so that Access would not have them held up anymore. I did that, or at least tried to set them to nothing, but it still doesn't work. With the code I have now, it gives the the Object variable or with block error, at line 59 of my code. I'm not sure if it is still the same problem of Access still not completely letting go from the first time the function ran or if it's something else. Does anybody have any suggestions?
thanks
Josh - Private Sub Command0_Click()
-
'create and set connection to current database
-
Dim conn As ADODB.Connection
-
Set conn = CurrentProject.Connection
-
-
'create and connect new recordset to the new connection
-
Dim myRecordSet As New ADODB.Recordset
-
myRecordSet.ActiveConnection = conn
-
-
'pop up input box to retrieve Officer Badge
-
Dim strInput As String
-
strInput = InputBox("Enter Officer Badge", "Officer Badge")
-
-
'assign the SQL query that pulls all records matching SOBadge to strSQL
-
Dim strSQL As String
-
strSQL = "SELECT tblDRIVEINPASS.SOBadge, tblDRIVEINPASS.* FROM tblDRIVEINPASS "
-
strSQL = strSQL + "WHERE ((tblDRIVEINPASS.SOBadge = '" & strInput & "'));"
-
-
'populate the recordset with results from the query
-
myRecordSet.Open strSQL
-
-
'create excel application to push query results to
-
Dim xlApp As Excel.Application
-
Dim xlWorkbook As Excel.Workbook
-
Dim xlsht As Excel.Worksheet
-
Set xlApp = CreateObject("Excel.Application")
-
Set xlWorkbook = xlApp.Workbooks.Add
-
xlApp.Visible = True
-
-
Dim intRows As Integer
-
Dim qryResultsCols As Integer
-
intRows = 2
-
-
xlApp.Sheets("Sheet1").Select
-
-
'fill in column headings
-
For intCols = 1 To 13 Step 1
-
xlApp.Cells(1, intCols).Value = myRecordSet.Fields(intCols - 1).Name
-
Next intCols
-
-
'fill in records from database
-
If (myRecordSet.EOF And myRecordSet.BOF) Then
-
'no records to pull - do nothing
-
Else
-
myRecordSet.MoveFirst
-
xlApp.Cells(1, 1).Value = "Officer Badge"
-
-
-
Do Until (myRecordSet.EOF)
-
For qryResultsCols = 0 To 12 Step 1
-
xlApp.Cells(intRows, qryResultsCols + 1).Value = myRecordSet.Fields(qryResultsCols).Value
-
Next qryResultsCols
-
-
myRecordSet.MoveNext
-
intRows = intRows + 1
-
Loop
-
End If
-
-
xlApp.ActiveSheet.Range("A1:M1").Select
-
With Selection.Borders(xlEdgeLeft)
-
.LineStyle = xlContinuous
-
.Weight = xlThick
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Borders(xlEdgeTop)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Borders(xlEdgeBottom)
-
.LineStyle = xlContinuous
-
.Weight = xlThick
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Borders(xlEdgeRight)
-
.LineStyle = xlContinuous
-
.Weight = xlThick
-
.ColorIndex = xlAutomatic
-
End With
-
With Selection.Font
-
.Name = "GE Inspira Medium"
-
.Size = 12
-
.Bold = True
-
End With
-
-
-
Set xlWorkbook = Nothing
-
Set xlSheet = Nothing
-
Set xlApp = Nothing
-
-
myRecordSet.Close
-
Set myRecordSet = Nothing
-
-
End Sub
2 4663
Hi. Problem is the use of the Selection method, which is available in Excel but not reliably when Access is using Excel as an automation server. You don't need it, and it is considerably faster to refer directly to ranges rather than select them first. Add an Excel range object variable object to your code and change the Select references in lines 60 and beyond as follows: - Dim Cellrange as Excel.Range
-
…
-
Set CellRange = xlApp.ActiveSheet.Range("A1:M1")
-
With CellRange.Borders(xlEdgeLeft)
-
.LineStyle = xlContinuous
-
.Weight = xlThick
-
.ColorIndex = xlAutomatic
-
End With
-
With CellRange.Borders(xlEdgeTop)
-
.LineStyle = xlContinuous
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
With CellRange.Borders(xlEdgeBottom)
-
.LineStyle = xlContinuous
-
.Weight = xlThick
-
.ColorIndex = xlAutomatic
-
End With
-
With CellRange.Borders(xlEdgeRight)
-
.LineStyle = xlContinuous
-
.Weight = xlThick
-
.ColorIndex = xlAutomatic
-
End With
-
With CellRange.Font
-
.Name = "GE Inspira Medium"
-
.Size = 12
-
.Bold = True
-
End With
you may also find the discussion in the later part of the following post relevant for more detailed explanations: http://www.thescripts.com/forum/thread784045.html.
-Stewart
Awesome, that seems to have helped. Thanks for your suggestion
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Hung Jung Lu |
last post by:
Hi,
Does anybody know where this term comes from?
"First-class object" means "something passable as an argument in a
function call", but I fail to see the connection with "object class"
or with "first-class airplane ticket". I just find the name a bit
strange. Also, if there are first-class objects, what would the
second-class objects or economy/tourist class objects be? :)
|
by: Michael McCracken |
last post by:
Hi, I have a problem with unittest.TestCase that I could really use
some help with.
I have a class File in module File. The important thing about File for
this discussion is that it's simple - no pool of objects are involved,
and subsequent invocations of File.File('filename') should return
distinct objects (and indeed, they do on the command line). Also,
__repr__ prints out the value of id(self) for File, so I can tell
what's going on...
|
by: Lauren Quantrell |
last post by:
I get the following error:
"Object variable or Width block variable not set error"
trying to run this in my Access2000 .ADP database:
CurrentDb.Properties.Append
CurrentDb.CreateProperty(myPropertyName, dbLong, myValue)
Any ideas why and how to fix would be appreciated.
lq
|
by: Mark Neilson |
last post by:
1. What is the best way to make a single instance of my top level class
(DLL) internally available to all other members of the assembly? The top
level object is where all other access is made in to the program. Where and
how do I declare and initialise this object?
For instance, it would have property implementation like this:
//*******************************************
public MyClass1 Class1
{
|
by: Neo Geshel |
last post by:
I am trying to deal with an image in code-behind. I consistently get the
following error:
Server Error in '/' Application.
Object variable or With block variable not set.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object variable...
| |
by: petermichaux |
last post by:
Hi,
I have been using the following line of code to create an object called
"Serious" if it doesn't already exist.
if (Serious == null) {var Serious = {};}
This works in the scripts I use it but but www.jslint.com is not happy
with me.
|
by: Richard Hollenbeck |
last post by:
I've marked the line in this subroutine where I've been getting this error.
It may be something stupid but I've been staring at this error trying to fix
it for over an hour. I'm pretty sure the table and field names and controls
are all named correctly, and the control referred to in the errant code is
open, and it has data in it.
Private Sub cmdAddIngredientToRecipe_Click()
' Get RecipeID for future action query
Dim recipeID As Long
|
by: abhijmenbumca07 |
last post by:
Object variable or With block variable not set.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object variable or With block variable not set.
Source Error:
Line 305: Me.txt_amount.Text = mat(0)
Line 306: ...
|
by: BrianAdev |
last post by:
Why It throw an error of <Object variable or With block variable not set>
set objAccess = GetObject(strMdbPath)
objAccess.DoCmd.SetWarning False
objAccess.DoCmd.OpenReport strReportName, 0
'It is fine until now
objAccess.Application.Run "Function",Arg1,Arg2
'Throw the arror
<Object variable or With block variable not set>
|
by: Newbie19 |
last post by:
I'm trying to get a list of all subfolders in a folder on a share drive, but I keep on getting this error message:
Object variable or With block variable not set.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object variable or With block...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |