Open Macro in Design Mode (1 Viewer)

[Smilie]

I need my users to be able to open a macro in design mode because they are using the transfer database function to bring in a report that changes names every month. So, they need to update the source file before running it.

Is there a way to open a macro in design mode besides doing it from the main database screen??? You can open every other kind of object by saying "Docmd.OpenForm" or whatever you are trying to open...

I would love to be able to have a macro open another macro in design mode on an onclick event.

Any ideas???

Thanks in advance.

 

[Birdy]

Why don't you do this in VBA and get the user to select the file that is to be brought in.

This way the Macro will not have to be changed.

Also, this is more user friendly than having to change a macro and then typing the file name incorrectly.

 

[Smilie]

That sounds like a great idea, but unfortunately I don't know how to do that. Since I am self taught, I know only enough VBA to get by.

If this is something that you have an example of I would love to see it or if you could offer some general guidance of how to go about it.

thanks!

 

[Birdy]

This is from a form that I have used.
You will need to use the Common Dialog box with it.

Private Sub cmdBrowse_Click()
Dim strMsg As String
' Disable all other command buttons
cmdExit.Enabled = False
btnClearNewFlags.Enabled = False
cmdCalcMetric.Enabled = False
'cmdConvertPackSizes.Enabled = False 'v9.01
cmdCalcMargins.Enabled = False
cmdUnitPricing.Enabled = False 'v8.02

' Set CancelError is True
Forms!frmExtract!CommonDialog1.CancelError = True
On Error GoTo ErrHandler
' Set flags

Forms!frmExtract!CommonDialog1.Flags = cdlOFNHideReadOnly
' Set filters
Forms!frmExtract!CommonDialog1.Filter = _
"Dat Files (*.dat)|*.dat|" & _
"Text Files (*.txt)|*.txt|"
' Specify default filter

Forms!frmExtract!CommonDialog1.FilterIndex = 2
' Give the dialogue a caption title
Forms!frmExtract!CommonDialog1.DialogTitle = "Please Select the Location of The Outerface Text File"
' Set up the default directory
Forms!frmExtract!CommonDialog1.InitDir = "C:\"
' Initialise filename
Forms!frmExtract!CommonDialog1.FileName = "*.txt"

'Fix done top set the Max File Size
Forms!frmExtract!CommonDialog1.MaxFileSize = 30

' Display the Open dialog box
Forms!frmExtract!CommonDialog1.ShowOpen

'Display name of selected file
Forms![frmExtract]![txtImpFile] = Forms!frmExtract!CommonDialog1.FileName
Exit Sub

' Enable all other command buttons
cmdExit.Enabled = True
btnClearNewFlags.Enabled = True
cmdCalcMetric.Enabled = True
'cmdConvertPackSizes.Enabled = True 'v9.01
cmdCalcMargins.Enabled = True
cmdUnitPricing.Enabled = True 'v8.02

ErrHandler:
'User pressed the Cancel button
If Err = 32755 Then
cmdExit.Enabled = True
btnClearNewFlags.Enabled = True
cmdCalcMetric.Enabled = True
'cmdConvertPackSizes.Enabled = True 'v9.01
cmdCalcMargins.Enabled = True
cmdUnitPricing.Enabled = True 'v8.02
Exit Sub
Else
MsgBox Err & Error
End If
End Sub


Hope this helps.

 

[Smilie]

Thanks. I actually ended up doing it a different way. Here's the code in case it will help anyone else. This allows the user to enter the date portion of the file name. (They are always IV010105.dbf where 010105 is the date the table was created.)

Private Sub cmdImport_Click()
On Error GoTo ErrHandler

Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFolderPath As String
Dim SQL As String
Dim i As Integer

sFolderPath = "H:\"

Set oFSystem = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSystem.GetFolder(sFolderPath)

For Each oFile In oFolder.files
If oFile.Name = "IV" & Forms!frmImport.IVDate & ".DBF" Then
SQL = "Insert into [EndOfMonth]" _
& " Select """ & Left(oFile.Name, 8) & """ as [Key],*" _
& " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
& " IN """ & sFolderPath & """ ""dBASE 5.0;"""

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
i = i + 1
End If
Next

MsgBox i & " file(s) imported successfully."

Exit Sub

ErrHandler:
MsgBox Err.Description
End Sub

 

[James Dickinson]

I need my users to be able to open a macro in design mode because they are using the transfer database function to bring in a report that changes names every month. So, they need to update the source file before running it.

Is there a way to open a macro in design mode besides doing it from the main database screen??? You can open every other kind of object by saying "Docmd.OpenForm" or whatever you are trying to open...

I would love to be able to have a macro open another macro in design mode on an onclick event.

Any ideas???

Thanks in advance.

Here ya go

DoCmd.SelectObject acMacro, "MACRO NAME", True
DoCmd.RunCommand acCmdDesignObject

 

[Gasman]

16 year old thread?

 

[James Dickinson]

16 year old thread?

lol I know I know but no one answered it properly. This is for the 0.000001% of people that need this information lol