Access and Excel

[Cruisinid]

I've created an inner-office expense form in Access but I need to be able to
create a command button/macro to open the standard company expense form
(which is in excel format) and link the values load into the appropriate
fields on the "official" excel form. I need this to be a one-step process so
that the user doesn't get frustrated.. HELP!!!

 

[Beetle]

Here is an example of some code that opens a pre-formatted Excel
spreadsheet and inserts data starting at a specified cell;

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim iRow As Integer
Dim strSQL As String
Dim rst As Recordset

strSQL = “SELECT CustomerID, CompanyName, Address, City, State†_
& “ ZipCode, PhoneNumber, FaxNumber FROM tblCustomers†_
& “ ORDER BY tblCustomers.CompanyName;â€

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount = 0 Then
MsgBox “There is nothing to transferâ€
Exit Sub
End If

Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("Path to your Excel file goes here")
Set objSht = objWkb.Worksheets(1)

iRow = 7 ‘ set this to the row number you want to start at in the spreadsheet

rst.MoveFirst

Do While Not rst.EOF
' modify the column letters as needed
objSht.Range("B" & iRow) = rst!CustomerID
objSht.Range("C" & iRow) = rst!CompanyName
objSht.Range("D" & iRow) = rst!Address
objSht.Range("E" & iRow) = rst!City
objSht.Range("F" & iRow) = rst!State
objSht.Range("G" & iRow) = rst!ZipCode
objSht.Range("H" & iRow) = rst!PhoneNumber
objSht.Range("I" & iRow) = rst!FaxNumber
iRow = iRow + 1
rst.MoveNext
Loop

exit_procedure:
rst.Close
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rst = Nothing

 

[Cruisinid]

I am REALLY green and have no idea what any of that means, but it looks
awesome!! Can I just copy that somewhere into my access program and then
adjust the variables? (That being said, I'm not sure which variables I can
change

THank you thank you!!!
Cheers,
D

 

[Beetle]

Can I just copy that somewhere into my access program and then

adjust the variables?

To a certain extent, yes. The first thing you would want to do is open
a code window and go to Tools/References and make sure you have a
reference set to the Microsoft Excel Object Library.

Next you would need to modify the SQL statement to pull the appropriate
fields from whichever table you want. You would also need to modify the
recorset field names.

Last, you would need to put in the path to your Excel file and set the
appropriate row and column number where you want the data input
to start.

If you need help post back. If you do post back for help, make sure to include
the following;

The name of the table the data is stored in

The name of each field in that table that you want to reference

The name of the path to your Excel file

The cell (A3, B4, whatever) in the Excel file where you want the data transfer
to start

 

[Cruisinid]

Thanks for the help!

The name of the table that the data will be stored in is called
ExpenseClaimTable. It will be stored in the D drive in the Dawn folder.

The name of each fields that I want referenced from this table are:
? don't know if I need an itinerary number to pull up the specific record,
if not, there is no use for this field here.

First sheet: "GC74 - PLEASE COMPLETE FIRST"
* Departure Date [FieldCallItinerary] A9
* Departure Time [FieldCallItinerary] B9
* Distance travelled [ExpenseClaimTable] G9
* Parking expenses [ExpenseClaimTable] Q9
* Other (travel) [ExpenseClaimTable] T9
* breakfast [ExpenseClaimTable] I9
* lunch [ExpenseClaimTable] J9
* dinner [FieldCallItinerary] K9
* other (meal) [ExpenseClaimTable] L9
* registration fee this is added to the "Other misc" [ExpenseClaimTable] L9
* other (misc) [ExpenseClaimTable] L9 [ANYTHING EXTRA GOES IN L9]
* First name, Last name (these are in separate field but need together here)
[Officers] D4

Second sheet: some data will autofill from the page above "Travel Claim"
* Name (auto from above)
* PRI [Officers] E5
* Work Phone [Officers] E7
* StreetAddress [Officers] E8
* City [Officers] E9
* Province [Officers] E10
* Postal Code (need province and postal code to appear on same line)
[Officers]
* Bank [Officers] i7
* Home [Officers] I9
* Time leave am (H14) or PM (h15) (this isn't set up in my database but it
is on the form
* Time return AM (H16) or PM (H17) (this isn't set up on my database but it
is on the form
* Notes [FieldCallItinerary] E20
* City (this is the destination) [FieldCallItinerary] E21
* km travelled (should copy from cell above) G30
* breakfast (should copy from cell above) G35
* lunch (should copy from cell above) G36
* dinner (should copy from above) G37
* Other (misc) (should copy from above) G38



The path to the excel file is D drive, Dawn folder, ExpenseClaims subfolder.
I'm not sure about the last question so I will just put the cell number of
the excel spreadsheet that it is supposed to go to..

Sorry, tried to be as organized as possible..
Cheers!

 

[Beetle]

I'm not really sure how to interpret what you posted, but as far as I can tell
you want to take data from multiple tables in your Access db and insert it
into multiple Excel worksheets in various non-contiguous cells.

If that's the case, the code I posted earlier is not quite approriate. It
could
probably be adapted to work, but you would have to hard code each cell
value and switch worksheets at some point in the code.
--
_________

Sean Bailey

Thanks for the help!

The name of the table that the data will be stored in is called
ExpenseClaimTable. It will be stored in the D drive in the Dawn folder.

The name of each fields that I want referenced from this table are:
? don't know if I need an itinerary number to pull up the specific record,
if not, there is no use for this field here.

First sheet: "GC74 - PLEASE COMPLETE FIRST"
* Departure Date [FieldCallItinerary] A9
* Departure Time [FieldCallItinerary] B9
* Distance travelled [ExpenseClaimTable] G9
* Parking expenses [ExpenseClaimTable] Q9
* Other (travel) [ExpenseClaimTable] T9
* breakfast [ExpenseClaimTable] I9
* lunch [ExpenseClaimTable] J9
* dinner [FieldCallItinerary] K9
* other (meal) [ExpenseClaimTable] L9
* registration fee this is added to the "Other misc" [ExpenseClaimTable] L9
* other (misc) [ExpenseClaimTable] L9 [ANYTHING EXTRA GOES IN L9]
* First name, Last name (these are in separate field but need together here)
[Officers] D4

Second sheet: some data will autofill from the page above "Travel Claim"
* Name (auto from above)
* PRI [Officers] E5
* Work Phone [Officers] E7
* StreetAddress [Officers] E8
* City [Officers] E9
* Province [Officers] E10
* Postal Code (need province and postal code to appear on same line)
[Officers]
* Bank [Officers] i7
* Home [Officers] I9
* Time leave am (H14) or PM (h15) (this isn't set up in my database but it
is on the form
* Time return AM (H16) or PM (H17) (this isn't set up on my database but it
is on the form
* Notes [FieldCallItinerary] E20
* City (this is the destination) [FieldCallItinerary] E21
* km travelled (should copy from cell above) G30
* breakfast (should copy from cell above) G35
* lunch (should copy from cell above) G36
* dinner (should copy from above) G37
* Other (misc) (should copy from above) G38



The path to the excel file is D drive, Dawn folder, ExpenseClaims subfolder.
I'm not sure about the last question so I will just put the cell number of
the excel spreadsheet that it is supposed to go to..

Sorry, tried to be as organized as possible..
Cheers!

To a certain extent, yes. The first thing you would want to do is open
a code window and go to Tools/References and make sure you have a
reference set to the Microsoft Excel Object Library.

Next you would need to modify the SQL statement to pull the appropriate
fields from whichever table you want. You would also need to modify the
recorset field names.

Last, you would need to put in the path to your Excel file and set the
appropriate row and column number where you want the data input
to start.

If you need help post back. If you do post back for help, make sure to include
the following;

The name of the table the data is stored in

The name of each field in that table that you want to reference

The name of the path to your Excel file

The cell (A3, B4, whatever) in the Excel file where you want the data transfer
to start
--

 

[Cruisinid]

Yes, the information is contained on one form but comes form multiple tables
in the same database. I thought that's how Access is supposed to be set up
so that information isn't duplicated, that it's all sourced via the primary
key? I am sort of lost on how to proceed..? I would be very open to any
suggestions.

I'm not really sure how to interpret what you posted, but as far as I can tell
you want to take data from multiple tables in your Access db and insert it
into multiple Excel worksheets in various non-contiguous cells.

If that's the case, the code I posted earlier is not quite approriate. It
could
probably be adapted to work, but you would have to hard code each cell
value and switch worksheets at some point in the code.
--
_________

Sean Bailey

Thanks for the help!

The name of the table that the data will be stored in is called
ExpenseClaimTable. It will be stored in the D drive in the Dawn folder.

The name of each fields that I want referenced from this table are:
? don't know if I need an itinerary number to pull up the specific record,
if not, there is no use for this field here.

First sheet: "GC74 - PLEASE COMPLETE FIRST"
* Departure Date [FieldCallItinerary] A9
* Departure Time [FieldCallItinerary] B9
* Distance travelled [ExpenseClaimTable] G9
* Parking expenses [ExpenseClaimTable] Q9
* Other (travel) [ExpenseClaimTable] T9
* breakfast [ExpenseClaimTable] I9
* lunch [ExpenseClaimTable] J9
* dinner [FieldCallItinerary] K9
* other (meal) [ExpenseClaimTable] L9
* registration fee this is added to the "Other misc" [ExpenseClaimTable] L9
* other (misc) [ExpenseClaimTable] L9 [ANYTHING EXTRA GOES IN L9]
* First name, Last name (these are in separate field but need together here)
[Officers] D4

Second sheet: some data will autofill from the page above "Travel Claim"
* Name (auto from above)
* PRI [Officers] E5
* Work Phone [Officers] E7
* StreetAddress [Officers] E8
* City [Officers] E9
* Province [Officers] E10
* Postal Code (need province and postal code to appear on same line)
[Officers]
* Bank [Officers] i7
* Home [Officers] I9
* Time leave am (H14) or PM (h15) (this isn't set up in my database but it
is on the form
* Time return AM (H16) or PM (H17) (this isn't set up on my database but it
is on the form
* Notes [FieldCallItinerary] E20
* City (this is the destination) [FieldCallItinerary] E21
* km travelled (should copy from cell above) G30
* breakfast (should copy from cell above) G35
* lunch (should copy from cell above) G36
* dinner (should copy from above) G37
* Other (misc) (should copy from above) G38



The path to the excel file is D drive, Dawn folder, ExpenseClaims subfolder.
I'm not sure about the last question so I will just put the cell number of
the excel spreadsheet that it is supposed to go to..

Sorry, tried to be as organized as possible..
Cheers!

Can I just copy that somewhere into my access program and then
adjust the variables?

To a certain extent, yes. The first thing you would want to do is open
a code window and go to Tools/References and make sure you have a
reference set to the Microsoft Excel Object Library.

Next you would need to modify the SQL statement to pull the appropriate
fields from whichever table you want. You would also need to modify the
recorset field names.

Last, you would need to put in the path to your Excel file and set the
appropriate row and column number where you want the data input
to start.

If you need help post back. If you do post back for help, make sure to include
the following;

The name of the table the data is stored in

The name of each field in that table that you want to reference

The name of the path to your Excel file

The cell (A3, B4, whatever) in the Excel file where you want the data transfer
to start
--