linkerschuh
06-22-2015, 08:51 AM
Dear all,
my problem has to do with exporting data from Access to Excel. I currently retrieve data with a query within Access and then export to Excel with VBA code. This works well. The code used is as attached below:
Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A2:I4001")
wks.Cells(1, 1).value = "Generating Data Central Europe (DE, AT, NL, Lux, CH)"
DoCmd.OpenQuery ("output_a")
Set rst = CurrentDb.OpenRecordset("output_a")
If (rst.RecordCount > 0) Then
cnt = 1
For Each fld In rst.Fields
wks.Cells(1, cnt).value = fld.Name
cnt = cnt + 1
Next fld
Call rng.CopyFromRecordset(rst, 4000, 26)
End If
wks.Columns("A:Z").HorizontalAlignment = xlLeft
wks.Rows("1:1").Font.Bold = True
DoCmd.Close acQuery, "output_CE"
rst.Close
Set rst = Nothing
I would like to achieve the following:
The code should, within the Excel file to which it exports, open three worksheets and then name them a, b, c. Then worksheet “a” should be filled with data out of query “output_a”, worksheet “b” with data out of “output_b” and worksheet “c” with “output_c”.
Furthermore, there is an additional problem:
The last field of the query output which is exported to Excel should contain a hyperlink. The formula for the hyperlink is static, with the exception of the cell number it should reference to (see highlight). It should be the same for all three queries, so a, b and c.
=HYPERLINK(CONCATENATE("http://gateway.fundinfo.com/3.0", A2), "Doc")
Could somebody help me? I tried around and I think I am going crazy.
Thanks in advance!
my problem has to do with exporting data from Access to Excel. I currently retrieve data with a query within Access and then export to Excel with VBA code. This works well. The code used is as attached below:
Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A2:I4001")
wks.Cells(1, 1).value = "Generating Data Central Europe (DE, AT, NL, Lux, CH)"
DoCmd.OpenQuery ("output_a")
Set rst = CurrentDb.OpenRecordset("output_a")
If (rst.RecordCount > 0) Then
cnt = 1
For Each fld In rst.Fields
wks.Cells(1, cnt).value = fld.Name
cnt = cnt + 1
Next fld
Call rng.CopyFromRecordset(rst, 4000, 26)
End If
wks.Columns("A:Z").HorizontalAlignment = xlLeft
wks.Rows("1:1").Font.Bold = True
DoCmd.Close acQuery, "output_CE"
rst.Close
Set rst = Nothing
I would like to achieve the following:
The code should, within the Excel file to which it exports, open three worksheets and then name them a, b, c. Then worksheet “a” should be filled with data out of query “output_a”, worksheet “b” with data out of “output_b” and worksheet “c” with “output_c”.
Furthermore, there is an additional problem:
The last field of the query output which is exported to Excel should contain a hyperlink. The formula for the hyperlink is static, with the exception of the cell number it should reference to (see highlight). It should be the same for all three queries, so a, b and c.
=HYPERLINK(CONCATENATE("http://gateway.fundinfo.com/3.0", A2), "Doc")
Could somebody help me? I tried around and I think I am going crazy.
Thanks in advance!