Solved: How to Retrieve "Existing Excel values" to Access ??

[beginner]

hello....got some headache and need somebodies help...

I am trying to figure out how to using Access DAO or ADO to "retrieve existing Excel Worksheets" particular cells value(s) and save back to Access table, i can only got some coding from some sources as below but which only show how to "write" data from Access to a "new created" Excel & worksheet only...

Can somebodies help....thanks a lot.


[VBA] Sub OpenExcel()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Add
ActiveCell.FormulaR1C1 = "ABC"
xlApp.Quit
Set xlApp = Nothing
End Sub
[/VBA]

Beginner

[xCav8r]

This should help you read it.

http://www.exceltip.com/st/Import_da...Excel/429.html

[beginner]

hi xCav8r....Thanks a lot and I will digest the coding first and tks again.


Beginner

[xCav8r]

If you need help with the reading portion, just let me know. After you're comfortable with reading the data, we'll move on to storing it in Access.

[beginner]

Hi xCav8r, this is beginner and thanks for your info. & help. however, i find it ADO seems a bit more difficult to understand than DAO and can I use DAO sample to ask your help again.....

Below got the coding sample for my headache.....what i want to do in details are :-

1. under Access Form, click a Product_Search button and run the VBA when "on_click"
2. then, the VBA will first clear the records in my current database (ABC.mdb)'s table "ABC_level_1" and open a recordset.
3. then, it also open the other Excel file (Excel.xls) in the same path as the ABC.mdb and located the WorkSheet(1)
4. Then it start to retrieve data from the Excel cells value to access under some criterias ( lines start with "i=4" )

However, the VBA got error and stop at the line "Set xlApp = ......" and i also find some error at the line "Set mySht = .....(either options also got errors)", so I cannot achieve the above step 3. ie. I cannot locate the Excel.xls to access the Worksheet(1) cell values.

I checked the line "myDb.Execute "DELETE * FROM " & myTblName" is working ok as the table was being empty and those data retrieve lines after "i=4" should be ok as i tried run them inside Excel macro. (although the VBA still not yet reach this lines)


Would you help to study what should be the correct coding to open the Excel file and locating the required worksheets or some other coding not correct ??
Many many thanks....!!!


[VBA] *******************************
Private Sub Product_Search_Click()
Dim xlApp As Excel.Application
Dim filename_excel As String

Dim myDb As DAO.Database
Dim myRst As DAO.Recordset
Dim myFileName As String
Dim myTblName As String
Dim myKey As String
Dim mySht As Worksheet
Dim i As Long
Dim j As Long
myFileName = "ABC.mdb"
myTblName = "ABC_level_1"

Set myDb = CurrentDb
myDb.Execute "DELETE * FROM " & myTblName
Set myRst = myDb.OpenRecordset(myTblName)
filename_excel = "Excel.xls"
Set xlApp = GetObject(ThisWorkbook.Path & "\" & filename_excel, "Excel.Application")
' Set mySht = xlApp.Workbooks.OpenDatabase(filename_excel).Worksheets(1)
Set mySht = xlApp.Worksheets(1)

i = 4

With myRst
.Index = "PrimaryKey"
For i = 4 To mySht.Range("A100").Row
If Trim(mySht.Cells(i, 23).Text) = "142" Then Exit For
If mySht.Cells(i, 4 + 19).Value <> "" Then
.AddNew
myRst(1).Value = mySht.Cells(2, 10).Value
myRst(2).Value = mySht.Cells(2, 30).Value
For j = 4 To .Fields.Count - 1
myRst(j - 1).Value = mySht.Cells(i, j + 19).Value
Next
If mySht.Cells(i, .Fields.Count + 19).Value = "" Then
myRst(.Fields.Count - 1).Value = "N"
Else
myRst(.Fields.Count - 1).Value = "Y"
End If
.Update
Else
End If
Next
.Close
End With

myDb.Close
Set myRst = Nothing
Set myDb = Nothing

End Sub
*******************************
[/VBA]

[mdmackillop]

Hi Beginner,
If you select your text and click on the VBA button it formats the the code as above, making it more "readable" in the forum.
Regards
MD

[beginner]

Hi MD, thanks for your tips.!!


To all helpers in this forum, refer the previous DAO sample, I believe below coding to locate the Excel.xls file and Worksheet(1) got something wrong but not sure how to correct.......any help very much appreciated , thanks !


[VBA] **********************
Set xlApp = GetObject(ThisWorkbook.Path & "\" & filename_excel, "Excel.Application")
' Set mySht = xlApp.Workbooks.OpenDatabase(filename_excel).Worksheets(1)
Set mySht = xlApp.Worksheets(1)
**********************
[/VBA]

[xCav8r]

Sorry, beginner, I've been busy lately and haven't had the time to respond. It looks like you want to read your data by creating an instance of Excel, which, of course, is fine--though it's obviously not DAO. Just replace your existing code using the following as a model:

[VBA] Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.workbooks.Open strPathToFile
[/VBA]

That should get you rolling. If you're still having problems, please post a sample workbook and mdb.

[beginner]

hi XCav8r, grateful for your help....it's working now.!! Although the solution seems simple but its very important for me !!

next step i will try to generalize the search function by using multiple combo with multiple worksheets, may also need your help again once i face another problem.....and thanks again for your help & adv.

beginner.

[beginner]

hi xCav8r,

forget to ask you if i use the create Excel instance (not DAO) as what you mentioned, can i apply it under network file server for multi-users ? if yes, how should i locate my Excel file in the coding ? can i use something like... ThisWorkbook.Path & "\" & ExcelFileName etc. but it seems didn't work in my previous coding.

Thanks in advance for your help again.

beginner

[xCav8r]

You can't use ThisWorkbook.Path in Access unless you create an Excel application object, but that won't help you since you can only return the path of a workbook that's already open, not one you want to open. The best method would be to keep it stored in a static directory on the network. An alternative would be to search for it, but that could take a long time to complete and not necessarily generate the results you desire.

[beginner]

hi xCav8r, thanks for your adv.again.

beginner

[beginner]

hi xCav8r and other helpers....

i encountered another headache and need your adv.

- After running coding under Access2000 to retrieve Excel file data,
- closed Access
- try to open the Excel file which was just opened by Access before, but cannot and got error message window " 'filename' is now available for editing. Choose Read-Write to open it for editing..."

below is the coding structure for ref.


[VBA] Dim appExcel As Object
Dim objExcel As Object

Dim workBook As Object
Dim workSheet As Object
Dim ExcelFile As String

ExcelFile = "c:\test.xls"

Set objExcel = CreateObject("Excel.Application")
' Show spreadsheet on screen
objExcel.Workbooks.Open "filename"

appExcel.Application.DisplayAlerts = False

*****************
coding

******************
workbook.Close
appExcel.Application.Quit

' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing
Set objExcel = Nothing
[/VBA]
Any adv. very much appreciated.

Tks.
Beginner

[xCav8r]

If you had an error and stopped execution before the objects were released, it could still think it's open. That happens to me all the time. See if a process is running that you can terminate in the task manager. If not, rebooting should free it up.

BTW, it looks redundant to have objExcel and appExcel since my guess is they are both objects used with CreateObject("Excel.Application"). Was this on purpose? You running two separate threads?

[beginner]

hi xCar8r, tks again for your tips.

below more clear about my coding structure.

Yes, i check the task mgr. got quite a lot of Excel.exe running if i run that function many times (also after closing the Access, some screens remain in desktop showing those Excel workbooks, which opened by coding before, was linked to something and cannot be closed or move to other folders...etc. ) and occasionally the system will give "not enough memory" etc. if run the function too many times,

so i need to use code to close all the Excel stuffs at the end of the coding but my below coding seems can't do it perfectly. any other suggestion on the coding ?

one more query, instead of using below remark line coding, any commands can closed the Active Workbook during running without saving the workbook and without any pop up prompt screen come out ? because if i use below Remark line code, i can avoid those pop up prompt screen but the workbook still open hiddenly and this is not allowed in my system.

' objExcel2.Application.DisplayAlerts = False

Thanks very much for any help again.

BR/
beginner

[VBA]
Private Sub fun_Click()

Dim objExcel2 As Object

Dim myDb2 As DAO.Database
Dim myRst2 As DAO.Recordset

Dim mySht2 As Worksheet
Dim myWb2 As Workbook

Set myDb2 = CurrentDb

Set myRst2 = myDb2.OpenRecordset("table-name")

' objExcel2.Application.DisplayAlerts = False

''''''''''''checked if any unclosed workbook and closed it'''''''''''''

Set myWb2 = objExcel2.ActiveWorkbook

If myWb2 Is Nothing Then

Else

myWb2.Close

End If
'''''''''''''''''''''''''''''''''''''''''

Set objExcel2 = CreateObject("Excel.Application")

objExcel2.Workbooks.Open "File-name"

Set myWb2 = objExcel2.ActiveWorkbook


''''''''''''''''

coding....

''''''''''''''''

' objExcel2.Application.DisplayAlerts = True

myRst2.Close
myDb2.Close

myWb2.Close
objExcel2.Workbooks.Close
objExcel2.Quit

Set myRst2 = Nothing
Set mySht2 = Nothing
Set myWb2 = Nothing
Set myDb2 = Nothing
Set objExcel2 = Nothing

End Sub
[/VBA]

[xCav8r]

[VBA] Private Sub fun_Click()
'Requires MS Excel Library to use Worksheet and Workbook
Dim objExcel2 As Object
Dim myDb2 As DAO.Database
Dim myRst2 As DAO.Recordset
Dim mySht2 As Worksheet
Dim myWb2 As Workbook

Set myDb2 = CurrentDb
Set myRst2 = myDb2.OpenRecordset("table-name")

'objExcel2.Application.DisplayAlerts = False
'you can supress messages, but you should do that by putting in the parameters you need for the commands
'that you're doing (like saying to save or not to save--just search in help for what you're doing to see
'what your options are

''''''''''''checked if any unclosed workbook and closed it'''''''''''''
Set objExcel2 = CreateObject("Excel.Application")
'you have to create the excel.application object before you can use it
'I moved this line up here
Set myWb2 = objExcel2.ActiveWorkbook
'you can't do this from another Office app to see if excel has a workbook open
'it might be possible from VBA to do this (I don't know), but I would suggest that you focus instead
'on making sure that every closes properly instead of trying the shut-down-if-running approach

'Below comments on If Block for learning purposes only (see above for why)
If Not myWb2 Is Nothing Then
'note the change in syntax above
myWb2.Close
'this won't happen with the current code, since there is no activeworkbook of objectExcel2
Else
'this would catch something you didn't plan for
End If

objExcel2.Workbooks.Open "File-name"
Set myWb2 = objExcel2.ActiveWorkbook
'this isn't necessary, since whatever is just opened will be the activeworkbook

''''''''''''''''
'coding....
''''''''''''''''

myRst2.Close
myDb2.Close

myWb2.Close
objExcel2.Workbooks.Close '<-- I don't think this'll work without qualifying workbooks
'which is already implicit in myWb2.Close; this line will generate an error and is
'unecessary
objExcel2.Quit '<--- this is probably what you were forgetting before or your code was
'stopped by an error before it could reach this point; add an error handler to do this
'so you don't end up with a bunch of instances of Excel running

Set myRst2 = Nothing
Set mySht2 = Nothing
Set myWb2 = Nothing
Set myDb2 = Nothing
Set objExcel2 = Nothing

End Sub
[/VBA]

[beginner]

hi xCav8r, tks for your valuable adv. again and will try them. tks.

BR/
Beginner

[beginner]

hi xCav8r (or any other helpers in this forum),

one quick question relating to simple Form input (though not involve any VBA coding, but i stuck at this point) that i believe you can help.

Suppose a simple operation involving only data entry in a Form involving lots of fields and then update to the table (not by coding but by the form itself.) , would you adv. how to set the form property or other easiest method so that after operator finished key-in & update the 1st record, those field values still be maintained in the fields of the Form, so that the operator no need re-key in same values again for the 2nd record. I thought there should be some Form's text property can be set but I check all of them cannot figure out which one.

many tks for any adv.

BR/
beginner

[xCav8r]

hi xCav8r (or any other helpers in this forum),

It would be nice for you to get some alternate advice to mine, huh?

Suppose a simple operation involving only data entry in a Form involving lots of fields and then update to the table (not by coding but by the form itself.) , would you adv. how to set the form property or other easiest method so that after operator finished key-in & update the 1st record, those field values still be maintained in the fields of the Form, so that the operator no need re-key in same values again for the 2nd record. I thought there should be some Form's text property can be set but I check all of them cannot figure out which one.

A few unsolicited words of caution: in my experience, the biggest problem with databases is people, and auto-populating fields can often be problematic in that regard. Data entry is tedious, and most people (understandably) get bored then lazy. It's too easy to turn your brain off, and operating on autopilot isn't the best formula for accurate data entry. I assume, of course, that this is something that you've considered, but the circumstances warrant this.

The short answer is I don't know how to do with without VBA. An alternative--though not very flexible and one you've probably already considered--would be to assign default values to your fields.

[beginner]

hi xCav8r, tks for your adv., you already gave me a feasible idea about using default values, so may be it can be done by setting default values of the last records by using coding ? i will try to fig. out first, tks.

beginner