Manipulate Excel from Access

[RascalBird]

Hi there

I am trying to create a module for a client and have run into a wall.

They have an existing spreadsheet with data in the form of

12345 ABC Location | 203049 | Small

I need to copy the three columns of data and perform a PASTE SPECIAL into a new workbook then save it as data.xls into the same folder the database is located in

Then I need to append the information to an existing table in excel

Unfortunately I can't put the macro code for manipulating the data into the spreadsheet (long story) so I really need to do it from Access

Can anyone give me any help on how this can be done????

Thank you in advance for any help

 

[smercer]

Have you tried having a link by going file-->Get External Data-->link Tables. You then go down to "files of type" and choose "Microsoft Excel(*.xls)". You can then change the data as you would as in any table. even from a form

Or you could just import the whole thing and there would be no link (and no data updating either).

if these are not your cup of tea, please tell us the long story

 

[RascalBird]

Hi There

Unfortunately a link is not appropriate as I need to retrieve data from a pivot table within Excel - hence the copying the data and using paste special into a new file

I have started hard coding the module I need but for some reason it won't work

Private Sub matData()

'-------Declare 
Dim lngColumn As Long
Dim xlx As Object, xls As Object, xlc As Object, xlw As Object

Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\access\matlevels.xls") ',,True
Set xls = xlw.Worksheets("sheet1")

'--- copy and paste data into new workbook then save -----------------------------

Set xlc = xls.range("A9")
Set xls = xls.range(selection, selection.end(xltoright)).select
Set xlc = xlc.range(selection, selection.end(xldown)).select

any thoughts as to what I'm doing wrong???

At the moment after it opens the excel file it won't change the active cell or even the worksheet!

 

[smercer]

The only other suggestion is that having a variable "xls" could be a reserved word that visual basic needs.
try another name.

If that didn't work, good luck.

 

[RoyVidar]

The method you use, does not select or activate any cells, but the objects you've instantiated, refers to a cell, worksheet and workbook, so using for instance:

xls.cells(9,1).value="something"

Would probably populate A9 with "something" on the sheet.

How do you intend to populate the area? I suppose opening a recordset and either looping thru the rows, or use the copyfromrecordset method of the excel range object could be used?

 

[RascalBird]

At the moment I am not trying to populate the area, merely select the data from A9 down to the bottom of the populated area -1 row but unfortunately I can't get the active cell to move anywhere

I have tried it without the variable name XLS without any success

From what I have seen of other snippets of code it *should* work........ unless there is some other syntax I'm missing......

 

[RoyVidar]

If we knew a little about the purpose of why you need to perform a selection, then we can perhaps assist better. I don't think you'll be able to use Paste Special from Access with much luck (but I've never even tried, I prefer more control when dumping data to excel, and use the methods I described in the last reply).

 

[fpendino]

Private Sub matData()

'-------Declare 
Dim lngColumn As Long
Dim xlx As Object, xls As Object, xlc As Object, xlw As Object

Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\access\matlevels.xls") ',,True
Set xls = xlw.Worksheets("sheet1")

'--- copy and paste data into new workbook then save -----------------------------
Set xlc = xls.range("A9")
Set xls = xls.range(selection, selection.end(xltoright)).select
Set xlc = xlc.range(selection, selection.end(xldown)).select

any thoughts as to what I'm doing wrong???

At the moment after it opens the excel file it won't change the active cell or even the worksheet![/QUOTE]



Try doing this instead...

Dim lngColumn As Long
Dim xl As Object

Set xl = CreateObject("Excel.Application")
xl.Visible = True
xl.Workbooks.Open("C:\access\matlevels.xls") ',,True
xl.sheets("sheet1").Select

'--- copy and paste data into new workbook then save -----------------------------
xl.Range("A2:A11").Select
xl.Selection.Copy
xl.Range("D2").Select
xl.ActiveSheet.Paste
xl.Application.CutCopyMode = False

 

[RoyVidar]

Ouch, that I see now - you're intending to copy within Excel - gee I need new glasses.

Just to put in another "flavour", I like to use explicit referencing of the workbook and worksheet objects. If the info is to be pasted into another/new workbook, I'd explicitly declare and use that too.

Dim xl As Object, sh1 As Object, sh2 As Object, wr As Object

on error resume next
set xl=getobject(,"excel.application")
if err.number<>0 then
err.clear
Set xl = CreateObject("Excel.Application")
if err.number<>0 then exit sub ' is excel installed?
end if
on error goto <your error handler>
'xl.Visible = True
Set wr = xl.Workbooks.Open("C:\access\matlevels.xls")
Set sh1 = wr.Worksheets("sheet1")
Set sh2 = wr.Worksheets("sheet2")

'--- copy and paste data into new workbook then save -----------------------------
sh1.range("A9").select
sh1.range(xl.selection, xl.selection.end(xldown)).select
sh1.range(xl.selection, xl.selection.end(xltoright)).select
xl.selection.copy
sh2.activate
sh2.range("a2").select
sh2.paste
' an alternative for the two last lines
sh2.range("a2").pastespecial <add your choice xlpastevalues?>

 

[RascalBird]

Thanks for that Roy, I appreciate it. I still seem to be missing something unfortunately.

What is wrong with this code? I can't see that anything is being copied let alone pasted or saved in the new workbook






Private Sub Test()



Dim xl As Object, sh1 As Object, sh2 As Object, wr As Object, wrNew As Object

On Error Resume Next
Set xl = GetObject(, "excel.application")
If Err.Number <> 0 Then
Err.Clear
Set xl = CreateObject("Excel.Application")
If Err.Number <> 0 Then Exit Sub ' is excel installed?
End If

Set wr = xl.Workbooks.Open("C:\access\matlevels.xls", , True)
Set sh2 = wr.Worksheets("sheet1")

Set wrNew = xl.Workbooks.new

'--- copy and paste data into new workbook then save -----------------------------
sh1.range("A9").select
sh1.range(xl.selection, xl.selection.End(xldown)).select
sh1.range(xl.selection, xl.selection.End(xltoright)).select
xl.selection.copy
wrNew.Activate

wrNew.range("a2").pastespecial paste:=xlpastevalues

xl.cutcopymode = False

wrNew.saveas filename:="C:\access\tempdata.xls"

wrNew.Close True
wr.Close False

xl.Quit

Set sh1 = Nothing
Set sh2 = Nothing
Set xl = Nothing
Set wrNew = Nothing

End Sub

 

[RoyVidar]

Take a look at which workbook/sheet you are copying from and to, and also the methods of the Workbook object. Even if one are determined to use late binding, it is often easier to use early binding when developing, then switch to late binding when deploying.

For late binding, you my need to replace the excel constants with their value (xlpasteformats -4122, xldown -4121, xltoright -4161)

Dim xl As Object, sh1 As Object, sh2 As Object, wr As Object, wr2 as object

on error resume next
set xl=getobject(,"excel.application")
if err.number<>0 then
err.clear
Set xl = CreateObject("Excel.Application")
if err.number<>0 then exit sub ' is excel installed?
end if
on error goto <your error handler>
'xl.Visible = True
Set wr = xl.Workbooks.Open("C:\access\matlevels.xls")
set wr2 = xl.Workbooks.Add
Set sh1 = wr.Worksheets("sheet1")
Set sh2 = wr2.Worksheets("sheet1")

'--- copy and paste data into new workbook then save -----------------------------
sh1.range("A9").select
sh1.range(xl.selection, xl.selection.end(xldown)).select
sh1.range(xl.selection, xl.selection.end(xltoright)).select
xl.selection.copy
sh2.activate
sh2.range("a2").select
sh2.paste
' an alternative for the two last lines
sh2.range("a2").pastespecial <add your choice xlpastevalues?>
wr2.saveas "C:\access\tempdata.xls"
doevents
wr2.Close True
wr.Close False
xl.cutcopymode = False
set sh1=nothing
set sh2=nothing
set wr=nothing
set wr2=nothing
'xl.quit
set xl=nothing

 

[RascalBird]

*bangs head on desk* missing that keyword of ADD just isn't forgivable


Am I missing something equally as stupid for why I can't copy or paste any data? at the moment I'm getting a blank workbook saved

 

[RascalBird]

BY JOVE I GOT IT!

I used

wr1.Activate

sh1.Activate
sh1.Range("a9").Select

Range(Range("a9").End(xlDown), Range("a9").End(xlToRight)).Select

to get it working messy but it works


OK I'm ALMOST there

Only problem is when I go to save the workbook it tells me the document with the name "tempdata.xls" already exists

I can't find the argument to force it to say yes without showing the prompt - any thoughts???

 

[RoyVidar]

Aaarrrrgh - I had some equivalent of that in my code, but forgot to include it - sorry

Hm - saveas when the file exists, must confess I'm of the type to either
* delete the file first - or
* alter the file name (adding version numbers etc)

(and no, I won't mention the SendKeys)

For the first, try just

on error resume next
Kill "C:\access\tempdata.xls"
if err.number<>0 then
' file wasn't found
err.clear
end if
on error goto <your error handler>

Or use the FileExists method of the FileSystemObject to determine whether it exists, and then issue the DeleteFile method of the same object.

 

[RascalBird]

That was perfect THANK YOU!


Unfortunately I am at the finishing touches and am trying to perform an excel function which seems to run fine but no results are being placed into the target cell. What am I missing THIS time?


'convert the spreadsheet data to a readable format
sh2.Range("a1").Select

Range("a1") = WorksheetFunction = Left(e2, WorksheetFunction.Search(" ", e2, 1))


Range("a2").Select

Range(Range("a1").End(xlDown)).FillDown

 

[reclusivemonkey]

Try using Application.WorksheetFunction rather than just WorksheetFunction.