Is this possible

[FoeHunter]

Not sure if this is even possible/feasible in VBA but here is idea:

Every day the list of parts that are backordered is sent out to be verified. I bump the spreadsheet against the access program I built to act as a single source for information regarding anything parts order related. On the spreadsheet from supply the parts are listed with generic names but the access program has more descriptive names to help easily identify exactly what the item in question is. For example “Bolt, Machine” would be “Aft Housing Bolt”. Clear as mud?

I would like to add a button to access that will take the value from the “txtNomenclature” text box and put that value into excel, in the cell immediately to the left that is currently selected. (i.e. cell C15 is currently selected the “Aft Housing Bolt” would be put in cell B15)

Thanks in advance

 

[mole999]

can you not just export the additional info via the sql when the parts are ordered ?

otherwise I would be thinking a vlookup

 

[Aaron Blood]

It seems as though you already have the code setup to populate data in Excel from Access... and now you are just asking how to add a value to an adjacent cell?

If C15 is currently selected, then...

Selection.Offset(0, -1).Value = n

...where n is the appropriate Access reference to the "txtNomenclature" string that you would like to drop in Excel.

Or are you having difficulty figuring out how to reference the open Excel workbook from Access?

 

[FoeHunter]

Sorry for the delay, the internet connectivity lately has been...troublesome. Aaron Blood is correct, I don't know how to make the VBA in access select excel

 

[Aaron Blood]

I see...

By default, Access is not setup to reference or "know about" the existence of Excel and you have to start by setting up the reference in the Access VBE. Thankfully, this is a very simple "put checkmark in checkbox" activity.

First open up the code editor in Access where your button code is going to reside. Presumably, this is going to be a button object that you have on an Access Form. You can add your button object to the form now if you want... maybe name it something like: "Btn_ToExcel" Then start the process to add code to the button. You should have something in your Access VBE that looks like this as a starting point:

Private Sub Btn_ToExcel_Click()

End Sub

Now, at the top of the Access VBE, select "Tools" and then "References". Scroll down to the section that starts with "Microsoft..." and look for the Microsoft Excel Object Library reference and put a checkmark next to it. In my case it's literally "Microsoft Excel 14.0 Object Library" your version number may be different. Then just click the "OK" button and now you have the complete Excel object model at your disposal in MS Access and you can add code to your button that might look like this:

Private Sub Btn_ToExcel_Click()
    Dim XLApp As Excel.Application
    Set XLApp = GetObject(, "Excel.application")
    If XLApp Is Nothing Then Exit Sub
    XLApp.ActiveCell.Offset(0, -1).Value = Me.txtNomenclature.Value
End Sub

 

[FoeHunter]

Works perfectly, thank you so much