Run Code in Another Workbook

  • Thread starter Thread starter Bob Barnes
  • Start date Start date
B

Bob Barnes

Looking for automation code to have in a "Template" Workbook
that will run code in an Excel File received from Management.

This code would make a DBF from the Excel File received from Management.

TIA - Bob
 
The following looks for an open workbook called test.xls, finds a sheet
called "Sheet1" in it and colours cell A1 light blue. It can be placed in any
other workbook & will still work:

Sub demo()

dim wb as workbook
set wb = workbooks("test.xls")

dim ws as worksheet
set ws = wb.worksheets("Sheet1")

ws.range("A1").interior.colorindex = 37

end sub

You can use activeworkbook etc rather than setting wb to a pre-named
workbook etc.

Sam
 
Joel - It's a Daily Excel file including data for many Depts, including our
Dept.

I can easily do that manually, but as a Contractor, I'm not here every day.
I want to have a regular Mgr here run a macro to make the DBF when I'm not
here.

I'd like a "template" Excel File that will Find the Daily
File..IE..Data_111109, and then make it a DBF. I saw an Archive Post you had
in Sep this year which will help, but needed automation code to another Excel
file.

Thank you - Bob
 
Sam - this helps. Thank you - Bob

Sam Wilson said:
The following looks for an open workbook called test.xls, finds a sheet
called "Sheet1" in it and colours cell A1 light blue. It can be placed in any
other workbook & will still work:

Sub demo()

dim wb as workbook
set wb = workbooks("test.xls")

dim ws as worksheet
set ws = wb.worksheets("Sheet1")

ws.range("A1").interior.colorindex = 37

end sub

You can use activeworkbook etc rather than setting wb to a pre-named
workbook etc.

Sam
Click to expand...
 
Thank you Joel.

I'm an Access Developer, and use Excel usually only thru
Access-to-Excel-automation.

I'm adding this to my "Knowledge File".

BTW...is there a way to Filter only those Records which, IE, below to Dept
"A", even thought the Excel file contains Depts "A", "B", "C"......"L"

TIA - Bob
 
Joel - THANK you.

I'm a Contract Access Programmer for a Large Dept and am fortunate to have
several Projects for several Mgrs here.

I'm meeting later today w/ the Mgr interested in doing this. I'll try this,
run it by the Mgr, and Post here again late Afternoon today (I'm East Coast
time).

Thanks again.
 
The Excel file received has 2 Worksheets...the 2nd Sheet is what's needed,
and the Depts are in Column B. It has a Header Row. It's a Daily File w/ 18
Columns
w/ about 1000 Rows of data.

Eventually I'd like to have the Mgr run the Subroutine which would be stored
in an Access database. An Access dropdown would select the Date which is
part of the Filename, IE..110409_DailyStores.xls.

Could look into the .bat, but the Mgr would be happy selecting from Access
where we call Excel automation a lot.

Joel - I'm getting Error 1004 (won't even Step thru) from the Sub below..

Even tried adding..
Dim Folder$, BkName$
Dim bk As Workbook

Private Sub SaveGoDBF()
Folder = "c:\BobDev\"
BaseName = "Stores_" & Format(Date, "mmddyy")

BkName = Folder & BaseName & ".xls"
Set bk = Workbooks.Open(Filename:=BkName)
'create workbook to copy filtered data
Set bk2 = Workbooks.Add(template:=xlWBATWorksheet)

'With bk.Sheets(1)

'11/12/09 - For 2nd Tab
With bk.Sheets(2)
'select autofilter to select Dept A
'11/12/09 - For 2nd Tab
'select autofilter to select Dept A
'LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'.Columns("A:A").AutoFilter
'.Columns("A:A").AutoFilter Field:=1, Criteria1:="A"
'copy only filtered rows
'.Rows("1:" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
' bk2.Sheets(1).Rows(1)
'11/12/09 - Modified for 2nd Column & Plastics
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Columns("B:B").AutoFilter
.Columns("B:B").AutoFilter Field:=1, Criteria1:="Plastics"
'copy only filtered rows
.Rows("1:" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
bk2.Sheets(1).Rows(1)
End With

'create new filename to save file
DBaseName = Folder & BkName & ".dbf"

bk2.SaveAs Filename:=DBaseName, _
FileFormat:=xlDBF4

bk.Close savechanges:=False
bk2.Close savechanges:=False

End Sub
 
Joel - I will get a chance to test this tonite, and Post here again before 8
PM Eastern time.

Thank you - Bob
 
Joel - It's BEAUTIFUL...here's the code I modified.

Now I need to run the code thru a Dropdown in Access to Select the "mmddyy"
Date format.

Private Sub SaveGoDBF()
Dim Folder$, BkName$, CCName$
Dim bk As Workbook
Stop
On Error GoTo AAA1
Folder = "c:\BobDev\"

BaseName = "110409_Daily"

BkName = Folder & BaseName & ".xls"

Set bk = Workbooks.Open(Filename:=BkName)
'create workbook to copy filtered data
Set bk2 = Workbooks.Add(template:=xlWBATWorksheet)

With bk.Sheets(2)
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Columns("B:B").AutoFilter
.Columns("B:B").AutoFilter Field:=1, Criteria1:="Plastics"
'copy only filtered rows
.Rows("1:" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
bk2.Sheets(1).Rows(1)
End With

'create new filename to save file
CCName = Folder & BaseName

bk2.SaveAs Filename:=CCName, _
FileFormat:=xlDBF4

bk.Close savechanges:=False
bk2.Close savechanges:=False
AAA2:
Exit Sub
AAA1:
Select Case Err
Case Else
MsgBox "Error Number " & Err.Number & " " & Err.Description
Resume AAA2
End Select
End Sub

joel said:
the code below works ok in 2003. I made one minor change

from
DBaseName = Folder & BaseName & ".dbf"

to
DBaseName = Folder & BkName & ".dbf"

The problem above create a filename with and extension .xls.dbf instead
of .dbf


The SAVEAS has a problem when the workbooks contains more than one
sheet. The line "template:=xlWBATWorksheet" creates a workbook with one
sheet. You other changes made the code create non-related errors. The
1004 error was due to the fact workbook that you opened had more than
one sheet. That is why I copied the the filtered sheet to a new
workbook before saving as a dbf file.

Sub SaveDBF()

Folder = "c:\BobDev\"
BaseName = "Stores_" & Format(Date, "mmddyy")

BkName = Folder & BaseName & ".xls"
Set bk = Workbooks.Open(Filename:=BkName)

'create workbook to copy filtered data
'Original Source: The Code Cage Forums http://tinyurl.com/yhtubl7
Set bk2 = Workbooks.Add(template:=xlWBATWorksheet)

With bk.Sheets(1)
'select autofilter to select Dept A
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("A:A").AutoFilter
.Columns("A:A").AutoFilter Field:=1, Criteria1:="A"
'copy only filtered rows
.Rows("1:" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
bk2.Sheets(1).Rows(1)
End With

'create new filename to save file
DBaseName = Folder & BaseName & ".dbf"


bk2.SaveAs Filename:=DBaseName, _
FileFormat:=xlDBF4

bk.Close savechanges:=False
bk2.Close savechanges:=False
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=153064

Microsoft Office Help

.
Click to expand...
 
Joel - Column L in the DBF is what makes the Row (Record) Unique.

Column L in the Daily excel file is a "mixture" of Entries w/ 9 or 10
characters. I know when making a DBF Manually from Excel, that Excel looks
at the first few rows to "guess" what DataType it is, and number of
characters.

Working w/ this particular file, Excel has to be "guessing" it's 9
characters instead of the actual (example) 10 characters below.

MSR0332476
MSR0332489
MSR0332488
MSR0332486

So...the DBF results in...
MSR033247
MSR033248 --> Can't be a Primary Key
MSR033248 --> Can't be a Primary Key
MSR033248 --> Can't be a Primary Key

Is there a way to use VBA to set the number of characters
in the DBF's Column L to 10-characters?

TIA - Bob
 
Back
Top