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

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

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

.

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