Populated excel spreadsheet with access database

[buju03]

Hello, i have a project i am working. i need to send spreadsheets out to distributors, i need to populate there information on the spreadsheet: distributor, material, site, tradename. our database in access has all these information. what is the proper way to link the database to the spreadsheet, so the information needed(distributor, material, site, tradename) can populate itself out by distributor(or whenever i pick one of the distributor from a dropdown the rest populated themself or i run a macro/vba where there is query run for each distributor then populated the spreadsheet w/o alot of back forth between access and excel). please help me, i may sound confusing please let me know if i confuse you guys anywhere in my explanation.

thanks,
Mike

 

[Norie]

I don't think you could say there's a 'proper way's to do this sort of thing.

There are different ways depending on what you actually want to do and what data you want to export.

I suppose one if the simplest ways would be to something like this:

Set up a parameter query to return the data for the distributors.

The parameter can be setup to come from a form.

Create a form with a combobox listing the distributors and a command button.

In the click event of the button add code to export the query to Excel using DoCmd.TransferSpreadsheet.

That's one method.

 

[buju03]

Okay, your suggestions sounds easy but i am having a hard making it do exactly what i want. so basically this is the scenario:

i have an excel spreadsheet/form i want to send out to distributors, so the distributors can fill out the rest of the missing information on the spreadsheet for me. but before i send the spreadsheet out, i want to populated it with these infos(ID, dist, mfr, tr, site). i have a query in access with all these infos already, how would i link that qery to the excel worksheet/spreadsheet so that the data populated by itself(or by a command in excel) by distributor instead of copy&paste from access to excel(472 times). a lil new to that kind of excel work a lil walk through could help or steps to follow.

thanks alot

 

[Norie]

In Excel do you actually want to have some sort of form with all the fields for each distributor?

Then fill in the pieces of data you know, then send it out to the distributor and have them 'fill in the blanks'.

 

[buju03]

That is correct, but I have 472 different distributors to email the spreadsheet too and on everage 20-30 chemicals I need to listed so they provide me information on the chemicals I have to list on the spreadsheet. I think I didn't explain myself right before, but I am looking for a way to populate a spreadsheet for 472 distributors(so 472spreadsheets) and each spreadsheet will have 20-30 chemical names(that's the data I have in accessdatabase: distributors with their respective chemicals) that I need update information for. So I want to know how can I set a utilities toI automatically run a query with the formatted spreadsheet for each distributor with the chemicals they responsible for to be listed in their spreadsheet.

 

[buju03]

Re: Populated excel template

here is my problem:
(i can have the two as two diff files or two diff sheet in a wb)


I have these two files: template(attached for reference), The Data file.

The Template – is a form I want to populate with data from the (data file) i attached it so you can see(i want macro to open the datafile filter it by distributor and list all the data for each particular distributor then copy the data and open the template file and paste the copy data on cell a6:g6 and down depend on how many data it pull for that particular datafile.

The datafile – that file has all the information/data I want to populate on the template

i found this so far:

Sub FillOutTemplate()
'From Sheet1 data fill out template on sheet2 and save
'each sheet as its own file.
Dim rng As Range
Dim rng2 As Range
Dim LastRw As Long, Rw As Long, Cnt As Long
Dim dSht As Worksheet, tSht As Worksheet
Dim MakeBooks As Boolean, SavePath As String

Application.ScreenUpdating = False 'speed up macro execution
Application.DisplayAlerts = False 'no alerts, default answers used

Set dSht = Sheets("Data") 'sheet with data on it starting in row2
Set tSht = Sheets("Template") 'sheet to copy and fill out

'Option to create separate workbooks
MakeBooks = MsgBox("Create separate workbooks?" & vbLf & vbLf & _
"YES = template will be copied to separate workbooks." & vbLf & _
"NO = template will be copied to sheets within this same workbook", _
vbYesNo + vbQuestion) = vbYes

If MakeBooks Then 'select a folder for the new workbooks
MsgBox "Please select a destination for the new workbooks"
Do
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then 'a folder was chosen
SavePath = .SelectedItems(1) & "\"
Exit Do
Else 'a folder was not chosen
If MsgBox("Do you wish to abort?", _
vbYesNo + vbQuestion) = vbYes Then Exit Sub
End If
End With
Loop
End If

'Determine last row of data then loop through the rows one at a time
LastRw = dSht.Range("A" & Rows.Count).End(xlUp).Row

For Rw = 2 To LastRw
tSht.Copy After:=Worksheets(Worksheets.Count)

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

If MakeBooks Then 'if making separate workbooks from filled out form
ActiveSheet.Move
ActiveWorkbook.SaveAs SavePath & Range("B3").Value, xlNormal
ActiveWorkbook.Close False
End If
Cnt = Cnt + 1
Next Rw

dSht.Activate
If MakeBooks Then
MsgBox "Workbooks created: " & Cnt
Else
MsgBox "Worksheets created: " & Cnt
End If

Application.ScreenUpdating = True
End Sub

 

[Norie]

Re: Populated excel template

What happened to Access?

 

[buju03]

i export the file to excel instead and work with it from there, i think its easier that way.