Ms Access Database & Excel - Import/export Data???

  .:º»[RjA]«º:. 15:43 09 Jan 2003
Locked
  .:º»[RjA]«º:. 15:43 09 Jan 2003

This one has really got me stumped....please help me..
I need to
Attach a button to a form in access (Called CarDetails, that displys individual data about each car) to transfer specific fields from the database (SUCarSales.mdb) to a spreadsheet (SUCarStickList.xls). The fields and references are as follows.

"RegNo" - Cells A7:A21 in excel
"StockNo" - Cells B7:B21
"Mileage" - Cells C7:C21
"Make" - Cells D7:D21
"Price" - E7:E21

I need to transfer the appropraite details from the database to the spread sheet. Also a button (in the spreadsheet) that transfers these details back to the database.

If it would be easier the files are located here:
click here

(in the speadsheet, the markup is 9%)

I realise that this is a bit specialised, but there has to be someone who has had experience woth this. Thanks agian Rja

  Sir Radfordin™ 15:57 09 Jan 2003

Sounds a bit complicated really.

You will have to make use of Macros, in Access thats a fair bit of work as it doesn't have a recorder.

No doubt someone will be able to help, I'll have a look into it though.

  .:º»[RjA]«º:. 15:59 09 Jan 2003

Thankyou.

This one has totally got me stumped.

Anyone Out there who can help?

  IanNiblock 16:08 09 Jan 2003

What version fo Access are you using - I cannot open the database file.
It should be possible for you to export the data from access to excel using OLE automation, but without being able to open the database it is slightly more difficult to be specific. I will get some sample code that you might find useful.

Ian.

  .:º»[RjA]«º:. 16:10 09 Jan 2003

Access 2002 Xp.

Thanks for this. I'm @ a loss

  Sir Radfordin™ 16:22 09 Jan 2003

If it helps the database opens fine in Access 2000.

  IanNiblock 16:29 09 Jan 2003

That would explain why I cannot open it - I only have Access 97/2000 on my machine. The code should still work the same though.

Make sure you have 'event procedure' selected for the on click event of the button and open up the visual basic editor for that procedure.

Firstly you have to go to Tools->References and make sure that the Microsoft Excel Object Library is selected (if it is not then find it in the list and check the box).

I assume that you have the SQL statement to select the records that you want to export to the spreadsheet, in the example below I have referred to that statement as SQLStatement

Here is an example you may be able to use:

Private Sub YourButton_Click()

Dim oRS AS Recordset
Dim oDB AS Database
Dim strSQL AS String
Dim oXL AS Excel.Application
Dim oWB AS Excel.Workbook
Dim oWS AS Excel.Worksheet
Dim LoopCounter AS Integer

LoopCounter = 7

Set oDB = CurrentDB
strSQL = "SQLStatement"
Set oRS = oDB.OpenRecordset(strSQL)
Set oXL = CreateObject("Excel.Application")
Set oWB = oXL.Workbooks.Open(PathToSpreadsheet)
Set oWS = oWB.ActiveSheet

If Not (oRS.EOF AND oRS.BOF) then
While Not oRS.EOF
oWS.Cells(LoopCounter, 1).Value = oRS("Reg No")
oWS.Cells(LoopCounter, 2).Value = oRS("StockNo")
etc...
LoopCounter = LoopCounter + 1
oRS.MoveNext
Wend
End If

oRS.close
Set oRS = Nothing
Set oDB = Nothing
Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

Have a look/play around with this...

I am off home now but will be back at 8am so if you have any trouble then post back and I will see what I can do

Ian.

(Unless of course, anyone has a better solution?)

  Sir Radfordin™ 16:30 09 Jan 2003

You can create a macro that will output the contents of a table/query to an excel spreadsheet.

My thinking (given I'm at work right now!) would be to create a query that contains the correct data that you want to use.

You would then need to create a macro within Excel that would copy and paste from the sheet you created in Access to the Excel sheet. Not very quick or easy to do.

Can I ask why you want to do this?

(I've worked at car dealers for 5 years and never had anything this complex!)

  IanNiblock 16:30 09 Jan 2003

Apologies for the formatting, I will fix it and post back. Will be after I get home now though.

  Sir Radfordin™ 16:31 09 Jan 2003

You can create a macro that will output the contents of a table/query to an excel spreadsheet.

My thinking (given I'm at work right now!) would be to create a query that contains the correct data that you want to use.

You would then need to create a macro within Excel that would copy and paste from the sheet you created in Access to the Excel sheet. Not very quick or easy to do.

Can I ask why you want to do this?

(I've worked at car dealers for 5 years and never had anything this complex!)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

50 best online Adobe XD tutorials

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?