Another Excel question... manipulating excel after export 1

[blondends]

Hi,

I have trawled through the FAQ's and searched for similar posts but can't seem to get what i need.

I am using:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, lcQueryName, strFilePathNamE, True

...to export my query data to an existing excel workbook. Then what i need to do is sort the data and then copy and paste it to another sheet in the workbook. I have managed to do similar things bofore but only when i use the line:

DoCmd.OutputTo acExport, lcQueryName, , strFilePathNamE, True, ""

...this if i am not mistaken creates a new workbook - the problem there being that i need to use an existing workbook.

In the past i have recorded a macro in Excel and then ripped the code and put it into Access VBA then placed a snipit of code before each line to make it work. But it isn't working this time.

Any ideas?

Chris Hill

 

[VBslammer]

Can't you change the query so it does the sorting before the transfer?

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[blondends]

Hi, didn't think of that - but yes. Altought that does leave me with the problem of them placing this data in to a sheet in the workbook but not on the first row. It needs to be pasted in to A12.

 

[SkipVought]

Why not query Access directly from the Excel workbook using MS Query?

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[blondends]

Do you have any links that talk about how to do this?

Thanks for the answer,

Chris

 

[SkipVought]

do a search on

"MS Query", external

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[blondends]

Ok, just thought that you might have something.

Cheers for the help,

Chris

 

[SkipVought]

Link an Access table or query to an Excel spread sheet faq68-1403

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[blondends]

Just thought that i would add the code that i have already which returns an error saying "subscript out of range". If anybody could tell me what is not quite right with this that would be great.


Dim E As Excel.Application
'Open a new instance of Excel
Set E = New Excel.Application
'Do something with Excel
With E
'use this if you wish to see excel
E.Visible = True
'Open a workbook
.Workbooks.Open (strFilePathNamE)
.Workbooks(strFilePathNamE).Worksheet(lcQueryName).Activate
'Run code
'macro code
.coloums("A:H").Select
.Selection.Sort Key1:=.Range("A2"), Order1:=xlAscending, Key2:=.Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortTextAsNumbers
.Range("I1").Select
'count records in list
.ActiveCell.FormulaR1C1 = "=COUNT(C[-8])"
countnum = .Range("I1")
countnum = countnum + 1
.Range("A2:H" & countnum).Select
.Selection.Copy
.Sheets("Profit analysis").Select
.Range("A14").Select
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'.Run "MacroName"
.ActiveWorkbook.Save
End With
'close excel
E.Quit
Set E = Nothing

 

[blondends]

SKIP,

I can't believe that i have only just found out about this excel function now!!! It works a treat and saves me coding lines and lines of script to do that same thing.

Thanks again,

Chris

 

[SkipVought]

"subscript out of range". on what line of code?

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[blondends]

I think that it is this line

.Workbooks(strFilePathNamE).Worksheet(lcQueryName).Activate

i have also had problems with line too...

.coloums("A:H").Select

Cheers,

Chris

 

[SkipVought]

does lcQueryName contain a valid sheet name???

        .Workbooks(strFilePathNamE).Worksheet([b]lcQueryName[/b]).Activate
        'Run code
        'macro code
        .[b]Columns[/b]("A:H").Select

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[blondends]

Hi Skip,

I have checked that var and it is a valid sheet name. And it is deffitly that line that is causing the error.

This is that line that comes beofre that last snipit of code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, lcQueryName, strFilePathNamE, True

Cheers,

Chris

 

[SkipVought]

Sorry...

        .Workbooks(strFilePathNamE).Worksheet[b][red]s[/red][/b](lcQueryName).Activate

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]