I am exporting some data from Access to Excel, I have used this code before and it worked perfectly. I have 2 tables which I am trying to export, contacts and company details. When clicking export on the contacts form, it works perfectly whereas clicking on the company export button does not work. It is using the exact same code and so I do not see what could be making one work and the other not... does anyone have any ideas? Or suggestions as to what I should check?
Thanks
Rebecca
7 1602
Hi Rebecca. We'd need to see the code you are mentioning which worked before. Without it there is too little detail to even hazard a guess about what might or might not be wrong.
If you could post the code behind your command button etc. it would be appreciated.
Welcome to Bytes!
-Stewart
-
Option Compare Database
-
Option Explicit
-
-
Public Function funexport(strfilter As String, intfunctionid As Integer)
-
On Error GoTo Err_funErrorChecking
-
Dim strObjectName As String
-
Dim strFunctionName As String
-
Dim strselect As String
-
Dim rstdata As DAO.Recordset
-
Dim db As Database
-
Dim strPath As String
-
Dim objXL As Object
-
Dim objActiveWkb As Object
-
Dim objActiveWrksheet As Object
-
Dim intcolumn As Integer
-
Dim rstexport As DAO.Recordset
-
-
Set db = CurrentDb
-
-
Set rstdata = db.OpenRecordset("select * from usystbl000_export where functionid = " & intfunctionid)
-
strFunctionName = rstdata!Function
-
strObjectName = rstdata!ObjectName
-
-
Set rstdata = db.OpenRecordset("select * from usystbl000_exportdetail where functionid = " & intfunctionid)
-
-
rstdata.MoveFirst
-
Do Until rstdata.EOF
-
strselect = strselect & rstdata!FieldName & ", "
-
rstdata.MoveNext
-
Loop
-
strselect = Left(strselect, Len(strselect) - 2)
-
-
If strfilter = "" Then
-
Set rstexport = db.OpenRecordset("SELECT " & strselect & " FROM " & strObjectName)
-
Else
-
Set rstexport = db.OpenRecordset("SELECT " & strselect & " FROM " & strObjectName & " WHERE " & strfilter)
-
End If
-
-
If rstexport.RecordCount > 0 Then
-
-
'browse folder option to create filename
-
strPath = BrowseFolder("Please select a folder for EXPORT")
-
If strPath <> "Cancelled" Then
-
'open excel
-
-
Set objXL = CreateObject("Excel.Application")
-
objXL.Application.Workbooks.Add
-
objXL.Visible = False
-
Set objActiveWkb = objXL.Application.ActiveWorkbook
-
Set objActiveWrksheet = objActiveWkb.Worksheets("Sheet1")
-
-
objXL.ScreenUpdating = False
-
objXL.DisplayAlerts = False
-
-
'loop through header fields and create them
-
'Export details
-
-
With objActiveWrksheet
-
.Range("A1").CopyFromRecordset rstexport
-
rstdata.MoveFirst
-
intcolumn = 1
-
Do Until rstdata.EOF
-
.Columns(intcolumn).NumberFormat = rstdata!Format
-
.Columns(intcolumn).ColumnWidth = rstdata!ColumnWidth
-
'.Columns(intcolumn).HorizontalAlignment = rstdata!Alignment
-
intcolumn = intcolumn + 1
-
rstdata.MoveNext
-
Loop
-
-
End With
-
-
objActiveWkb.SaveAs strPath & "\" & strFunctionName & "_" & Format(Now, "YYYYMMDDhhmmss") & ".xlsx."
-
objActiveWkb.Close SaveChanges:=True
-
objXL.Application.Quit
-
Set objActiveWrksheet = Nothing: Set objActiveWkb = Nothing: Set objXL = Nothing
-
End If
-
End If
-
-
Exit_funErrorChecking:
-
Exit Function
-
-
Err_funErrorChecking:
-
Call funErrorChecking(Err.Description, Err.Number, Application.CurrentObjectName, "funexport")
-
Resume Exit_funErrorChecking
-
Resume
-
End Function
-
Well, there's no obvious problem with the code concerned that I can see from checking the content.
Given that we don't have your application to hand and you do, you'll need to do some digging to find out exactly what happens when you click on the Company export button (as we need to know much more about what is happening than 'it doesn't work' to be able to give you suitable advice!).
You'll need to approach this from a debugging perspective and set a break point in the On Click event handler for that button, then use the F8 key to single-step through your code to find out what exactly is being done.
If you need some help on what debugging is about we have an Insights article on Debugging in VBA which may be of use, linked here.
-Stewart
I have done that and tried to work it out myself, basically what happens is an error appears 'too few parameters'. The code errors on this line: Set rstexport = db.OpenRecordset("SELECT " & strselect & " FROM " & strObjectName)
Presumeably this means an error with the specified recordset, I have looked at the recordset and cannot see what could be wrong with it. The recordset consists of the following fields if it helps: CompanyName, CompanyAddress, TownCity, Postcode, ContactNumber1, WebsiteAddress, VATNumber, CompanyContact, CompanyType
Don't worry now I've found the answer
Hi Rebecca. For interest, what was wrong? The error message you mention normally arises if a field mentioned in the WHERE clause of the SQL statement does not correspond to a field that the SQL interpreter can access. This can happen if a field is renamed or removed, for example.
By the way, I notice that lines 20 and 24 in the code you posted duplicate each other; it is not a good idea to open a recordset and then re-open the same one for the same object variable. I'd guess that line 24 can be deleted, but you'd need to confirm this by testing. Line 20 cannot be, as the recordset is referred to immediately after.
-Stewart
Hi, If you read the table name opened in line 20 and 24 they are actually two different tables, export and export detail as they have different information within them. Yes the problem was that a field name in the table did not correspond to a field name in the query (it was quite a subtle difference so took me a while to notice).
Thanks for trying to help anyway!
Rebecca
Sign in to post your reply or Sign up for a free account.
Similar topics
by: ImraneA |
last post by:
Hi there
Have a database, where front-end interface allows user to select a ms
access database. From there, standard tables are linked. Routine,
that creates a spreadsheet, for each table a...
|
by: tkaleb |
last post by:
I have to create output file in a text, MS Access, MS Excel and .dbf format
from C# Win/ADO.NET application. Data are collected in DataSet and there is
no problem to make text file. However, I have...
|
by: Gill Smith |
last post by:
My Access Key is not working in the below code.
<TD align="left" width="25%"><A href="javascript:OpenSearch()"
accesskey="L"><IMG src="Images/MySeach.gif" align="left"
border="0"></A></TD>
Is...
|
by: James Wong |
last post by:
Dear all,
I have an old VB6 application which can create and access Excel object. The
basic definition statements are as follows:
Dim appExcel As Object
Dim wkb1 As Excel.Workbook
Dim wks1...
|
by: franjorge |
last post by:
Hi,
I have created two stored procedures via VB using this code:
sql = "CREATE PROC " & nombre_proc & " AS SELECT *" & _
" From MBM_PUNTOS_SCE_SIN_COINCIDIR_SIEGE_FALTA_PM_NE_" & mes & _
"...
|
by: TechnoPup |
last post by:
Greetings,
I am very new to working with databases, and I am not sure how to go about structuring the query I need. What I have is an Access database with approx. 400,000 records in 5 fields. ...
|
by: Keith Wilby |
last post by:
How controllable from Access VBA is Excel? I'm currently using automation
to dump 2 columns of data into an Excel spreadsheet so that the end user can
create a line graph based on it. Could the...
|
by: pixie |
last post by:
Hi,
I have some code that I have been using for more than a year. Excel VBA. Today it stopped working. I have no idea why. Here is the code: Range ("E1").Select
If IsEmpty(ActiveCell) Then...
|
by: jayce |
last post by:
Hi
I am using an ADODB connection to link up an Access database to my core spreadsheet tool. I have created a loop to repeatedly:
- download data from Access
- analyse it in Excel, with data...
|
by: Nadirsha Muhammed |
last post by:
How to access excel function wizard through C#
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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,...
|
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,...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| | |