Move Excel Module to Access (1 Viewer)

[LadyDi]

I have a module in Excel that I would like to move to Access, but I'm not sure how to go about doing it. The module loops through the data stored on a certain sheet and enters the information in another system, called Extra. The module enters thirteen lines of data, presses Enter, and continues in that loop until all the data on the sheet has been entered. Once it is moved to Access, I would like it to loop through the results of a query instead of the data on a certain sheet. Here is the code that I have in Excel:

Option Base 1
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

 
Sub EnterCount()
   
    Dim strSendKeys As String
    Dim n As Integer
    Dim Line As String
    Dim Data(13) As Integer
    Dim i As Integer
    Dim varNumber As Double
    Dim System As Object
    Dim Sess0 As Object
     
    
    Set WshShell = CreateObject("WScript.Shell")
    Set System = CreateObject("EXTRA.System")
    Set Sess0 = System.ActiveSession
    SessName0$ = "Session1.EDP"
    
    If Not Sess0.Visible Then Sess0.Visible = True
    
    
   Do
For i = 1 To 13
     
        Data(i) = Selection.Value
        Selection.Offset(1, 0).Select
    Next
    
  
For i = 1 To 13
    Sess0.Screen.SendKeys ("<EraseEOF>")
    Sess0.Screen.SendKeys (Data(i))
 
    Sess0.Screen.SendKeys ("<TAB>")
    Next
    
    Sess0.Screen.SendKeys ("<ENTER>")
    
    Sess0.Screen.WaitHostQuiet (2000)
    Sleep (3000)
    
    Loop Until IsEmpty(ActiveCell.Offset(1, 0))
    
 
    
End Sub

Can this code be modified to run through Access instead? Any help you can provide would be greatly appreciated. I've just spent about 40 hours building a new database to automate a process just to find out that the people won't use it unless it can run this module. :banghead:

 

[Frothingslosh]

It can, but let's back up a step. You want to import data from Excel to Access. Is the data scattered all over the spreadsheet, or is it in a single range of cells? I ask because there are faster ways to transfer data.

We can get into the rest of the code after that, but as a rule, you should avoid SendKeys like the plague.

 

[LadyDi]

The data was originally in a single range of cells on the spreadsheet. Now, it is a column in a query. I would rather not put it back in Excel if I don't have to. However, if that's the only way to get it to work, that's fine.

 

[Frothingslosh]

No, no, I thought this was going to be an ongoing or repeated process, that's all, and was going to introduce you to the joy of TransferSpreadSheet. It might end up being the best way in the end, but let's not work backwards.

To loop through the results of a query, you would need two nested loops. The outer loop is basically

Do Until rs.EOF...Loop

which checks each record in turn, while the inner loop would use

For Each fld in qdf.Fields...Next fld

which cycles through each field in the current record.

Basically, when working with recordsets, you need to get used to working with flexible numbers, rather than a specific count. You CAN get that count, and sometimes you'll need to, but usually it just makes life harder.

Now, that said, what precisely are you trying to do with the data in this query? A powerful tool, SQL is, and the better option it might be!

/yoda

Anyway, it's quite possible that you can do what you want with a query. If I'm reading your code right, it sounds like you want to take the results of the query and insert that data into a table? If so, that's pretty straightforward, and can be done with either an Append or an Update query, depending on what precisely you're doing.

 

[LadyDi]

Actually, what I need to do is take the results of a query in my database and use that information to update another system that tracks the technician inventory (that's the Extra.System that is referenced in the original code). The Extra program functions like a table. You pull up the bin number, the screen lists the parts in the bin, and then in the last column you need to enter the number of each part the technician has (that's what I would like the database to do).

 

[LadyDi]

Does anyone have any suggestions on this?

Do I just need to leave this code in Excel? If so, is there a better way to write it (i.e. without using the SendKeys command)?

Is there a way to get Access to enter information in a non-Microsoft program?

 

[JHB]

Maybe the below code could give you an idea, (it is not tested).

  Dim strSendKeys As String
  Dim n As Integer
  Dim Line As String
  Dim Data(13) As Integer
  Dim i As Integer
  Dim varNumber As Double
  Dim System As Object
  Dim Sess0 As Object
  Dim dbs As DAO.Database, rst As DAO.Recordset
  Set WshShell = CreateObject("WScript.Shell")
  Set System = CreateObject("EXTRA.System")
  Set Sess0 = System.ActiveSession
  SessName0$ = "Session1.EDP"
  
  If Not Sess0.Visible Then Sess0.Visible = True
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("YourQuery")
   
  If Not rst.EOF Then
    Do
      For i = 1 To 13
        Sess0.Screen.SendKeys ("<EraseEOF>")
        Sess0.Screen.SendKeys (rst![TheFieldName])
        Sess0.Screen.SendKeys ("<TAB>")
        rst.MoveNext
        If rst.EOF Then
          Exit For
        End If
      Next
      Sess0.Screen.SendKeys ("<ENTER>")
      Sess0.Screen.WaitHostQuiet (2000)
      Sleep (3000)
    Loop Until rst.EOF
  End If