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 Autpen()
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
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 Autpen()
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