Inserting variables in VBA (Excel) from VFP

[kyletreyfield]

I am trying to launch this VBA script from VFP but I need to change the file path in the line:
xStrPath = "C:\rfportal\combine_project\files\"

This path needs to change 'on the fly' from my vfp program.

I am just launching the macro because of its complexity, I can't get it to run entirely in VFP. Here is the entire macro:

Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\rfportal\combine_project\files\"
xStrFName = Dir(xStrPath & "*")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

Thanks in advance.

 

[GriffMG]

That shouldn't be that hard to do as a automation exercise. However, if I am right in thinking this is a macro in an existing spreadsheet/workbook
surely you could pass the folder name (xStrPath) as a parameter to the routine... in your vba module

Sub MyMacro(xStrPath as String)
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrFName = Dir(xStrPath & "*")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
end sub

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

Just to expand on that and give it some kind of VFP context:

In VFP you might call it like this:

OEXCEL = CREATEOBJECT("Excel.Application")
OEXCEL.WORKBOOKS.OPEN("c:\dev\xl\MySheet.xlsm")
SELECT MYTABLE
GO TOP
DO WHILE .NOT. EOF()
	OEXCEL.RUN "MyMacro", TRIM(MYTABLE.FOLDERNAME)
	SKIP
ENDDO
OEXCEL.QUIT

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Griff, I'm not completely sure about this, but shouldn't that be:

OEXCEL.RUN("MyMacro", TRIM(MYTABLE.FOLDERNAME))

I stand corrected if I'm wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I'm not 100% sure myself! But the google search I did gave the version I used

Checked, you need the parenthesis
B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[kyletreyfield]

Thanks, Guys! This helped alot but I'm still unsure how to tell the macro where the directory name goes.

It seems like you would have to define the variable in VFP, but then can you pass it to the macro?

Thanks again.

 

[Mike Lewis]

That's exactly what we showed you - see above. You pass it as the secone argument to Excel.Run().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[kyletreyfield]

I understand, but in the macro its called "xStrPath". How does the macro know that the second argument in Excel.Run()=xStrPath ?

 

[Mike Lewis]

The macro is essentially a function (a sub in VB terminology). It receives parameters via the function header, which is the first line:

Sub MyMacro([highlight #FCE94F]xStrPath[/highlight] as String)

So, in this case, the parameter is named xStrPath. You can then use xStrPath when you want to know the path name, for example:

Workbooks.Open Filename:=[highlight #FCE94F]xStrPath[/highlight] & xStrFName, ReadOnly:=True

So how does the path name get into the paramete? It is passed by the calling program (which in this case is in VFP):

OEXCEL.RUN("MyMacro", [highlight #FCE94F]TRIM(MYTABLE.FOLDERNAME)[/highlight])

Here, TRIM(MYTABLE.FOLDERNAME) contains the path. The calling program gets it from a table ("MyTable" in Griff's example), and passes it to the function. Of course, Griff has assumed that the path name is held in a table, in a field named FolderName, but that was just to illustrate the mechanism. The point is that the VFP program knows the path name - or where to find it - and it passes it to the VB function.

I hope this makes sense. If in doubt, try it and see.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Exactly Mike, I could not have put it better

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[kyletreyfield]

It works! Stupid me didn't see that Griff had changed the original macro script and added the (xStrPath as String) to the sub name.

Both you guys (and Olaf too!) have saved my butt so many times over the years it's unbelievable. God Bless you.

 

[GriffMG]

Happy to help

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Ditto.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads