Please Help with this Macro.. Urgent...

E

Excel_Newbie

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("PivotTable1").PivotCache.Refresh

ActiveWorkbook.Worksheets("ScoreCard").Select

Range("A42:G60").Copy
Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(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).Offset(2, 0)

ActiveWorkbook.Save
ActiveWindow.Close

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top