Prev: 2003 works, 2007 doesn't: "Method 'Select' of object 'Shape'Fail
Next: Redefine a Range
From: Bob Barnes on 12 Nov 2009 11:18
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/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  | 
Pages: 1 2 3
Prev: 2003 works, 2007 doesn't: "Method 'Select' of object 'Shape'Fail
Next: Redefine a Range