Merging specific cells from multiple workbooks into one master workbook

[G. L. Swientek]

This is my conundrum. I've created a complex workbook template in Mac
Excel 2004 (containing 8 named sheets, eg. "materials", "labor detail",
etc.) that I use to compose bids for my business. Each new workbook I
create from the template represents a new job and is named as the
address of that particular job, and I keep them all in a folder called
"Bids-Pending". When any of them are accepted & started, I move them to
a folder called "Bids-Accepted".

My question is, is there any way using VBA that I can create a special
worsheet in which I can consolidate & sum the totals from specific
cells within each workbook contained in my "Bids-Accepted" folder?
Specificially, I'd like to sum the totals for my Job Cost, Base Bid &
Projected Profit fields that reside on the 4th worksheet of my bids,
named Labor Detail.

Can anyone direct me to where I might find a possible solution? Any
help would be appreciated.

 

[JE McGimpsey]

This is my conundrum. I've created a complex workbook template in Mac
Excel 2004 (containing 8 named sheets, eg. "materials", "labor detail",
etc.) that I use to compose bids for my business. Each new workbook I
create from the template represents a new job and is named as the
address of that particular job, and I keep them all in a folder called
"Bids-Pending". When any of them are accepted & started, I move them to
a folder called "Bids-Accepted".

My question is, is there any way using VBA that I can create a special
worsheet in which I can consolidate & sum the totals from specific
cells within each workbook contained in my "Bids-Accepted" folder?
Specificially, I'd like to sum the totals for my Job Cost, Base Bid &
Projected Profit fields that reside on the 4th worksheet of my bids,
named Labor Detail.

Can anyone direct me to where I might find a possible solution? Any
help would be appreciated.

one way:

- Change sPATH to your folder path (including the trailing ":")
- Change the addresses for your cells to suit. Note that they need to be
in R1C1-style format.
- The substitution pattern assumes that you don't have "$$" or "%%" in
your filenames. If you do, change to another pattern in sXL4MArg and
within in the Do Loop.

Public Sub SumBidsAccepted()
Const sPATH As String = _
"HD:Users:jeocuments:Bids-Accepted:"
Const sMSG As String = _
"Total Job Cost: $$" & vbNewLine & _
"Total Base Bid: %%" & vbNewLine & _
"Total Projected Profit: ##"
Const sSHEETNAME As String = "Labor Detail"
Const sJOBCOSTADDR = "R1C1" 'Cell A1
Const sBASEBIDADDR = "R2C1" 'Cell A2
Const sPROJPROFITADDR = "R3C1" 'Cell A3
Dim sXL4MArg As String
Dim sFileName As String
Dim dJobCost As Double
Dim dBaseBid As Double
Dim dProjProfit As Double
Dim sOldDir As String

sFileName = Dir(sPATH) 'Verify path and file exist
If sFileName <> "" Then
sOldDir = CurDir
ChDir sPATH
sXL4MArg = "'" & sPATH & "[$$]" & sSHEETNAME & "'!%%"
With Application
Do
dJobCost = dJobCost + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sJOBCOSTADDR), "$$", sFileName))
dBaseBid = dBaseBid + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sBASEBIDADDR), "$$", sFileName))
dProjProfit = dProjProfit + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sPROJPROFITADDR), "$$", sFileName))
sFileName = Dir()
Loop Until sFileName = ""
MsgBox .Substitute(.Substitute(.Substitute(sMSG, _
"$$", dJobCost), "%%", dBaseBid), "##", dProjProfit)
End With
ChDir sOldDir
End If
End Sub

 

[G. L. Swientek]

Thanks for responding J. I'm trying to modify it for my purposes, but
still without much success. I guess its too complicated. This is what I
have so far:

Public Sub SumBidsAccepted()
Const sPATH As String = _
"HD:Users:Gerardesktop:Bids-Accepted:"
Const sMSG As String = _
"Total Job Cost: $$" & vbNewLine & _
"Total Base Bid: %%" & vbNewLine & _
"Total Projected Profit: ##"
Const sSHEETNAME As String = "Labor Detail"
Const sJOBCOSTADDR = "R37C2" 'Cell A1
Const sBASEBIDADDR = "R42C2" 'Cell A2
Const sPROJPROFITADDR = "R43C2" 'Cell A3
Dim sXL4MArg As String
Dim sFileName As String
Dim dJobCost As Double
Dim dBaseBid As Double
Dim dProjProfit As Double
Dim sOldDir As String

sFileName = Dir(sPATH) 'Verify path and file exist
If sFileName <> "" Then
sOldDir = CurDir
ChDir sPATH
sXL4MArg = "'" & sPATH & "[$$]" & sSHEETNAME & "'!%%"
With Application
Do
dJobCost = dJobCost + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sJOBCOSTADDR), "$$", sFileName))
dBaseBid = dBaseBid + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sBASEBIDADDR), "$$", sFileName))
dProjProfit = dProjProfit + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sPROJPROFITADDR), "$$", sFileName))
sFileName = Dir()
Loop Until sFileName = ""
MsgBox .Substitute(.Substitute(.Substitute(sMSG, _
"$$", dJobCost), "%%", dBaseBid), "##",
dProjProfit)
End With
ChDir sOldDir
End If
End Sub

Can you see what I'm missing?

 

[JE McGimpsey]

Thanks for responding J. I'm trying to modify it for my purposes, but
still without much success. I guess its too complicated. This is what I
have so far:

hard to tell without knowing what "without much success" means. Are you
getting compile errors? Run-time errors? File system errors? Incorrect
results? Crashes?

It looks like you've defined your Job Cost field as cell B37, your Base
Bid field as B42, and your Projected Profit field as B43. Is that
correct?

If it compiles OK, try stepping through the code (F8). After the

sFileName = Dir(sPATH)

line, what is the value of sFileName (type this in the Immediate Window
to find out:

?sFileName

followed by a Return. Type CMD-g to have the Immediate Window open if
it's not already).

If it's a valid filename, try stepping through the remaining code. Does
it loop through the Do...Loop?

While it looks complicated the basic structure is pretty simple:

1) See if sPATH exists and whether there are any files in it.
2) If there are, change the current directory to sPATH and
a) Grab the value of 'Labor Detail'!B37 and add it to dJobCost
b) Grab the value of 'Labor Detail'!B42 and add it to dBaseBid
c) Grab the value of 'Labor Detail'!B43 and add it to dProjProfit
d) If more files, change the filename and go to 2(a).
e) If no more files, throw up a msgbox with the results
f) Change the current directory back to what it was before

 

[G. L. Swientek]

J,

Your code worked! I followed your instructions and saw that I had a
mistake in the definition of the sPATH. Thank you so much for your
help. I simply don't have the mind for programming logic, but I'm sure
glad someone does

Just one more question: is there any way to restrict the output to
currency format? It currently displays it in regular number format to
the sixth decimal point. It really isn't that crucial of course, but I
appreciate your expertise!

 

[JE McGimpsey]

J,

Your code worked! I followed your instructions and saw that I had a
mistake in the definition of the sPATH. Thank you so much for your
help. I simply don't have the mind for programming logic, but I'm sure
glad someone does

Just one more question: is there any way to restrict the output to
currency format? It currently displays it in regular number format to
the sixth decimal point. It really isn't that crucial of course, but I
appreciate your expertise!

If you're using the MsgBox, wrap each argument with

"Format(xxx, "$#,##0.00)

e.g.,

MsgBox .Substitute(.Substitute(.Substitute(sMSG, _
"$$", Format(dJobCost, "$#,##0.00)), _
"%%", Format(dBaseBid, "$#,##0.00")), _
"##", Format(dProjProfit, "$#,##0.00"))

 

[G. L. Swientek]

Thanks J, that did the trick. Could this macro work if the workbook
containing it was on different computer and they were linked to mine by
a simple peer-to-peer AppleTalk network? I was testing this to see if I
could allow others on the network access the information in the same
way. Aside from modifying the path to be "Macintosh
HD:Volumes:gerardesktop:Bid folder:Bids-Accepted:" (which does give
me a valid file path when I test it in the Immediate window) it gives
me a runtime error '1004' when compiling it, with the path being the
only change. The debugger shows it get hung up at the Do command. Any
ideas?

 

[G. L. Swientek]

Now none of my iterations of it are working. I keep getting the same
"runtime error '1004'

The formula you typed contains an error.· To get assistance in
entering a function, click OK, then click Function on the Insert menu.
· If you are not trying to enter a formula, avoid using an equal sign
(=) or minus sign(-), or preced it with a single quotation mark (')."

And this is the code as I was using it:

Public Sub SumBidsAccepted()
Const sPATH As String = _
"Macintosh
HD:Users:Gerardesktop:Bid-folder:Bids-Accepted:"
Const sMSG As String = _
"Total Job Cost: $$" & vbNewLine & _
"Total Base Bid: %%" & vbNewLine & _
"Total Projected Profit: ##"
Const sSHEETNAME As String = "Labor Detail"
Const sJOBCOSTADDR = "R37C2" 'Cell A1
Const sBASEBIDADDR = "R42C2" 'Cell A2
Const sPROJPROFITADDR = "R43C2" 'Cell A3
Dim sXL4MArg As String
Dim sFileName As String
Dim dJobCost As Double
Dim dBaseBid As Double
Dim dProjProfit As Double
Dim sOldDir As String

sFileName = Dir(sPATH) 'Verify path and file exist
If sFileName <> "" Then
sOldDir = CurDir
ChDir sPATH
sXL4MArg = "'" & sPATH & "[$$]" & sSHEETNAME & "'!%%"
With Application
Do
dJobCost = dJobCost + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sJOBCOSTADDR), "$$", sFileName))
dBaseBid = dBaseBid + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sBASEBIDADDR), "$$", sFileName))
dProjProfit = dProjProfit + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sPROJPROFITADDR), "$$", sFileName))
sFileName = Dir()
Loop Until sFileName = ""
MsgBox .Substitute(.Substitute(.Substitute(sMSG, _
"$$", Format(dJobCost, "$#,##0.00")), "%%",
Format(dBaseBid, "$#,##0.00")), "##", Format(dProjProfit, "$#,##0.00"))
End With
ChDir sOldDir
End If
End Sub

On compiling, it keeps hanging on this expression right after "Do":

dJobCost = dJobCost + ExecuteExcel4Macro( _
.Substitute(.Substitute(sXL4MArg, "%%", _
sJOBCOSTADDR), "$$", sFileName))

 

[G. L. Swientek]

J, I think I found the problem causing my error. One of the files the
code was sampling was modified and slightly inconsistent with the
others, so it broke the code and generated that error. I redid the file
and now it is working perfectly again. Once again, thank you so much
for your help & expertise!