Import a macro file from C:\Drive using a macro?

[vane0326]

I created three macros in one module Then I saved it my C:\Drive.

My problem is I tried to record a macro to go to my visual basic editor then right click on ThisWorkbook then import file then I go to my C:\Drive then I click on Inventory Macro.bas then I assign the macros to the buttons.

But it does not work is there a special code for this?

Thanks!

 

[chiello]

Try the following code:

Sub ImportOneModule()
Dim FName As String
FName = "C:\Drive\Inventory Macro.bas"
ThisWorkbook.VBProject.VBComponents.Import FName 'If this code is not whithin the workbook where you want to import the module, replace ThisWorkbook with Workbooks("FileName")
End Sub

See http://www.cpearson.com/excel/vbe.htm for more information
Ciao

 

[vane0326]

I found this code BUT the only wrong with it it goes to the personal.xls I want it to go to the VBAProdject(Negative Inventory as of 09-10-2004.xls)
workbook But the file name changes can it go in with a wild card like a ? mark or a *

Example: VBAProdject(Negative Inventory?.xls)
OR
VBAProdject(Negative Inventory*.xls)




Sub CopyModulesFromA()

Dim FS As Office.FileSearch

Dim vaFileName As Variant



Set FS = Application.FileSearch



With FS

'clear old search criteria

.NewSearch

'directory to search

.LookIn = "C:"

'look for .bas (Module) files

.Filename = "*Inventory.bas"

'carry out search

.Execute

For Each vaFileName In .FoundFiles

MsgBox "About to import module " & vaFileName

ThisWorkbook.VBProject.VBComponents.Import Filename:=vaFileName

Next vaFileName

End With



End Sub

 

[chiello]

it goes to the personal.xls I want it to go to the VBAProdject(Negative Inventory as of 09-10-2004.xls)

If "Negative Inventory as of 09-10-2004.xls" is the name of the destination file of your import operation, then you have to substitute

ThisWorkbook.VBProject.VBComponents.Import Filename:=vaFileName

with

Workbooks("Negative Inventory as of 09-10-2004.xls").VBProject.VBComponents.Import Filename:=vaFileName

 

[vane0326]

It works but my file changes daily meaning

Negative Inventory as of 09-10-2004.xls

then the next day

Negative Inventory as of 09-11-2004.xls

and the next

Negative Inventory as of 09-12-2004.xls

and so on

The date changes daily. Can the code search for a wild card with a ? or *?
I tried to modify it
Workbooks("Negative Inventory?.xls").VBProject.VBComponents.Import Filename:=vaFileName

OR

Workbooks("Negative Inventory*.xls").VBProject.VBComponents.Import Filename:=vaFileName

But it does not work. Any ideas?

 

[chiello]

Try this


Sub CopyModulesFromA()

Dim wbFileName As String
Dim FS As Office.FileSearch
Dim vaFileName As Variant

wbFileName = Application.GetOpenFilename("XLS Files (*.xls), *.xls")
If wbFileName <> False Then
Set FS = Application.FileSearch
With FS
'clear old search criteria
.NewSearch
'directory to search
.LookIn = "C:"
'look for .bas (Module) files
.Filename = "*Inventory.bas"
'carry out search
.Execute
For Each vaFileName In .FoundFiles
MsgBox "About to import module " & vaFileName
Workbooks(wbFileName).VBProject.VBComponents.Import Filename:=vaFileName
Next vaFileName
End With
End If
End Sub

Ciao

 

[chiello]

Sorry..

Replace "Dim wbFileName As String"
With "Dim wbFileName As Variant"

 

[vane0326]

It did'nt not work out it open to my documents.
You know how like in excel you go to FILE the click on OPEN thats were this code sends me.

Any Ideas?

Thanks!

 

[vane0326]

now I notice it goes to my personal.xls still.

Any Ideas?

Thanks!

 

[chiello]

Copy the code below and note:

1) Run Macro with destination Workbook CLOSED
2) When the Open window appears, you can browse your file system and select the file you want

Code:

Sub CopyModulesFromA()

Dim wbFileName As Variant
Dim FS As Office.FileSearch
Dim vaFileName As Variant

wbFileName = Application.GetOpenFilename("XLS Files (*.xls), *.xls")
If wbFileName <> False Then
Application.ScreenUpdating = False
Workbooks.Open Filename:=wbFileName
Set FS = Application.FileSearch
With FS
'clear old search criteria
.NewSearch
'directory to search
.LookIn = "C:"
'look for .bas (Module) files
.Filename = "*Inventory.bas"
'carry out search
.Execute
For Each vaFileName In .FoundFiles
'MsgBox "About to import module " & vaFileName
ActiveWorkbook.VBProject.VBComponents.Import Filename:=vaFileName
Next vaFileName
End With
ActiveWorkbook.Save 'Save
ActiveWindow.Close 'Close: comment if you want
Application.ScreenUpdating = True
End If
End Sub