appending an Excel worksheet to an existing Access database

How can I with the code below when the database opens and table2 already exist delete it and make a new table2?

On Error GoTo errHandler
Dim mExcelFile As String
Dim mAccessFile As String
Dim mWorkSheet As String
Dim mTableName As String
Dim mDataBase As Database
mExcelFile = App.Path & "Book1.xls"
mAccessFile = App.Path & "Db2.mdb"

mWorkSheet = "Sheet1"
mTableName = "Table2"
' Below you may use "Excel 7.0" or 8.0 depending on your installable ISAM.
Set mDataBase = OpenDatabase(mExcelFile, True, False, "Excel 5.0")
mDataBase.Execute "Select * into [;database=" & mAccessFile & "]." & mTableName & _
" FROM [" & mWorkSheet & "$]"
MsgBox "Done. Use Access to view " & mTableName
Exit Sub
errHandler:
If Err.Number = 3010 Then
MsgBox mTableName & " already exist." & vbCrLf & _
"Delete " & mTableName & " first or use another table name."
Else
MsgBox Err.Number & " " & Err.Description
End If


Post your answer or comment

comments powered by Disqus
Hi, All,


Appending an Excel file to an Access table
------------------------------------------

Just now I tried to convert an Excel file (let's call it source_1.xls) into an Access database (Access 2000). The first line of the Excel file represents the field names in the database.

I opened the Access program, clicked on 'Blank Access database', named the desired Access file, and saved it as fred.mdb

I clicked file | get external data | import |
and selected source_1.xls, then clicked import

The following unhelpful since unspecific error msg came up:
"Wizard unable to access information in file xyz. Check that file exists and is in correct format."

Can anyone please tell me what to do?

--------------

A puzzling aspect of this affair is the following:

A couple of weeks ago when first trying to make this conversion from Excel into an empty database I did succeed, but unfortunately it was so easy that I did not see the need for writing down what exactly I did, and now I cannot reproduce it.

But the files left over from that experiment are puzzling and may shed light on my current problems.

For safety reasons I made, at that time, a copy of file source_1.xls (let's call it source_2.xls) and renamed it. Looking at the two source files I can see no difference between them. Both have the same number of records and fields. Both have the field names in the first row.

Now the miracle:
1 source_2.xls (88 kb) is almost exactly twice as large as source_1.xls (43 kb).
2 source_2.xls imports into Access OK without the above error msg.

What on earth could I have done to the Excel file to double it in size and to make it acceptable to Access for import?

Could it have something to do with delimiters that I have forgotten?

-----------------------------------

Now to the overall context of this problem.

I regularly receive a fairly small Excel file (always in the same format) with addresses and I have to append these addresses to my Access database without fuss and error. Esp. I must avoid records becoming scrambled up (e.g. wrong name joined with wrong address by risky pasting into tables).

The file mentioned above is the first Excel file that arrived. I converted it into Access (but see problems mentioned above), then deleted a number of fields and added a number of others.

I took care not to alter the names of the fields which the Excel table and the Access table are intended to have in common.

In future (once or twice a month) I want to import the future Excel files into the existing database, by appending them. I expect Access to import, for each record, the matching fields in the Excel file, and ignore the non-matching fields (i.e. those which exist only in Excel or only in Access).

btw: It is ***not*** practicable for me to expand the Excel table manually to match the Access table.

Question: I there a utility in Access which could accomplish this.

Or: Is there a stand-alone utility (not too expensive) which I could use to convert the Excel table into a common format from which I can easily import it into Access in the way described.

What else can I do to accomplish what I need?

I have a voluminous book, 'Running MS Access 2000' by John Viescas, but do not know where to look for what I want (if it exists). Please refer me to a page if the answer is there?

If there is a Tutorial on the Internet which describes my specific problem, please refer me to it.


Many thanks for your help.

Adrian

Hello Access experts,

I have been touring around the web looking for some decent answer to my problem until i bumped into this cool website that might help me.

The attached file contains an excel spreadsheet that has information on it that i want to import to my access database specifically on column "C". Importing a file from excel was easy when the data you want to get are arranged in a Row but in this case it is arranged in a Column.

Can anyone help me with an Access Macro that can Automate the import?

1. Command button allows me to pick the excel file and automatically imports to my access database and everytime i import another file, it appends to the database.

This is really challenging and hope anyone can help me

This code works perfectly to append one excel file but I need help to amend it so it can append one file after another. Any ideas?

Quote: Option Compare Database
Option Explicit

Const constColumns = 53
Const constFirstLine = 17

' Tracker Fields
Const constRef = 1
Const constDate_of_Reg = constRef + 1
Const constCompany = constRef + 2
Const constContractor = constRef + 3
Const constDirectorate = constRef + 4
Const constProgram_Project = constRef + 5
Const constContract_Descr = constRef + 6
Const constSupply_Service_or_Works = constRef + 7
Const constForm_of_Contract = constRef + 8
Const constTotal_Value = constForm_of_Contract + 1
Const constContract_and_variation_no = constForm_of_Contract + 3
Const constAgreed_Award_Date = constTotal_Value + 4
Const constPeriod_of_Award_Date = constTotal_Value + 5
Const constCustomer_GM = constAgreed_Award_Date + 5
Const constCustomer_BM = constCustomer_GM + 1
Const constGM_Area = constCustomer_BM + 1
Const constGM = constCustomer_BM + 3
Const constBM = constCustomer_BM + 4
Const constProc_Contact = constCustomer_BM + 5
Const constLegal_Contact = constCustomer_BM + 6
Const constCurrent_Status = constLegal_Contact + 1
Const constReason_Code = constLegal_Contact + 3
Const constNext_Management_Steps = constLegal_Contact + 4
Const constActual_Contract_date = constNext_Management_Steps + 11
Const constPeriod_Contract_date = constNext_Management_Steps + 12
Const constStatus = constNext_Management_Steps + 14

Dim appExcel As Excel.Application
Dim xlBook As Object
Dim xlSheet As Object

Dim db As Database
Dim rs As Recordset

Public Sub basControl_Import()

' controlling subroutine for import Excel information
If basOpenRecordset() Then
If basOpenExcel _
("Z: ProcurementPerformanceContracts.xls") _
Then
End If
End If

End Sub

Public Function basOpenRecordset() As Boolean

On Error GoTo basOpenRecordset_err

' Empty the table used to hold the imported information
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblLive_Tracker"
DoCmd.SetWarnings True

' open the table in preperation of importing the data
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblLive_Tracker", DB_OPEN_DYNASET)

basOpenRecordset_ok:
On Error Resume Next
basOpenRecordset = True
Exit Function

basOpenRecordset_err:
MsgBox "Error clearing and then opening tblLive Tracker : " & Error(Err)
basOpenRecordset = False
Resume basOpenRecordset_ok


End Function

Public Function basOpenExcel(strFile As String) As Boolean


Dim intI As Integer
Dim intJ As Integer

'Debug.Print "Start: " & Now()
' open Excel as an object
On Error GoTo basOpenExcel_err
Set appExcel = CreateObject("Excel.Application")

' Open the workbook
Set xlBook = appExcel.Workbooks.Open(strFile)
'Open the first worksheet
Set xlSheet = xlBook.Sheets("TRACKER")

' Set the start point
intI = constFirstLine

' Keep reading lines until the first cell on a line contains an empty string
While Len(xlSheet.Cells(intI, 1)) 0
'Debug.Print intI
' Check to see if this line is a recognised data line
If basDataline(intI) Then
' Pull in the fields and add to the recordset
basAddLine (intI)
End If
' Next row
intI = intI + 1
Wend

basOpenExcel = True

basOpenExcel_ok:
' Exit routine
On Error Resume Next
' Close the workbook (no changes to save)
xlBook.Close SaveChanges:=False
' Quit Excel
appExcel.Quit
'Debug.Print "Stop: " & Now()
Exit Function

basOpenExcel_err:
' Error handling
MsgBox "Error opening Excel workbook " & strFile & " worksheet Master Tracker : " & Error(Err)
basOpenExcel = False
Resume basOpenExcel_ok


End Function

Public Function basDataline(intRow As Integer) As Boolean

' Specific test to see if this row has data that we want to import
If xlSheet.Cells(intRow, 1) "Overall Result" Then
basDataline = True
Else
basDataline = False
End If

End Function

Public Sub basAddLine(intLine As Integer)
'Dim tmpDate_12Months As Date
'Dim tmpDate_09Months As Date

DoCmd.SetWarnings True
On Error GoTo basAddline_err

' Create a row in the recordset
rs.AddNew
' Update the fields with the specific data, each field is a specific type)
rs![Ref] = basGetTextField(intLine, constRef)
rs![Date_of_Reg] = basGetDateField(intLine, constDate_of_Reg)
rs![Contractor] = basGetTextField(intLine, constContractor)
rs![Company] = basGetTextField(intLine, constCompany)
rs![Directorate] = basGetTextField(intLine, constDirectorate)
rs![Program_Project] = basGetTextField(intLine, constProgram_Project)
rs![Contract_Descr] = basGetTextField(intLine, constContract_Descr)
rs![Supply_Service_or_Works] = basGetTextField(intLine, constSupply_Service_or_Works)
rs![Form_of_Contract] = basGetTextField(intLine, constForm_of_Contract)
rs![Total_Value] = basGetNumericField(intLine, constTotal_Value)
rs![Agreed_Award_Date] = basGetDateField(intLine, constAgreed_Award_Date)
rs![Period_of_Agreed_date] = basGetTextField(intLine, constPeriod_of_Award_Date)
rs![Customer_GM] = basGetTextField(intLine, constCustomer_GM)
rs![Customer_BM] = basGetTextField(intLine, constCustomer_BM)
rs![Proc_GM] = basGetTextField(intLine, constGM)
rs![Proc_BM] = basGetTextField(intLine, constBM)
rs![Proc_Contact] = basGetTextField(intLine, constProc_Contact)
rs![Legal_Contact] = basGetTextField(intLine, constLegal_Contact)
rs![Current_Status] = basGetTextField(intLine, constCurrent_Status)
rs![Reason_Code] = basGetTextField(intLine, constReason_Code)
rs![Next_Managment_Steps] = basGetTextField(intLine, constNext_Management_Steps)
rs![Actual_Contract_date] = basGetDateField(intLine, constActual_Contract_date)
rs![Period_contract_date] = basGetTextField(intLine, constPeriod_Contract_date)
rs![Status] = basGetTextField(intLine, constStatus)
rs![Contract_and_variation_no] = basGetTextField(intLine, constContract_and_variation_no)
rs![Proc_Area] = basGetTextField(intLine, constGM_Area)


'tmpDate_12Months = DateAdd("m", -12, Date)
'tmpDate_09Months = DateAdd("m", -9, Date)
'rs![agencyLengthOfService] = IIf(rs![agencyDES] < tmpDate_12Months, 12, IIf(rs!agencyDES < tmpDate_09Months, 9, 0))

rs.Update

DoCmd.SetWarnings False

basAddLine_ok:
Exit Sub

basAddline_err:
MsgBox "Error appending line to tblLive Tracker line [" & intLine & "] : " & Error(Err)
Resume basAddLine_ok

End Sub


Public Function basGetTextField(intLine, intCol) As String

' Text field
On Error GoTo basGetTextField_err

basGetTextField = xlSheet.Cells(intLine, intCol)

basGetTextField_ok:
On Error Resume Next
Exit Function

basGetTextField_err:

MsgBox "Error importing text from row " & intLine & " column " & intCol & " " & xlSheet.Cells(intLine, intCol)
basGetTextField = ""
Resume basGetTextField_ok

End Function

Public Function basGetNumericField(intLine, intCol) As Long

' Numeric field
On Error GoTo basGetNumericField_err

basGetNumericField = xlSheet.Cells(intLine, intCol)

basGetNumericField_ok:
On Error Resume Next
Exit Function

basGetNumericField_err:
MsgBox "Error importing numeric from row " & intLine & " column " & intCol & " " & xlSheet.Cells(intLine, intCol)
basGetNumericField = 0
Resume basGetNumericField_ok

End Function

Public Function basGetDateField(intLine, intCol) As Date
' Date field
On Error GoTo basGetdateField_err

basGetDateField = xlSheet.Cells(intLine, intCol)

basGetdateField_ok:
On Error Resume Next
Exit Function

basGetdateField_err:
'MsgBox "Error importing date from row " & intLine & " column " & intCol & " " & xlSheet.Cells(intLine, intCol)
basGetDateField = #1/1/1900#
Resume basGetdateField_ok

End Function

Hi

I'm sure this is a very straightforward question, but I can't think of the answer. I am using a macro to import an Excel spreadsheet to an existing table in an Access 97 database (yes, we still use 97 in the office!). If I import the table manually, there is no problem and when I open the table, there are the expected number of rows (in this case 25724). However, if I use the macro, only 17413 rows are imported. I've tried removing the PK just in case (but as it works when I import manually, I didn't expect that to help (and it didn't). I've checked the imported data against the whole spreadsheet, and I can't see anything apparent in the rows that have failed to import. Can anyone help?

(I've reduced the macro to just that one function so I know the problem is somewhere in the TransferSpreadsheet action).

Many thanks

Ed

Hello.
I need help.
I am a new access user and am trying to create a simple claims database.
I have created all the tables that I need so far , but I need one more table that will house the answers to an excel macro.

This Macro is a series of questions that a customer is asked at enrollment time and the answers determine if the customer is accepted or not accepted in the program.

I tried linking the worksheet to my database what I got was the macro programming rather than the answers and final decision.
What I want ideally is only the answers to the questions in this table, I don't need to see all the options available in the macro.
I don't know if my explanation makes sense , but any help is greatly appreciated.

Thank you

Automate the transfer of multiple excel files to one Access table.

Hello. I'm new to this forum and whilst not a complete newbie to Access or excel or basic VBA programming i didn't have a clue how to do this but i did find some code when searching for answers through google
However, i want to do two things in addition to what the code below does. I want to transfer these files but my records start on a certain row in excel. To Be exact row 17. I also want to be able move the imported excel files into an exported folder and exporting

I hope someone can give me some advise on how to do this

Thanks

Here is the code i found




Option Compare Database
Option Explicit

Public Function ImportExcelMultipleFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has Field names
blnHasFieldNames = False

' Replace C:Documents with the real path to the folder that
' contains the EXCEL files
strPath = "C:UsersKevinGoogle DriveATOSATOS Reports"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "ATOSData"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Function

I have two queries which are data sources for an Excel worksheet...

My problem: If I try to refresh the Excel file when the database is OPEN, I get an error msg saying "The database has been placed in a locked state...blah blah"

[I'm running Access 02 - never had this problem in previous versions]

VBA (command button) opens the Excel file externally through Access after going through the necessary computation.

As an alternative, I could create a (new) workbook object each time through Access, but then I cannot use the graphs that I want to build and refresh in my Excel file.

Any suggestions?

Hi everyone,

I have an error to import an excel file into ms access 2.0(1993). I am developig a vb6 application.Back end is ms access 2.0. 5000 records are saved in the excel sheet. I need to import to access 2.0. I am using ms access 2003. I could not edit or modify the 2.0 database in the ms access 2003. And also, I could not import the excel file into ms access 2.0.

Please guide me.

Thanks in advance,

J. Mohan

Hi I am trying to add more data points to an existing system that is importing data from Rockwell PLC Rslogix5000 to Microsoft Access Database, but I could not find out what type of communication link between the MS Access Database and the PLC. Could anyone tell me how to find that?

How to embed a Microsoft excel file into Microsoft access database.

I came across this strange error for the first time. I am trying to export all my access database objects (Forms, Queries, Reports, Modules) to another access database file. While some of the object will export successfully, about one or two forms and modules will not and give the following error.

File not found: 'C:UserssandaaAppDataLocalTempVB31D5.tmp' or
File not found: 'C:UserssandaaAppDataLocalTempVB4A76.tmp'

depending on the object and whenever I click OK button I get a further error report saying:

"Name COnflicts with existing module, project, or object library."

Does anybody know how I can resolve this problem?

Thanks.

First off, its been a long time since I have been here (since sometime in 2003 LOL), partly because I didn't have to create a new db in a while. Now, I have been handed a project and come across something new.

OK, here is what I want to do. I would like to be able to dump data from an Excel file into existing Access tables (two to be more specific). Now, I also have relating tables that add data into certain fields. I tried using a query to try to group data ad hopeful able to dump data through the query. I noticed that I couldn't import data through a query. Just for kicks, I tried to pull data through table and noticed that I can only create a new table, not able to update and existing table.

How would I go about performing this task? I have never attempted anything in the past.

Thank you in advance for your considered help,

Steve

Hi,
I was wondering if there was a way for a report to be automatically saved as an excel file. Also, the Access database is stored on the company's server so would you be able to choose the path where the excel file would be stored?

Cheers,
Ben

I have mastered just beyond the basics of VBA and now am trying to learn a little VB. Just for learning purposes I am trying to recreate one of my completed Access database in Visual Basic. I downloaded Visual Studio 2008 Express Edition. I realize that in VB you cannot have bound forms as you can in Access, so I want to connect my VB project to my current Access database tables. I theoretically know how I would add, access, modify, delete, etc. my data without having those bound forms. I would use (and am only assuming that this would be the right way) SQL statements and recordsets to manipulate data and populate fields, etc. So my problem is, how do I originally connect to my Access database? I found the option to "connect to Database..." under the Tools menu, and successfully connected to my Access database, but now have no clue on how to use/reference it in my code. In the left pane of my Visual Basic window I now see the Database Explorer window which lists all my tables, fields in those tables, and even what appears to be my saved queries and functions. How do I now use this in my code?
For a simple example lets just say I wanted to make a simple form that shows the Name Address and Phone number for records in my Customers table from my Access database. I would understand the code behind setting the values of the fields/controls, and could figure out the code for navigating to the previous/next records, but its just that initial connection and opening of a recordset or referencing a table in an SQL statement that I cannot figure out. BTW, can I even use recordsets and SQL in VB as I can in VBA?

I am tryin very hard to link an Access table form one Access Database to another Access Database. Does anyone have any idea how to do this?

I am trying to do this by linking to the table through the Access interface. I click "link tables", then select an "access database type". I then "map a network drive" from the tools menu, then select "web folder or FTP site". I enter in my IP address e.g. http://160.123.121.213 and it says that this is not valid.

PLEASE HELP ME!!

I linked an excel spreadsheet to my access database and created a report that uses that spreadsheet. The problem that I'm having is that if I have the excel spreadsheet open and another user on a different computer opens up the access report, the linked excel spreadsheet will open up as well. How do I stop this from happening?

How do I import an excel file to a table with out it being Linked? I have a data base that works off of a table that is currently linked to a excel file. This file needs to be updated through out the day with all new data each time. But if i have the database open I cannot update the excel file. Is there a way I can just pull the data from the excel file?

Hi Group
I am trying to add an outlook contact from my access database. This code is attached to the OnClick event of a command button and works fine….it adds the contact and enters in the full name. Problems start happening when I try to add additional fields within the WITH statement. I get an Error 438 message – Object doesn’t support this property or method. Does anyone have any ideas???
Thanks for your input
Private Sub AddOLContact_Click()
On Error GoTo AddAppt_Err
'Save record first to be sure required fields are filled.
DoCmd.RunCommand acCmdSaveRecord
'Exit the procedure if appointment has been added to Outlook
If Me!AddedToOutlook = True Then
MsgBox "This appointment already added to Microsoft Outlook"
Exit Sub
'Add a new appointment
Else
Dim outobj As Outlook.Application
Dim outappot As Outlook.ContactItem
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olContactItem)
With outappt
FullName = Me.FName & " " & Me.LName

Save
End With
End If
'Release the Outlook object variable
Set outobj = Nothing
'Set the AddedToOutlook flag, save the record, display a message
Me!AddedToOutlook = True
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Contact Added!"
Exit Sub
AddAppt_Err:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Exit Sub

End Sub

I have a macro within my access database that exports data to an excel worksheet and this works great. However i now want to add data to an "existing" excel worksheet ie one that already has data in it from a previous export from access. Can this be done, if so can anyone point me in the right direction.

Hi,

I am trying to use VBA to open up an access database, enter values into a record from a series of defined names into a temporary table, then eventually run an append/update query to copy this record into the main table.

At the moment I am struggling with the first step, which is giving me the error:


runtime error 214721783(80040e4d) Authentication failed

This is the code:

	Code:
	Sub SelectDB()
'Select Database file location
PEMdb  = Application.GetOpenFilename(FileFilter:="Access Files (*.mdb),  *.mdb", Title:="Please select the location of the
Personnel &  Equipment Management Database")
If PEMdb = False Then
' They pressed Cancel
MsgBox "The operation was cancelled", vbInformation, "Import Cancelled"

Exit Sub
Else
Workbooks.Open Filename:=PEMdb
End If
End Sub

Sub Validate_Import_1()
'Validate Data before importing

End Sub


Sub Import_Stage_1()
'Export data from excel into Access DB

Call SelectDB

Dim cn As ADODB.Connection
Dim cmPerDet As ADODB.Command


Set cn = New ADODB.Connection
Set cmPerDet = New ADODB.Command

With cn
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & PEMdb & "; Persist Security Info=False"
.Open
End With


With cmPerDet
.CommandText  = "Insert into tbl_TEMP_Personnel_Details(Employee ID, First Name, Last  Name) Values ('" +
Range("Employee_ID").Text + "','" +  Range("First_Name").Text + "','" + Range("Last_Name").Text + "')"
.ActiveConnection = cn

End With


End Sub


The database will eventually be split with the back-end on a network drive, but at the moment I am getting this error even when it's located on my own desktop.

Any suggestions? It's the .Open line that is highlighted by the debugger. I have tried both changing selectDB() from a sub to a function and removing it completely (replacing PEMdb with a path & filename) but to no avail...

Thanks,

Duncan

Afternoon all,

I am new to this forum so please forgive me if I have posted in the wrong section,

I am trying to use Excel VB to write to an access database which is working brilliantly sometimes...

In Excel on Sheet 1 I have the data to be written to the access database,

In Excel on Sheet 2 I have created a data connection that refreshes the full table (that I am writing to from Sheet 1 to the access database)

The below codes works perfectly until I refresh the data connection on sheet 2, then when I next try and run the code below I get the following error:

Run-time error ‘3251’
Current Recordset does not support updating. This may be a limitation of the provider, or the selected locktype

I have looked up and down the web to no avail, the only way I can get around it is to close the Excel workbook and re-open it without refreshing the data connection and then trying again and it works.

If anybody has any idea I would be most appreciative!


Here is the code:

Sub SingleLineTransfer()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
On Error GoTo Errhandler



Dim cn As ADODB.Connection, rs As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersJamieDesktopGroup Commercial SystemGroup Commercial System Database.accdb;Persist Security Info=False;"

' open a recordset
Set rs = New ADODB.Recordset
rs.Open "DB_Main", cn, adOpenKeyset, adLockPessimistic, adCmdTable

With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Stage and Gate Ref") = Range("A" & 2).Value
.Fields("Project Name") = Range("B" & 2).Value
.Fields("# Of Products") = Range("C" & 2).Value
.Fields("Company Submission") = Range("D" & 2).Value
.Fields("Submitted By") = Range("E" & 2).Value
.Fields("Workstation Name") = Range("F" & 2).Value
.Fields("Domain") = Range("G" & 2).Value
.Fields("Submission Time and Date") = Range("H" & 2).Value
.Fields("Project Leader") = Range("I" & 2).Value



.Fields("Req Output Currency") = Range("M" & 2).Value
' add more fields if necessary...
.Update ' stores the new record
End With
' rs.Close
'Set rs = Nothing
' cn.Close
' Set cn = Nothing
GoTo Finish

Errhandler:
If Err.Number = 3251 Then GoTo ConnectionError
If Err.Number = -2147217887 Then GoTo DuplicateError
ConnectionError:
MsgBox "Connection Error, Please Contact Your System Administrator", vbCritical, "System Error"
GoTo Finish
DuplicateError:
MsgBox "That Stage and Gate Reference Is Already Being Utilised, Please Try Again", vbCritical, "System Restriction"
Finish:
End Sub


Thanks in advance guys!

Someone helped me with this code which saves the query results to an excel worksheet every time the query is run. (same worksheet every time it is run) I'd like to know how this code can be changed so that when the query is run it sends the query results on a new worksheet each time in the workbook. I would like to have the name of each sheet to be the date that the query was run that created that worksheet. Can someone show me how to do this?

Thanks



	Code:
	Sub btnAddNewDataToTbl1XLS_Click()
'************* Code Start *****************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish

'Copy records to first 20000 rows
'in an existing Excel Workbook and worksheet
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "DataSheet"
Const conWKB_NAME = "C:Tbl1XLS.xls"
  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset("query2", dbOpenSnapshot)
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    intLastCol = objSht.UsedRange.Columns.Count
    With objSht
'      .Range(.Cells(20, 1), .Cells(conMAX_ROWS, _
'          intLastCol)).ClearContents
'      .Range(.Cells(20, 20), _
'        .Cells(1, rs.Fields.Count)).Font.Bold = True

        Dim lngWhichLineToStart As Long
        'Take the line count of Tbl1XLS.xls
        lngWhichLineToStart = DLookup("[LineCount]", "tblLineCountToXLS") + 2   ' 2 is for 2 lines below
        'Now export to excel
        .Range("A" & lngWhichLineToStart).CopyFromRecordset rs
        'You need to know how many lines your query2 exported which is equal to record count
        Dim lngExportedLines As Long
        lngExportedLines = rs.RecordCount + 2
        'Now you need to change the LineCount value for the next export
        DoCmd.RunSQL "UPDATE tblLineCountToXLS SET tblLineCountToXLS.LineCount = [LineCount]+ " & lngExportedLines & ";"

    End With
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub



Can someone tell me if it is possible to import or link an existing macro in an Excel file to an Access 2010 database? If so, any direction on how to go about accomplishing this?

Thanks kindly.
sren

hi guys,

i am creating a report in an access databse. in this report i need to show data from a spreadsheet that is automatically updating every day.

how can i transfer this data into my databse in order to show it in the report. is there any way that i can populate a table with the data from the spreadsheet?

ps. i am a beginner in access databases and i would appreciate if you can help me with this.

Cheers


Not finding an answer? Try a Google search.