Prev: 2003 works, 2007 doesn't: "Method 'Select' of object 'Shape'Fail
Next: Redefine a Range
From: joel on 11 Nov 2009 15:59

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

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: Bob Barnes on 11 Nov 2009 17:59
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/showthread.php?t=153064
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>
From: joel on 11 Nov 2009 19:53

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

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: Bob Barnes on 12 Nov 2009 09:38
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/showthread.php?t=153064
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>
From: joel on 12 Nov 2009 10:03

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/excel/HA101580301033.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/showthread.php?t=153064

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: 2003 works, 2007 doesn't: "Method 'Select' of object 'Shape'Fail
Next: Redefine a Range