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


"Bob Barnes" wrote:

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

Is it sufficient to save the workbook as a DBF file or is there
something else required? If you simply went to the worksheet and did a
SAVEAS to DBF is that acceptable? A macro can easily be written to do
this. Or is there something else the macro need to do.


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

Microsoft Office Help

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


"joel" wrote:


Is it sufficient to save the workbook as a DBF file or is there
something else required? If you simply went to the worksheet and did a
SAVEAS to DBF is that acceptable? A macro can easily be written to do
this. Or is there something else the macro need to do.


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

Microsoft Office Help

.

Sam - this helps. Thank you - Bob

"Sam Wilson" wrote:

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


"Bob Barnes" wrote:

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

Since today is the eleventh day of the eleventh month I can't tell if
you want the date in month-day-year or day-month-year. Change as
required. Also change the name of the folder.

Sub SaveDBF()

Folder = "c:\temp\"
BaseName = "Data_" & Format(Date, "mmddyy")

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

DBaseName = Folder & BkName & ".dbf"

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

bk.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/sh...d.php?t=153064

Microsoft Office Help

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" wrote:


Since today is the eleventh day of the eleventh month I can't tell if
you want the date in month-day-year or day-month-year. Change as
required. Also change the name of the folder.

Sub SaveDBF()

Folder = "c:\temp\"
BaseName = "Data_" & Format(Date, "mmddyy")

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

DBaseName = Folder & BkName & ".dbf"

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

bk.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/sh...d.php?t=153064

Microsoft Office Help

.

The code below assumes the workbook has a header row, the workbook data
your are saving is on the 1st tab, and the Depts Names are in column A,
and there is at least one row with the deptment name "A". I 'm using
autofilter to filter the data.

Sub SaveDBF()

Folder = "c:\temp\"
BaseName = "Data_" & 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)
'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 & BkName & ".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/sh...d.php?t=153064

Microsoft Office Help

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.

"joel" wrote:


The code below assumes the workbook has a header row, the workbook data
your are saving is on the 1st tab, and the Depts Names are in column A,
and there is at least one row with the deptment name "A". I 'm using
autofilter to filter the data.

Sub SaveDBF()

Folder = "c:\temp\"
BaseName = "Data_" & 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)
'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 & BkName & ".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/sh...d.php?t=153064

Microsoft Office Help

.

You may want to setup a automatic service that performs the task. From
a command line open a workbook which as Worbook Open Event that
automatically runs the macro at night. See Excel Command Line options

http://office.microsoft.com/en-us/ex...580301033.aspx


Put the command line into a batch file (*.bat) which can be call from a
service on a PC.


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

Microsoft Office Help

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" wrote:


You may want to setup a automatic service that performs the task. From
a command line open a workbook which as Worbook Open Event that
automatically runs the macro at night. See Excel Command Line options

http://office.microsoft.com/en-us/ex...580301033.aspx


Put the command line into a batch file (*.bat) which can be call from a
service on a PC.


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

Microsoft Office Help

.

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/sh...d.php?t=153064

Microsoft Office Help

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

Thank you - Bob

"joel" wrote:


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/sh...d.php?t=153064

Microsoft Office Help

.

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" wrote:


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/sh...d.php?t=153064

Microsoft Office Help

.

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