Edit vba macro to paste data into specified worksheets

[Pistol3]

Hi experts

I have the following vba code which when run from a subform from ms access db is supposed to pasted the data held in the access tables into (the template workbook in excel) and into three worksheet.

Not sure what's wrong and how to correct the code:

Hi Cap1 - this was my attempt.....

Vba code is:

Private Sub export_data()

docmd set warnings false
docmd.transferspreadsheet acexport, 8, "xxx1","\\file path\final data.xls",true
docmd.transferspreadsheet acexport, 8, "xxx2","\\file path\final data.xls",true
docmd.transferspreadsheet acexport, 8, "xxx3","\\file path\final data.xls",true

Dim xlapp as excel.application
Dim xlbook as excel.application
Dim xlsheet as excel.application
Dim filepath as string
Dim my range as string

Filepath ="\\filepath\final data.xls"

Set xlapp = new excel.application
Xlapp.visible = false
Xlapp.displayalerts = false
Set xlbook = xlapp.workbook.open(filepath)
Set xlsheet = xlbook.worksheet(1).range"a1:f12"
Set xlsheet = xlbook.worksheet(2).range"b2:g10"
Set xlsheet = xlbook.worksheet(3).range"a2:h11"

Docmd.echo true,"exporting, please wait......."

Xlbook.save
Xlbook.close
Xlapp.quit

 

[sparks80]

Hi,

I am not sure if this will solve the problem, but you have set the object type for xlapp, xlbook and xlsheet to an excel application. Firstly you will need to set up these objects properly.

dim xlapp as Excel.Application
dim xlbook as Excel.Workbook
dim xlsheet as Excel.Worksheet
dim xlrange as Excel.Range

You have also tried to associate an excel range with the worksheet object, and this will not work. Firstly you need to set the xlSheet object as the given worksheet, then you need to set the range as the cells you want, e.g.

Set xlSheet = xlBook.Worksheets(1)
Set xlRange = xlSheet.Range("a1:f12")