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.

 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

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....!!!

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

[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 !

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

[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:

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

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.

 
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

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

 
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

[xCav8r]

 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

[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