Calling An Excel Macro From An Access Database That Refers To That Database
hi
i have a macro in excel that inserts some values into a table in a database in access, but i want to make it so i dont have to touch the excel file, instead i have a module in the access file that calls the sub in the excel file, but then i get an error saying that the database is already open....is there a way of getting around this?
thanks
jimmyp
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Open Access Database With Excel Macro
Hi all!
I managed to open Access from Excel, but I'm not able to open a database.
Code:
Set Projekt = CreateObject("Access.Application")
Projekt.Application.Visible = True
Projekt.Application.Database.Open Filename:="C:oce...FTF_LA81_.mdb"
So does anyone know how should I alter the last line?
Thank you in advance!
Is It Possible To Run A Macro In An Excel Workbook From An Access Database - Resolved
I am in the process of making a database in Access 97 that has links to a couple of Excel 97 Worksheets (in same Workbook). There is a macro in the workbook that I'd like to run, preferably without getting the user to have to open the workbook, run the macro, then return to Access. I'm almost sure that I read somewhere that this was possible, but I've no idea how it would be done.
Any suggestions would be appreciated.
Thanks in advance.
JackiD
Resolved
with thanks to Suat
See Below
Create a button on your form, then put a command button on this form (click Cancel when it goes to wizard to create button) then right click on the button and point to Built Event. Select Code Builder in the list. It will goto VBA and show you the Click event of the Command button, just like below:
Private Sub Command1_Click()
End Sub
Now put your code into this event sub, it will seem like below when you place the code in it:
code:
Private Sub Command1_Click()
Dim xls As Object
Dim wrk As Object
Set xls = CreateObject("Excel.Application")
Set wrk = xls.Workbooks.Open("D:Excel97 FilesTrial.xls")
xls.Run ("Module1.ReSetTime")
wrk.Close False
xls.Quit
Set xls = Nothing
End Sub
(Module1.ReSetTime: means you are calling a macro in workbook, named as "ReSetTime" and which is placed in a module named as "Module1")
I hope this helps.
Suat
Edited by - JackiD on 7/27/2003 9:52:51 AM
Calling Macro In Excel From Access With Vba
HI
Im new here, this is my first question and I hope someone out there can lend me hand or point me in the right direction. Thanks.
I have developed an Excel template with a macro in VBA. I have ten different templates which I want to fire off. At the moment, all ten is fired off from Access using a shell command, but Im finding that it uses too much virtual memory. The ten templates are all the same except using different parameters, so I am thinking of just having one template and using the Access to call up a template and passing in the parameter so the macro can act on it using that parameter. Is there a way of calling a macro function in Excel using vba?
Anyone got any advice, will be greatly appreciated.
Cheers
Calling Access Macro From Excel
I have a model, running from a macro in Excel. The model cycles through hundreds of thousands of iterations for forecasting purposes. I am taking the output from the model after each iteration and dumping it into Access.
I am experiencing two problems calling the Access macro (that imports the data and places it in the appropriate table) from Excel:
1.) The database connection times out. The speed of the Excel macro is drastically reduced if I open Access after each iteration to dump data in. Therefore I have connected to Access in the beginning of my Excel macro and am only calling the Access macro after each iteration. I tried to open and close the database with each iteration, but it is time consuming and sometimes the database doesn't close which causes the program to halt.
2.) I call the Access macro in the following manner:
In the beginning of the macro the following is established:
Dim cn As New Access.Application
cn.OpenCurrentDatabase ("C:
ame.mdb")
cn.Visible = False
Then after each iteration:
SendKeys "MacroName{ENTER}"
cn.RunCommand acCmdRunMacro
Then at the very end:
cn.CloseCurrentDatabase
cn.Quit acQuitSaveNone
Set cn = Nothing
The problem is with the "SENDKEYS" statement. Sometimes, about 1% of the time, the macro seems to skip over the line. The macro window will open in Access prompting me to enter the macro name and "ENTER".
Any wisdom that could be imparted regarding these two issues would be greatly appreciated! Thanks for your help.
Calling Image From Access Database
Hello All,
This is my first posting. I have a quick question about calling an images from an access database.
I currently have an Access database that gives a name, description, and image of a given product. I want to be able to call that data into a VB form along with the image. Calling the data up from the .mdb has been successful, but I am having trouble calling the image. I have tried using OLE images as well as listing the path of the image. I would like to be able to click on the item in a listbox and have the description as well as the image appear in the form.
I am kind of stuck here and I have been doing a lot of research online but to no avail.
Can this be done?
I really appreciate any help that people can provide. Thanks!
Mike
Macro With Sql To Access Database
I work in Excel and Access all the time, but never written a macro that called a query. I think it is time and if I could get some help that would be great.
I have a spreadsheet with a list of SSNs. I have a table with a list of SSN's and Names. If I want to populate the spreadsheet with the related name, how would I get started? I typically import the spreadsheet, join the tables, and write a query that pulls down the original list with Name attached.
How would I get started skipping the step of importing the table? I would like to press a button and populate the names, do I need to write a query first or is that handled in the VBA?
Excel file=Transactions
Access file=EDW
Table Name = History_T
Thanks for any help,
--bnpatten
Richmond,VA
Opening Database With Excel Macro
Hello!
I opening access database to excel workbook with following command
Code:
Workbooks.OpenDatabase Filename:=Application.Workbooks("mittaus.xls").Path + "cnctulostimet.mdb", _
CommandText:=Array("Listat"), CommandType:=xlCmdTable
Problem is when I opening database, it opens "Data Link Properties" -window and "MS JET OLE DB Initializion Information" -window.
Is there any other ways to read database with excel worksheet or can I accept those windows automatically?
Sami
Automatically Run Macro In Access Database?
Hi,
I have an Access database named db1.mdb in my C: root that has a Macro named Macro1.
How would I get Windows 95 to automatically run Macro1 every hour using the System Agent? Would I need to create a batch file first and call that every hour? If so, what would the batch file look like?
Any help and examples would be appreciated..
Dan
Calling Access Report In VB Produces Can't Open Database Error
Hi! Am getting an error message "Microsoft Access can't open database because it is missing, or opened exclusively by another user." Please help...
Below is my sample code:
Dim accApp As New Access.Application
Dim strConn As String
strConn = App.Path & "ORDERS.mdb" & " /User " & gUser & " /Pwd " & gPwd & " /wrkgrp " & App.Path & "systems.MDW"
With accApp
.OpenCurrentDatabase strConn, False
.DoCmd.Minimize
.RunCommand acCmdWindowHide
.DoCmd.Maximize
.DoCmd.OpenReport "Orders", acViewPreview
.Visible = True
End With
Set accApp = Nothing
My database is a secured database.
Syntax For Calling A Function In A Remote/external Access Database.
Syntax for calling a function in a remote/external database.
Hi,
I seem to remember doing this long ago, but I can't remember the syntax for calling/executing a function in a remote/external database. Now after messing with it for a couple of hours I am not even sure that I did do it. Maybe I tried and failed like I am doing now.
Database_A: has a Module called "modPublicFunctions" and within that Module exists the function called "fxImportDataFromSpreadSheet". I tested this function locally by using a Form with a button to call it when I opened the database. When it runs it does its thing and returns a numerical value that indicates success (0 to num records processed) /Unprocessed records exist/error condition etc.
Everything executes in the background so to speak. It has tables that are filled/populated/appended but that's about it. No visual forms or message boxes.
Database_B: has a Form called "Frm_ControlCenter" and a button. When I press this button I would like to be able to execute the function in Database_A and be able to evaluate the return value.
Code:
Public Sub PerformsRemoteFunction_NoFunctionReturnValues()
Dim appAccess As Access.Application
Dim strAppPath As String
Dim strDatabaseName As String
Dim strDatabase As String
Dim rfxRetVal As Variant
strAppPath = Application.CurrentProject.Path & ""
strDatabaseName = "DatabaseA.mdb"
strDatabase = strAppPath & strDatabaseName
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase (strDatabase)
appAccess.Run "fxImportDataFromSpreadSheet"
Set appAccess = Nothing
End Sub
The code above executes the function in the remote database just fine. All of the functionality works as intended only I can't get any function return values. The appAccess.Run command does not return any values. The documentation for that command says it runs functions, but MOST functions return something. (Another, in the long list of Microsoft shortcommings)
It is not a show stopper, I can move the functionality into Database_B and remotely access the tables to push the data back to Database_A.
It is the I-Like-To-Figure-Stuff-Out in me that does not want to quit
regards,
wiz
Desperately Trying To Tie A Word Macro ComboBox To An Access Database
I have a macro setup in Word. I have a ComboBox control that needs to be tied to a Access database.
The DB has a table called "Patients", and for each name in the "Patients" table, a row must be inserted into the ComboBox.
I know I must fiddle around with the RecordSource or ControlSource options but I swear I cant find anything on it, can anyone help me?
Edited by - jjoseph10 on 11/24/2002 4:38:40 PM
Exporting Data From One MS Access Database To Another MS Access Database In Visual Basic
hello friends,
i want to transfer the data from one Access database to another Access database using visual basic.
The first Access Database name is Data1
Table Name is tbldata1
fields - ID -- Number
venueID -- Number
Date --- Date/Time
RNo --- Number
SRNo --- Number
HNo --- Number
i want to transfer this data into the second Access Database -- Data2
with table name same & fields also the same
pls can anyone help me out with this problem
Regards
Girish
Calling An Excel Macro From VB
I've posted my code in an attempt to get some help. The line - "xl.run OverallLoop" is an attempt to call a macro in an excel application. It doesn't seem to be working. Can anyone help? ps thanks to the guys who helped before!
Sub VBOverallLoop()
Dim ThisOpenPath As String, NRuns As Integer, Age As Integer
Dim VBThisFileName As String, NPats As Integer, PatAge As Integer
Dim i As Integer, j As Integer
NRuns = frmDiab.txtNRuns
NPats = frmDiab.txtNPats
ThisOpenPath = "c:PTDMMHDiab.xls"
Set xl = New Excel.Application
Application.Visible = False
Set xlWorkbook = xl.Workbooks.Open(ThisOpenPath)
Set xlWorksheet = xlWorkbook.Sheets("TherapyParams")
xlWorksheet.Activate
Set xlNumbTherapy = xlWorksheet.Range("NumbTherapy")
InitialTime = Timer
For i = 1 To NRuns
For j = 1 To 1 ' replace with xlNumbTherapy
frmDiab.lblProgress.Caption = "Run " & i & ", Therapy " & j & ""
Set xlWorkbook = xl.Workbooks.Open(ThisOpenPath)
Set xlWorksheet = xlWorkbook.Sheets("OtherParameters")
xlWorksheet.Activate
Set xlRangeDiabFlag = xlWorksheet.Range("RngDiabFlag")
Set xlRangeNPats = xlWorksheet.Range("RngVBNPats")
Set xlRangeFileName = xlWorksheet.Range("RngFileName")
Age = xlWorksheet.Range("RngAgeForVB").Value
xlRangeDiabFlag.Value = j
VBThisFileName = "Therapy" & j & "Run" & i & ""
xlRangeFileName.Value = VBThisFileName
xlRangeNPats = NPats
xl.Run OverallLoop
'Call DeleteSheets
temptxt = "c:PTDM" & VBThisFileName & ".xls"
xlWorkbook.SaveAs temptxt
Next j
Next i
xlWorkbook.Close
xl.Quit
Set xl = Nothing
Set xlWorksheet = Nothing
Set xlWorkbook = Nothing
If Timer - InitialTime < 300 Then
ThisTimeTaken1 = Int(Timer - InitialTime)
ThisTimeTaken2 = "seconds"
Else
ThisTimeTaken1 = Int((1 / 60) * Int(Timer - InitialTime))
ThisTimeTaken2 = "minutes"
End If
frmDiab.RunButton.Enabled = False
frmDiab.Show
frmDiab.lblProgress.Caption = "Done in " & ThisTimeTaken1 & " " & ThisTimeTaken2
frmDiab.FinishButton.Enabled = True
End Sub
Excel, Access Database Help
Hey All,
Newbie on excel/databases any help would be great thanks
ok I have database with "Items" on it which has two fields in it (ItemName,HowManySold), and I would like it so that when I click a button it displays this data in an excel sheet in 2 colums down the page.
ItemName HowManySold
Shirt 5
Pants 6
etc.
After which I would like to be able to produce a graph/table on it in excel, but i am guessing i need to add the data to excel first.
I have the following code at present but it does not display it down a page, it just shows one item!.
Code:
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add
Set xlWS = xlWB.Worksheets.Add
'Fill cells using recordset
Do Until rs.EOF
xlWS.Range("A1").Value = rs("ItemName")
xlWS.Range("B1").Value = rs("HowManySold")
rs.MoveNext
loop
' save spreadsheet
xlWS.SaveAs "mysheet.xls"
xlApp.Quit
' Free memory
Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
End Sub
Calling External Excel 4 Macro
Is it possible to call an external excel 4 macro from VB? We have a very complicated macro that doesn't seem to have an equivalent in VBA, and I need to incorporate it into my VB program...anyone have any knowledge of this?
Thanks!
In Excel, Calling Word Macro
Please Help, i have gotten this far and am stuck: I have an Excel Macro that copies a specific cell, and it then needs to call a Word macro to paste the contents. I know the copy/paste commands, but am having trouble calling the Word Macro. Any help would be appreciated, for i am tearing my hair out
Creating An MS Access Database From Within Excel
Hi All
Is it possible to create an MS Access database from within MS Excel VBA?
I have so far only been able to start another instance of the MS Access application. I now want to be able to :-
1. Create a blank general database
2. Create a blank table with 'n' number of fields of "variant" datatypes
3. Populate this table with data from an already populated Excel VBA array, hence the need for the fields in the table to be of variant datatype! Can this entire array be dumped in to the table in one go or does this need to be done field by field on a record by record basis?
4. Sort this table on 'm' ('m' obviously <= 'n') number of fields in the independently specified sort order for the 'm' sort fields, preferably using appropriate SQL statement to sort the table/query.
5. Repopulate the array with the sorted data. Does this also needs to be done on dataitem by dataitem or can the whole table be dumped back in to the array?
The reason I want to go this path is because of the high degree of probability of having to work with more 65536 rows of data with the need to sort the same on more that 3 fields on independently specified sort order for each of the sort fields - hence would not ordinarily be able to do it on an Excel worksheet or array sorting using quicksort sorting on 3 or 4 dimensions (columns) - the code that I got given by Carl Porch (refer to one of his older posts on this forum so far only works for me for sorting on independent sort order on upto 2 dimensions, I am currently working on this with his assistance to be able to do it on 3 & 4 dimensions).
The other reason of going this way is that I can then have a highly generic "modus operandi" for sorting a dataset on any number of fields on independently specified sort orders for each of the sort fields instead of having specific codes for sorting on 'x' number of fields.
Any assistance shall be highly valued.
Best regards
Deepak Agarwal
Searching An Access Database From Excel
Hello all,
I have yet another problem. I would like to automate Access from within an Excel workbook. I want to use an Excel userform to search an Access table, and fill in values within that Access database. I found this generic code on http://www.ExcelTip.com/:
Code:
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:FolderNameDataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ID") = Range("A" & r).Value
.Fields("FirstName") = Range("B" & r).Value
.Fields("MI") = Range("C" & r).Value
.Fields("LastName") = Range("D" & r).Value
.Fields("Prefix") = Range("E" & r).Value
.Fields("Suffix") = Range("F" & r).Value
.Fields("MaintCo") = Range("G" & r).Value
.Fields("Installer") = Range("H" & r).Value
.Fields("Distributor") = Range("I" & r).Value
.Fields("Manufacturer") = Range("J" & r).Value
.Fields("Agency") = Range("K" & r).Value
.Fields("ContractDue") = Range("L" & r).Value
.Fields("StartDate") = Range("M" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
What that code does is copy information from an Excel worksheet to an Access table, but I am not sure how to search an Access database, or how to fill in the information in the Access database from an Excel userform.
I am perfectly willing to read over the basics of automating Access within Excel's VB capabilities, but I have not really been able to find any helpful web sites on the subject.
The reason I am asking about this is:
We are generating contracts through an Excel workbook. The first worksheet of the workbook is an input sheet, which fills in all of the pertinent information throughout the rest of the workbook (the body of the contract). We also use an Access database to track our contracts, so that we can schedule inspections, etc. I would like to give users the option of adding a record to our Access database when they finish generating the contract. We assign a job number to each client, so the user would enter the job number in an Excel userform. Then, I want Excel to search our Access database (all of the information is stored in an Access table) for that job number. If the job number exists, then Excel would initialize one userform to update the Access database record. If the job number does not exist, then Excel would initialize a different userform to add a record to our Access database.
I know that this is a very long post, and that I am asking an awful lot, but really all I am asking (at the moment) is that someone point me in a direction toward a free web site that will give me some tutorials on automating Access from within Excel VBA.
Access Database To Excel Spreadsheet
I need to convert my database to spreadsheet format in VB code - is there an easy way to do this or do I have to write a CSV file and then read it into excel?
Excel Data To Access Database
Hi!
I will do a program that will get the data from an excel file and it will be loaded to Access database. Do you have any idea on how to do it? If yes, please advise me ASAP.
Thanks a lot!!!
Update Access Database Using Excel
Can anybody please help / let me know whether this is possible?
I have an excel spreadsheet that I use to update a table in an access database (using ADO)
Sample code attached
Code:
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:xxxxxxxxxx.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "xxxxxx", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 1 ' the start row in the worksheet
Do While Len(Range("B" & r).Formula) > 0
' repeat until first empty cell in column B
With rs
.AddNew
' add values to each field in the record
.Fields("Ref") = Range("B" & r).Value
.Fields("Number") = Range("C" & r).Value
.Fields("Project") = Range("D" & r).Value
.Fields("Manager") = Range("E" & r).Value
.Fields("Date") = Range("F" & r).Value
.Fields("RAG") = Range("G" & r).Value
.Fields("Scope") = Range("H" & r).Value
.Fields("Scope extra") = Range("I" & r).Value
.Fields("Progress") = Range("J" & r).Value
.Fields("Progress extra") = Range("K" & r).Value
.Fields("Start Date") = Range("L" & r).Value
.Fields("End Date") = Range("M" & r).Value
.Fields("Current End Date") = Range("N" & r).Value
.Fields("Phase") = Range("O" & r).Value
.Fields("Original Budget") = Range("P" & r).Value
.Fields("Approved Budget") = Range("Q" & r).Value
.Fields("Forecast Budget") = Range("R" & r).Value
.Fields("Author") = Range("S" & r).Value
.Fields("Written Date") = Range("T" & r).Value
.Fields("Sponsor") = Range("U" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
However, this writes a new record each time. I have defined the number as a primary key in the database but basically just want to provide the user with the ability to update an existing record (if it exists). I can give the user the choice on the input form and this will run either piece of code. The code I don't have is the update - help!!
Edited by - Geof on 6/19/2006 3:58:47 AM
Calling Access Macro Problem
Is there anyway to keep access from popping up while doing the snippit below?
'Open database
objaccess.OpenCurrentDatabase "my database"
Thanks
Can't Open New MS Access Database Created Using Excel VBA With ADO's
Hi All
When creating new Access databases using Excel VBA using ADO why am I not able to open the newly created database in Access 97.
The code used to create the new database is shown below :-
Code:
Dim adoxCat As ADOX.Catalog
Set adoxCat = New ADOX.Catalog
adoxCat.Create "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='" & Application.Path & "" & "TempDb.mdb';"
There is further code beyond to create table, its fields etc and populating the table etc. but at this stage that may not be relevant.
When trying to open this database, it comes up with the error "Unrecognised database format ...followed by the full path of the database"
Any suggestions!
Best regards
Deepak Agarwal
Comparing Cells In Excel To Access Database
hello,
I was wondering if i could get with help this problem...it's kind of urgent
how do I go about comparing a cell in excel with a cell in Access
I want to compare every cell in the 2nd column of Excel and see if they match up with any of the cells in the 1st field of access. and if so then copy that row in access and paste it into a new table. Thank you in advance
Compact And Repair Access Database From Excel?
Thanks for the great help on the first post guys, but now I have a different conundrum. I have all my Access queries running from Excel (trying to remove any users from actually having to go into Access), but I'm stuck on how I can compact and repair the database from Excel. I know from several tests already that after a few iterations the database blows up to a fairly unefficient size. Thanks to anyone willing to offer some help!
Writing An Excel Cell To An Access Database
I am importing a word document into Excel then using a macro to import the data to a template. When I run the macro, the last procedure is to write a few cells to an Access database. Is this possible???????
Access Database Importing Data From Excel - Please Help
I am trying to add excel files into an Access database. Each file has the same field infomation, but different values. I would like to add all those excel files into one table in Access. I do not knwo how to do that. If you have any idea on this subject please let me know.
thanks
Inserting Excel Sheets Into Access Database
Hi guys..
I'd like to know how to insert excel sheets into an Access database. This is what I'm trying to do:
1. I have VB user interface. This is connected to the mdb using ADODB.Connection. From here, whatever the user inputs goes into the mdb. No problems here.
2. The user also needs to select a certain excel sheet (maybe from a combo box) and VB will extract data from the excel sheet.
Question: How do i put these numerous excel sheets into the mdb and allow user to select it?? Can I put these excel sheets displayed on a combo box on the user interface??
Using VBA To Pull-out From DATABASE(ACCESS) To A Spreadsheet(EXCEL)
I was making a VB Application and i'm having a problem of using the method of pulling-out the record from the database using with the vb application to transport to a Spreadsheet when a bigger record is handle. Kinda the VB Application is going to halt or hanging out...
Is there a way of maximizing the performance of the application?
Can you help producing a code for the to pull-out record ? with reliable performance that much better....
thnx to you ......
irvin
-=-
__________________
ambs
Using VBA To Pull-out From DATABASE(ACCESS) To A Spreadsheet(EXCEL)
I was making a VB Application and i'm having a problem of using the method of pulling-out the record from the database using with the vb application to transport to a Spreadsheet when a bigger record is handle. Kinda the VB Application is going to halt or hanging out...
Is there a way of maximizing the performance of the application?
Can you help producing a code for the to pull-out record ? with reliable performance that much better....
thnx to you ......
irvin
-=-
Best Way Of Loading Access Database With Data From Excel...
Dear All,
just a quicky but has anyone performed the seemingly trivial task of loading an Access Database with data from an Excel spreadsheet.
The spreadsheet is used to collate information from various sources and then used to update a number (> 200) access databases in various locations (not all I think networked)...no dont ask why !
Best ways...things to avoid etc.
Regards
Kevin Blount
Export Data From Access Database To Excel
i am coding in vb to get records from Access database and then to create an excel sheet using that data.
the user has a choice to select a particular reocrds.
when the program is started the firts time, it goes to the form where a user can select a particular record and then create a n excel sheet. it creates an excel sheet and saves it . the problem is if i choose another record and try to create a excel sheet i get an error message :
error 1004 method 'Range' of Object '_Global ' failed.
can some one help me with this problem.
Late Binding To Access Database From Excel
Can anyone help me out here please? I've got a .mdb sitting in a shared location. I want a small number of users to be able to pull summary data from this (and send data to it eventually) from Excel. I have this code (early bound) which does what I need for initial phase, but want to late bind so each user doesn't need to add the reference. Can anyone tell me the changes I have to make please?
Code:
Sub test()
Dim db As Database
Dim RecSet As Recordset
Set db = OpenDatabase("e:MyDB.mdb")
With Sheets(2)
.Range(.Cells(2, 1), .Cells(65536, 2)).ClearContents
SQLString = "Some SQL string in here"
Set RecSet = db.OpenRecordset(SQLString, dbOpenDynaset)
.Cells(2, 1).CopyFromRecordset RecSet
End With
End Sub
Copy Excel Sheet To Access Database
If I posted this in the wrong place, I am sorry. Just seemed to be the right place.
I have an Excel .xls file (one sheet) that I would like to copy to an Access Database. I am totally lost how to start.
I have (using excel) saved the file to a Text delimited file and in Access I drawed this file in with some sucess. this was a loborous process and not a total sucess. There must be a cut and dryed way to do this. I only have 14 to do each week and I can continue the way I am going if I have to, but was hoping for a smoother method.
So, any ideas
Thanks
Charles
Extracting Access Database And Putting It Into Excel
Hello,
I am extracting information from my database that has been entered in by using a multi-lined text box. The problem I am having is that when I then copy the data from my database into an Excel spreadsheet the multiline text has a charachter at the end of each line (I imagine a CR or LF charachter?).
Is there something simple that I can do to get rid of this charachter?
M
Problems With Excel To MS Access Database Conversion Using VB 6.0
Hello!
I am currently exporting my database from Excel (.xls) to single Microsoft Access (.mdb) file using this code. There are 24 excel
files with single sheets (different sheet names in every file) and I will preprocess and put them into a single MDB file with 24
different tables. The excel files have similar data, for customer numbers but for different months, 24 months i.e. 2 years data.
The columns in all excel files are equal, but the rows are not, meaning some customers are not present in every excel file.
Just FYI: This is my masters project.
My first preprocessing requires to extract all customers and their data into a separate (.mdb) file which have data
for all 24 months, which will then be implemented into a intelligent statistical based system for checking good and
bad customers based on some criteria.
Q1: Customer numbers in every file are in the second column, and I remove them if there are any duplicate customers with Excel 2007.
The data is too long (50,000+ rows) which cannot be done manually for every excel file.. i.e. for every excel file if customer numbers
in the second column are same, the entire rows for those particular customer number are removed using Excel's removing duplicate columns
function.
I have seen this type of procedure can be done using Macros in VBA, but my project requires me to use VB, as the project I give to the
company they will use other data files later on, which cannot be integrated into the current system. Can anyone give me advice from
where to start off if this can be accomplished using VB?
Q2: Secondly when I am reading Excel files the sheet name in every file (there is only 1 sheet in every excel file) has to be
read through an automated procedure.. and currently I am using an array " $ mytype $ " in my pasted code to do this for every excel file.
Is it possible that I can integrate reading every excel sheet name automatically and then export the database to MDB rather than
specifying the sheet names in an array? How to integrate this it into my current ode? Using excel object? Again I am discouraged here to
use VBA and Macros.
Q3. Thirdly the major problem is when I delete the duplicate customer numbers in my excel files using Excel 2007, the rows deleted
create empty rows additionally at the end of the excel files.. and for my case there are approximately 3000-4000 empty excel rows created
for each excel file. My current code reads these EMPTY rows also in the end of the excel, which messes up my conversion into my MDB
database. Is there a way I can skip reading empty rows at the end of the file which do not have any data?
Ofcourse I can delete them manually using excel again, but it is time consuming and needs to be automated. Currently I think maybe
using sql select, to do this.. will it be handy?
Here is my code for excel to mdb conversion:
Code:
Sub CreateAndInsertIntoTable()
Dim tbl As New Table
Dim cat As New ADOX.Catalog
Dim cn As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim fld As ADODB.Field
Dim recNew As New ADODB.Recordset
Dim strExcelPath As String
Dim intcnt As Long
Dim month As Variant
month = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)
Dim lngPosition As Long
Dim mtype As Variant
mtype = Array("Jan2006", "Feb2006", "Mac2006", "Apr2006", "May2006", "Jun2006", "Jul2006", "Aug2006", "Sep2006", "Okt2006", "Nov2006", "Dec2006", "Jan2007", "Feb2007", "Mac2007", "Apr2007", "May2007", "Jun2007", "Jul2007", "Aug2007", "Sep2007", "Okt2007", "Nov2007", "Dec2007")
'Loop for all 24 months
For lngPosition = LBound(month) To UBound(month)
'Excel File Path
strExcelPath = App.Path & "" & month(lngPosition) & ".xls"
'Opening Catalog Connection
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "data.mdb"
'Opening Excel Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strExcelPath & ";Extended Properties=Excel 8.0;"
'Opening Sheet in Excel Sheet Like a Table
rec.Open "Select * from [0121_" & mtype(lngPosition) & "$]", cn, adOpenKeyset
'------------------------------------------------------------------------------------
'Creating Table
'------------------------------------------------------------------------------------
'Assigning Table Name
Set tbl = New ADOX.Table
tbl.Name = mtype(lngPosition)
'Appending Column-The Col Name will be the Column name from the Sheet
For Each fld In rec.Fields
tbl.Columns.Append fld.Name
Next
'Creating Table using Catalog Object of ADOX
cat.Tables.Append tbl
'Opening the Newly created table in data.mdb
recNew.Open mtype(lngPosition), cat.ActiveConnection, adOpenKeyset, adLockOptimistic
'Making Pg Bar Visible
ProgressBar1.Visible = True
'Setting Max value of Pg BAr as No of Records in the Sheet
If rec.RecordCount <> 0 Then
ProgressBar1.Max = rec.RecordCount
End If
'Initialising Counter
intcnt = 1
Do Until rec.EOF
'Calling DoEvents so as to see the counter correctly
DoEvents
With recNew
.AddNew
For Each fld In rec.Fields
'Assigning value to the recNew Recordset for Insertion
.Fields(fld.Name) = IIf(IsNull(rec(fld.Name)), "", rec(fld.Name))
Next
.Update
End With
'Assigning Value to PgBar Control
ProgressBar1.Value = intcnt
'Assigning Value to Label Control
lblStatus.Caption = "Added " & intcnt & " Records..."
'Incrementing Counter
intcnt = intcnt + 1
'Moving to Next Record
rec.MoveNext
Loop
DoEvents
'Showing the Location of MDB File
lblStatus.Caption = "Open the MDB File at " & App.Path & "data.mdb"
ProgressBar1.Visible = False
cn.Close
recNew.Close
Next lngPosition
End Sub
Edited by - awyeah on 4/19/2008 2:56:43 AM
Importing Excel Files To Access Database Using VBA
Hi Everyone,
I have been searching for a solution to import information from on sheet of an excel file to a table in my access database. I have found that the DoCmd.TransferSpreadsheet should be used but I can't get it to work. What I want is to be able to open up the "Browse" dialog box and ask the user to locate the .xls file and then read the "Master" sheet and import it's data in to an existing table in the database.
One peice of code that I found is the follwoing:
Code:
"DoCmd.TransferSpreadsheet acImport, 8, tablename & " -tbl", DIR &
"Book2.xls", True"
However I couldn't find any information on DoCmd.TransferSpreadsheet to help me understand what are the variables that it needs in order to import data from excel file.
For opening the "Browse" dialog box I Found the following very very veryyyy long procedure which I knew it wouldn't work (http://www.mvps.org/access/api/api0001.htm ).
Code:
**UPDATE**
This part is Resolved; I removed the code since it's very big, You can find it in the above link if you want.
**UPDATE**
Can anyone lead me toward a method that works? I have been trying and I couldn't find much... Any help would be much appriciated
Thanks in advance,
Parsa
________________________________________________________________
Parsa Bahrami
Undergrad Mechanical Engineering
The gravity cannot be held responsible for people falling in love!! Einstein
Edited by - parsa on 11/1/2007 11:59:32 AM
HOW DO I CONVERT A EXCEL FILE TO ACCESS DATABASE?
Hello Friends
Can anybody please tell me that
1) HOW CAN I WRITE A CODE THAT MAY CONVERT A EXCEL FILE TO ACCESS DATABASE?
2) HOW MAY I USE FILE-LISTBOX THAT SHOULD DISPLAY THE FILES ONLY OF .XLS EXTENSION?
Please send me the same as soon as possible.It's very urgent for me.
Hope u will help me.....
Exporting Access Database Into A Excel Sheet
Dear Friends!
Please help me to export a Access databse into a excel sheet efficiently.
I need to export several Access tables into the worksheets within the same workbook.
thanks
Regards
Brinda
Not Able To Transfer All Data From Excel To Access Database
Hello,
I am trying to transfer data from a excel spreadsheet to
a access database. The tranfer only works for half of the data
The excel sheet has aprox. 34000 records. When the tranfer has finished only 16000 records were tranfered.
I used DAO 3.51 object Library. When I used DAO 3.6 object Library all 34000 record were tranfered but I am unable to open
the access file.
Any help would be much appreciated.
Thanks
Karl
Error In Transferring From Excel To Access Database
I have created a DSN for an Excel file and am transferring the data to an access database through VB. One of the coulmns in the Excel file contains numeric as well as alphanumeric entries.
The numeric data is being transferred but the aplhanumeric data is not being read. A Null value is returned for all such data.
All the cells in the Excel file are formatted as "General".
Please give me a solution for this error. It is urgent please.
Calling Microsoft Access Module Or Macro From VB
I would like to call a Microsoft Access Module or Macro from a VB executable. This way I could schedule some database activity from my NT server.
I do not know the commands to open an Access Database (with password protection) and us the DoCmd.
Please Help!!
|
TRACKER
