Exporting Certain Fields to Certain Cells in Excel

[MrAustin]

Hi all,

I am looking for a way to export my data to excel and it has to be a specific format. My client has a dispute form and will accept no other, so I have to recreate the form and fill it with the data from the database. If I can format the form in excel, is there a certain way to use it as a template to accept field data?

For instance, can I say: I want field dispute_title to go into C12 and dispute_date to go into C14?

Thanks a lot!

 

[WayneRyan]

MrAustin,

Maybe this thread will help:

http://www.access-programmers.co.uk/forums/showthread.php?t=73602&highlight=.cell

I've exported to specific cells before and if this doesn't help, I can try to
look for some old code.

Wayne

 

[MrAustin]

That would be great if you could look up some code. Something as simple as "this code puts the world 'snoogans' in cell C4. I'm browsing through the link you gave and, while I'm sure it's helpful, not so much if you don't know VB as all the other stuff is confusing :grins:

Sorry, and thanks!

 

[WayneRyan]

Mr,

I'm looking ...

Wayne

 

[WayneRyan]

Mr,

Way back in the archives!

More for syntax than anything ...

Dim app As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet
Dim rng As Excel.Range
Dim cht As Excel.Chart

Set app = CreateObject("Excel.Application")
app.Visible = True
Set wb = app.Workbooks.Add
Set sht = wb.ActiveSheet

sht.Cells(3, 1) = "Project"
sht.Cells(3, 2) = "AAAA"
sht.Cells(3, 3) = "BBBB"
sht.Cells(3, 4) = "CCCC"
sht.Cells(3, 5) = "Totals"

For RowCtr = 1 To ProjCtr
   sht.Cells(RowCtr + 3, 1) = ProjectNames(RowCtr)
   sht.Cells(RowCtr + 3, 2) = STR(Int(AAAA(RowCtr) / 60))
   sht.Cells(RowCtr + 3, 3) = STR(Int(CCCC(RowCtr) / 60))
   next RowCtr

Set rng = sht.Range("B5", LowRight)
Set cht = sht.Parent.Charts.Add

With cht
  .ChartWizard rng, xl3DColumn, 2, xlRows, , , , "Usage Between " & MiscData(1) & " And " & MiscData(2), , "Hours"
  .SeriesCollection(1).XValues = sht.Range("B3", "D3")
  For RowCtr = 1 To ProjCtr - 1
     .SeriesCollection(RowCtr).name = ProjectNames(RowCtr + 1)
     Next RowCtr
End With

app.WindowState = xlMaximized

Wayne

 

[MrAustin]

Hey Wayne,

Thanks for the code! It's got me on the right track, although the first five lines where you are declaring excel variables give "user defined-type not defined" errors. Any thoughts?

Thanks again!

 

[WayneRyan]

MrAustin,

You need to get the code in Design View.

Tools --> References

Then point to the Excel Library.

Use the Search Facility here and look for "References" for more info.

Wayne

 

[MrAustin]

Thanks a lot Wayne, I got it working great! I love this forum! I hope when I get better I can start to contribute as well )

Thanks again!