Hi,
My problem is i dont know the syntax for opening/writing data/closing excel/word file via vba ms access.. If you know links or sites that could be helpful...
thanks so much...
2 1738
Here is code to write data from query and into Excel - Function sql_til_excel(sqlString As String, navn As String)
-
-
-
Call Opprett_Spørring("Excel_" & navn & "_liste", sqlString)
-
Call overfør_spørring_til_excel("Excel_" & navn & "_liste", "Excel_" & navn & "_liste")
-
Call Slett_temp_spørring("Excel_" & navn & "_liste")
-
-
-
End Function
-
Function Opprett_Spørring(Spørringnavn As String, strsql As String)
-
-
Dim NtLogin As String
-
-
NtLogin = Environ("Username")
-
-
Dim dbs As Database
-
Dim strQueryName As String
-
Dim qryDef As QueryDef
-
-
-
Set dbs = CurrentDb
-
strQueryName = "tempqry " & NtLogin & " " & Spørringnavn
-
-
Dim q As QueryDef
-
-
For Each q In dbs.QueryDefs
-
-
If q.Name = strQueryName Then
-
dbs.QueryDefs.Delete strQueryName
-
End If
-
-
Next
-
-
Set qryDef = dbs.CreateQueryDef(strQueryName, strsql)
-
-
-
End Function
-
-
-
Function Slett_temp_spørring(Spørringnavn As String)
-
Dim NtLogin As String
-
-
NtLogin = Environ("Username")
-
-
DoCmd.DeleteObject acQuery, "tempqry " & NtLogin & " " & Spørringnavn
-
-
End Function
-
-
-
Public Function overfør_spørring_til_excel(Template As String, Optional Tempspørring As String, Optional Spørring As String) As String
-
Dim NtLogin As String
-
-
NtLogin = Environ("Username")
-
-
-
Dim appExcel As Excel.Application
-
Dim wbk As Excel.Workbook
-
Dim wks As Excel.Worksheet
-
-
Dim sTemplate As String
-
Dim sTempFile As String
-
Dim Info As String
-
-
Dim dbs As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim sSql As String
-
Dim lRecords As Long
-
Dim iRow As Integer
-
Dim iCol As Integer
-
Dim iFld As Integer
-
Dim sOutPut As String
-
Const cTabTwo As Byte = 2
-
Const cStartRow As Byte = 4
-
Const cStartColumn As Byte = 3
-
-
sOutPut = ahtCommonFileOpenSave(, "Skrivebord", , , , Template & " " & Date & ".xls", Template & " " & Date & ".xls", , False)
-
If sOutPut = "" Then
-
Exit Function
-
End If
-
-
-
-
-
Application.SetOption "Error Trapping", 0
-
-
sTemplate = CurrentProject.Path & "\templ\" & Template & ".xls"
-
If Dir(sOutPut) <> "" Then Kill sOutPut
-
-
FileCopy sTemplate, sOutPut
-
-
Set appExcel = CreateObject("Excel.Application")
-
Set wbk = appExcel.Workbooks.Open(sOutPut)
-
Set wks = appExcel.Worksheets(1)
-
-
If Not Tempspørring = "" Then
-
sSql = "SELECT * From [" & "tempqry " & NtLogin & " " & Tempspørring & "]"
-
End If
-
If Not Spørring = "" Then
-
sSql = "SELECT * From [" & Spørring & "]"
-
End If
-
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset(sSql, dbOpenSnapshot)
-
If Not rst.BOF Then rst.MoveFirst
-
-
iCol = cStartColumn
-
iRow = cStartRow
-
rst.MoveLast
-
Dim rc As Long
-
rc = rst.RecordCount
-
rst.MoveFirst
-
-
-
If rc > 1000 Then
-
svar1 = MsgBox("Du skal til å overføre " & rc & " poster." & vbCrLf & "Vil du fortsette?", vbYesNo, "LogiDose")
-
If svar1 = vbNo Then
-
On Error Resume Next
-
Set wks = Nothing
-
Set wbk = Nothing
-
appExcel.Quit
-
Set appExcel = Nothing
-
Set rst = Nothing
-
Set dbs = Nothing
-
DoCmd.Hourglass False
-
Exit Function
-
End If
-
End If
-
-
-
Call Fremdriftsindikator("ja", rc, "Initierer exceloverføring")
-
Do Until rst.EOF
-
iFld = 0
-
lRecords = lRecords + 1
-
Call Fremdriftsindikator(, , "Overfører post nr " & lRecords & " av " & rc, , 1)
-
For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
-
wks.Cells(iRow, iCol) = rst.Fields(iFld)
-
-
If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
-
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
-
End If
-
-
wks.Cells(iRow, iCol).WrapText = False
-
iFld = iFld + 1
-
Next
-
-
wks.Rows(iRow).EntireRow.AutoFit
-
iRow = iRow + 1
-
rst.MoveNext
-
Loop
-
-
Call Fremdriftsindikator(, , , "ja")
-
Application.FollowHyperlink sOutPut
-
-
-
exit_Here:
-
On Error Resume Next
-
Set wks = Nothing
-
Set wbk = Nothing
-
'appExcel.Quit
-
Set appExcel = Nothing
-
Set rst = Nothing
-
Set dbs = Nothing
-
DoCmd.Hourglass False
-
-
End Function
You will also need this module: - Option Compare Database
-
Global GL_UserName As String
-
Global GBL_Access_Level As String
-
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
-
-
Public Function init_globals()
-
GBL_Access_Level = "Ingen"
-
End Function
-
-
Public Function Get_Global(gbl_parm)
-
Select Case gbl_parm
-
Case "GBL_Access_Level"
-
Get_Global = GBL_Access_Level
-
End Select
-
End Function
-
-
-
The function fremdriftsindikator i use for a form with progressbar. This you should just delete or add the code for the progress bar form - Public Function Fremdriftsindikator_v2(Optional åpne As String, Optional maxhoved As Long, Optional maxdel As Long, Optional etiketthoved As String, Optional etikettdel As String, Optional lukk As String, Optional plushoved As Long, Optional plusdel As Long)
-
-
If åpne = "ja" Then
-
If Not CurrentProject.AllForms("MAIN progressbar v2").IsLoaded Then
-
DoCmd.OpenForm "MAIN progressbar v2"
-
End If
-
End If
-
-
-
If Not maxhoved = 0 Then
-
[Form_MAIN Progressbar v2].ProgressBar_hoved.max = maxhoved
-
End If
-
-
If Not maxdel = 0 Then
-
[Form_MAIN Progressbar v2].Progressbar_del.max = maxdel
-
End If
-
-
If Not etiketthoved = "" Then
-
[Form_MAIN Progressbar v2].etk_hoved.Caption = etiketthoved
-
End If
-
-
If Not etikettdel = "" Then
-
[Form_MAIN Progressbar v2].etk_del.Caption = etikettdel
-
End If
-
-
Dim val As Integer
-
-
If Not plushoved = 0 Then
-
-
val = [Form_MAIN Progressbar v2].ProgressBar_hoved.Value + plushoved
-
If val > [Form_MAIN Progressbar v2].ProgressBar_hoved.max Then
-
val = [Form_MAIN Progressbar v2].ProgressBar_hoved.max
-
End If
-
-
[Form_MAIN Progressbar v2].ProgressBar_hoved.Value = val
-
End If
-
-
If Not plusdel = 0 Then
-
val = plusdel
-
If val > [Form_MAIN Progressbar v2].Progressbar_del.max Then
-
val = [Form_MAIN Progressbar v2].Progressbar_del.max
-
End If
-
-
[Form_MAIN Progressbar v2].Progressbar_del.Value = val
-
End If
-
-
[Form_MAIN Progressbar v2].Repaint
-
If lukk = "ja" Then
-
DoCmd.Close acForm, "MAIN progressbar v2"
-
End If
-
End Function
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sandy Norton |
last post by:
Hi folks,
I have been mulling over an idea for a very simple python-based
personal document management system. The source of this possible
solution is the following typical problem:
I...
|
by: gremlinbass |
last post by:
I was wondering if I can check to see if a file is open?
Specifically, is there any way I can code Access to check to see if a
text file (datasource.txt to be specific) is open, returning a true...
|
by: |
last post by:
The following code:
Private Sub ClearControls(ByVal ctrl As Control)
Dim i As Int32
For i = ctrl.Controls.Count - 1 To 0 Step -1
ClearControls(ctrl.Controls(i))
|
by: Simon Cheng |
last post by:
Hi,
How do I open a Word or Excel document inside an event handler (e.g.,
Page_Load())?
Thanks,
Simon
|
by: Microsoft |
last post by:
I have a porgram that opens an excel workbook and modifies some data, but
almost randomly a box will pop up in the workbook telling me the file is now
available and to hit ok or cancel. This...
|
by: Syvman |
last post by:
Here's what I've got: I'm trying to grab some data out of an Excel
spreadsheet and bring it into Access. I'm able to do it, but only if
the Excel spreadsheet is not opened by any other users. I...
|
by: webgirl |
last post by:
Hi everyone,
I have a weird problem with some Word/Excel automation code that I run from Access (not sure if I should therefore post this in the Access forum..? Thought I'd try here first)
...
|
by: John |
last post by:
Access 2003
After my app is finished I'd like to create an mde file. Therefore my db has
to be made in the 2002-2003 format. So I've created a new mdb (default in
2000 format), I converted this...
|
by: NeoPa |
last post by:
Introduction:
Macro Security Levels in MS Office applications are recommended to be set to High. This stops any VBA code associated with a project from running, unless it is signed (with a...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| | |