Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help with this Macro.. Urgent...
Hello everyone... I am very new to Excel programming. What I am trying to do is to have an excel file that will excecute a macro automatically on opening. I have the code as below. This code works just fine if I just click on it to open; however, I want to open this excel file from a .bat file that is called from a SQl query, it does not work. my SQL query calls the .bat file, and the .bat file opens this excel file; it seems to work but not really works. What I meant is that when i execute the SQL query, my query calls the .bat file, and the ..bat file really opens the excel file because I can see the MODIFIED DATE changed. The scorecard file and the scorecard_temp file also have the MODIFIED DATE changed, so I can tell that these files are really called and opened. The only problem is that if I try to open it, it does not open but says that the file is IN USE and LOCKED for EDIDTING(?)... What happens? Would somebody please help me to resolve this issue? Thank you very much for your help! **************** Sub Auto_Open() Copy_SC CopyWkBookToWkBook ActiveWorkbook.Save ActiveWindow.Close Application.Quit End Sub Sub Copy_SC() Dim FilterRange As Range Workbooks.Open Filename:= _ "\\Score_Card\Scorecard.xls" ActiveWorkbook.Worksheets("ScoreCard").Select 'For Each sh In ActiveWorkbook.Sheets ' For Each qt In sh.QueryTables 'qt.Refresh BackgroundQuery:=False 'Next qt 'Next sh ActiveWorkbook.ActiveSheet.PivotTables("PivotTable 1").PivotCache.Refresh ActiveWorkbook.Worksheets("ScoreCard").Select Range("A42:G60").Copy Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(1, 0).PasteSpecial Paste:=xlPasteValues ActiveWorkbook.Worksheets("Sheet2").Select Range("H268:I286").Copy Destination:=Worksheets("Sheet2").Range("H65536"). End(xlUp).Offset(2, 0) '.Paste Paste:=xlPasteValues 'ActiveWorkbook.Save 'ActiveWindow.Close 'Application.Quit End Sub Sub CopyWkBookToWkBook() Workbooks.Open Filename:= _ "\\Score_Card\Scorecard_temp.xls" Set SourceBk = Workbooks("Scorecard").Sheets("scorecard") Set DestinBk = Workbooks("Scorecard_temp").Sheets("Sheet1") 'SourceBk.Range("A42:G60").Copy DestinBk.Range("A1") SourceBk.Range("A42:G60").Copy DestinBk.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues ActiveWorkbook.Worksheets("Sheet2").Select SourceBk.Range("H268:I286").Copy Destination:=DestinBk.Range("H65536").End(xlUp).Of fset(2, 0) ActiveWorkbook.Save ActiveWindow.Close End Sub -- Excel_Newbie ------------------------------------------------------------------------ Excel_Newbie's Profile: http://www.excelforum.com/member.php...o&userid=28861 View this thread: http://www.excelforum.com/showthread...hreadid=489935 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO URGENT HELP | Excel Discussion (Misc queries) | |||
Urgent Macro help | Excel Programming | |||
Urgent,! Need help with this Macro | Excel Programming | |||
Macro help urgent urgent | Excel Programming | |||
Macro help urgent urgent | Excel Programming |