2

I wrote a VBA-code which uses a batch to copy some files from A to B and then run Access-macros which use the copied files in B. After running the macros the VBA-code the queries results from Access into the excel.

' run the batch
    RetVal = Shell("I:\myW\k1ltios1_k1ltit31_Kopie.bat", 1)

' ============================
' run the macros in ACCESS

Dim accessApp
Set accessApp = CreateObject("Access.Application")  'define an Access object

accessApp.Visible = True 'hold Access object open

accessApp.UserControl = True
.
.
.
' <<< copy the results from Access into th eExcel files >>>
'#Enter  the valus of cells in the arrays
i = 0
ReDim marrSql(0)
 For Each cell In Worksheets("eingaben").Range("B20:B21")
    marrSql(i) = cell
    i = i + 1
    ReDim Preserve marrSql(i)
    Debug.Print marrSql(i)
Next


i = 0
ReDim marrPlace(0)
 For Each cell In Worksheets("eingaben").Range("A20:A21")
    marrPlace(i) = cell
    i = i + 1
    ReDim Preserve marrPlace(i)
Next
'# end of entering

For i = 0 To 1
'
sSQL = "SELECT * FROM [" & marrSql(i) & "];"

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmdCommand As ADODB.Command
.
.
.

Next

If I run the code it works well but all windows (batch, Access and Excel) open immediately together! Now my question: How is it possible to start the batch and if there is no error it should be closed then run the macros in Access and generate the queries if there is no problem copy the results into Excel and then close the Access? I want to be sure that the steps are done one after each other because otherwise the code would peek up the old results in one or another step!

Erik A
  • 31,639
  • 12
  • 42
  • 67
maniA
  • 1,437
  • 2
  • 21
  • 42
  • 2
    See http://stackoverflow.com/questions/1439200/vba-shell-and-wait-with-exit-code or http://stackoverflow.com/questions/8902022/wait-for-shell-to-finish-then-format-cells – Andre Sep 28 '16 at 09:19

0 Answers0