Hi,
Please can someone help me!
I have an excel macro which runs a macro in a microsoft access
database using the following code:
'************** *************** ***************
'* This part of the routine runs another routine within *
'* MS Access to export data to MS Excel *
'************** *************** ***************
Dim mydb As Object
'Establish the object reference
Set mydb = GetObject("\\ri mlfps1\sys\Alls taff\Sungard\St atistics\Operat ions.mdb")
'Run the MS Access Routine
mydb.Applicatio n.Run "ExportData "
'Quit MS Access
mydb.Applicatio n.Quit
'Release the automation control
Set mydb = Nothing
'************** *************** *************** *
'* End Of Excel Code
'************** *************** *************** *
The macro that is run in the database is called ExportData, and does
the following:
Public Sub ExportData()
Dim MyArray(10)
'Define Array Of All Manager Codes
MyArray(0) = "GD"
MyArray(1) = "GE"
MyArray(2) = "GM"
MyArray(3) = "GO"
MyArray(4) = "UC"
MyArray(5) = "UD"
MyArray(6) = "UE"
MyArray(7) = "UI"
MyArray(8) = "UP"
MyArray(9) = "UM"
MyArray(10) = "UL"
For i = 0 To UBound(MyArray)
DoCmd.DeleteObj ect acTable, "P0424" & MyArray(i)
Next
For i = 0 To UBound(MyArray)
Dim myfile As String
DoCmd.TransferT ext acImportDelim, , "P0424" & MyArray(i),
"\\rimlfps1\sys \Allstaff\Sunga rd\Downloads\Cu rrent Month\P0424" &
MyArray(i) & ".csv"
Next
DoCmd.OpenRepor t "Overdraft Positions"
DoCmd.OpenRepor t "Overdraft Positions"
'Create .csv output file
DoCmd.OutputTo acOutputReport, "Overdraft Positions", acFormatXLS,
"\\rimlfps1\sys \Allstaff\Sunga rd\Statistics\D aily Overdraft
Positions\Daily Overdraft Positions " & Format(Date, "YYYY MM DD") &
".xls"
End Sub
I have the doCmd.openrepor t twice as i need 2 copies of it. As you can
tell i am not really a developer. The database then closes
automatically. When the macro is running, and the database is opened,
the user can only see the front page of the database. i.e. the
database window is hidden.
This procedure all works fine when I run the excel macro, and the
report gets produced fine and it prints and saves the file. The
problem occurs whenever anyone else tries to run the excel macro. When
they try this, the database opens up, and the macro runs, but then the
user gets prompted for a database password. All they can do then is to
come out of the database, and whenever anyone tries to go into the
database again, it prompts for a password.
I have NEVER EVER placed a password on this database, and do not have
any idea why it is doing this or how to stop it! I really need to get
around this as I want to pass this off to the end user!
Please can someone help. If there is anything i have ommitted, please
let me know!
James
Please can someone help me!
I have an excel macro which runs a macro in a microsoft access
database using the following code:
'************** *************** ***************
'* This part of the routine runs another routine within *
'* MS Access to export data to MS Excel *
'************** *************** ***************
Dim mydb As Object
'Establish the object reference
Set mydb = GetObject("\\ri mlfps1\sys\Alls taff\Sungard\St atistics\Operat ions.mdb")
'Run the MS Access Routine
mydb.Applicatio n.Run "ExportData "
'Quit MS Access
mydb.Applicatio n.Quit
'Release the automation control
Set mydb = Nothing
'************** *************** *************** *
'* End Of Excel Code
'************** *************** *************** *
The macro that is run in the database is called ExportData, and does
the following:
Public Sub ExportData()
Dim MyArray(10)
'Define Array Of All Manager Codes
MyArray(0) = "GD"
MyArray(1) = "GE"
MyArray(2) = "GM"
MyArray(3) = "GO"
MyArray(4) = "UC"
MyArray(5) = "UD"
MyArray(6) = "UE"
MyArray(7) = "UI"
MyArray(8) = "UP"
MyArray(9) = "UM"
MyArray(10) = "UL"
For i = 0 To UBound(MyArray)
DoCmd.DeleteObj ect acTable, "P0424" & MyArray(i)
Next
For i = 0 To UBound(MyArray)
Dim myfile As String
DoCmd.TransferT ext acImportDelim, , "P0424" & MyArray(i),
"\\rimlfps1\sys \Allstaff\Sunga rd\Downloads\Cu rrent Month\P0424" &
MyArray(i) & ".csv"
Next
DoCmd.OpenRepor t "Overdraft Positions"
DoCmd.OpenRepor t "Overdraft Positions"
'Create .csv output file
DoCmd.OutputTo acOutputReport, "Overdraft Positions", acFormatXLS,
"\\rimlfps1\sys \Allstaff\Sunga rd\Statistics\D aily Overdraft
Positions\Daily Overdraft Positions " & Format(Date, "YYYY MM DD") &
".xls"
End Sub
I have the doCmd.openrepor t twice as i need 2 copies of it. As you can
tell i am not really a developer. The database then closes
automatically. When the macro is running, and the database is opened,
the user can only see the front page of the database. i.e. the
database window is hidden.
This procedure all works fine when I run the excel macro, and the
report gets produced fine and it prints and saves the file. The
problem occurs whenever anyone else tries to run the excel macro. When
they try this, the database opens up, and the macro runs, but then the
user gets prompted for a database password. All they can do then is to
come out of the database, and whenever anyone tries to go into the
database again, it prompts for a password.
I have NEVER EVER placed a password on this database, and do not have
any idea why it is doing this or how to stop it! I really need to get
around this as I want to pass this off to the end user!
Please can someone help. If there is anything i have ommitted, please
let me know!
James