Problem with Excel

[Fernando]

I bought QM because with the macro utility of Microsoft Excel I did not arrive to solve an automation problem.

In fact, I have to open two datasheets and build a third one with some data taken from both of them. Datasheets’ names changes each time. The Excel macro utility records the datasheet’s names when I build a macro, and it only works whit those datasheets.

Can I solve this problem with QM? And how?

[Gintaras]

Please post the Excel macro.

[Fernando]

This is the code

Sub NewFormat()
'
' NewFormat Macro
' Macro recorded 23/10/2008 by Fernando Pescador
'

'
Application.WindowState = xlMaximized
With ActiveWindow
.Top = 5.5
.Left = 559.75
End With
Windows("BPH 1429 (HT) 2008_10_13.xls").Activate
Sheets.Add
Sheets("Weekly Prices without taxes").Select
Range("B21:G47").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B4").Select
ActiveSheet.Paste
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4:G30").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Weekly Prices without taxes").Select
Range("H21:J47").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C4").Select
ActiveSheet.Paste
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Range("C4:E30").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("BPH 1429 (TTC) 2008_10_13.xls").Activate
Range("I21:J47").Select
Selection.Copy
Windows("BPH 1429 (HT) 2008_10_13.xls").Activate
Range("D4").Select
ActiveSheet.Paste
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
Range("D4:E30").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Weekly Prices without taxes").Select
Range("K21:O47").Select
Selection.Copy
Sheets("Sheet1").Select
Range("E4").Select
ActiveSheet.Paste
Range("E3").Select
Application.CutCopyMode = False
Selection.Copy
Range("E4:I30").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("BPH 1429 (TTC) 2008_10_13.xls").Activate
Range("K21:M47").Select
Selection.Copy
Windows("BPH 1429 (HT) 2008_10_13.xls").Activate
Range("F4").Select
ActiveSheet.Paste
Range("F3").Select
Application.CutCopyMode = False
Selection.Copy
Range("F4:H30").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Weekly Prices without taxes").Select
Range("P21:R47").Select
Selection.Copy
Sheets("Sheet1").Select
Range("G4").Select
ActiveSheet.Paste
Range("G3").Select
Application.CutCopyMode = False
Selection.Copy
Range("G4:I30").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("BPH 1429 (TTC) 2008_10_13.xls").Activate
Range("N21:O47").Select
Selection.Copy
Windows("BPH 1429 (HT) 2008_10_13.xls").Activate
Range("H4").Select
ActiveSheet.Paste
Range("H3").Select
Application.CutCopyMode = False
Selection.Copy
Range("H4:I30").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

[Gintaras]

Sheets("Weekly Prices without taxes").Select

"Weekly Prices without taxes" is different each time? Replace it to sheet index, if it does not change.

This selects first worksheet:
Sheets(1).Select

[Fernando]

There are two datasheets: "weekley prices without taxes" and "weekley prices with taxes". Both of them come from "BPH 1430 (HT) 2008_10_20" and "BPH 1430 (TTC) 2008_10_20". The two last change, not the two first.

[Gintaras]

The excel macro works with several windows?

Use variables for window names.

Sub NewFormat(windowname)
...
Windows(windowname).Activate

Then call the sub from Quick Macros:
Macro

Code:

Copy      Help

str s
inp- s "Excel window name"
ExcelSheet es.Init
es.ws.Application.Run("NewFormat" s)

Excel Visual Basic also probably has a function that shows an input box. Then you would not need QM.