Export Query Results To An Excel Sheet.
Can someone point me in the right direction; I want to pull the results from a query using Visual Basic and display it in an Excel work book. How do you go about doing this? Is there documentation?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Export Results Of Query To Excel
I want to write a query in access that accepts 2 parameters (first and last in a range of invoices). I want to have the query automatically export the data an excel spreadsheet. How could I set up a parameter query (i'm new to this stuff) and how could I export it to excel? Would I be better off actually executing the query from within excel and importing the data? What's the best way to go about this.
Thanks
Chris
Export Results Of Sql Query??
Hi, Ive pretty much exhausted all of my experience with VB...so i need some help.
Im using adodc, Trying to export a datagrid to an excel spreadsheet after it executes a user defined SQL query.
I only want the results of the query to be exported, not all the records in the recordset.
I have code (which works) to export all my records in the recordset to excel (see below).
One of the things i tried was using a datareport, the only thing wrong with that is i am unable to get all of my fields displayed even when i change to Landscape format, paper width error, so thats why i have to export.
What ive been trying to do is to pass in the sql query into this export function, but with no luck.
Any help i can get regarding the export of only the results of my query, would be much appreciated!
Thx!
Its a lot of code, but its the only way i can explain my problem:
___________________________________________________________________________________________
Private Sub cmdaddtoquery_Click()
If sQuery = "" Then
sQuery = "SELECT * FROM tblmonitor WHERE "
Else
sQuery = sQuery & " AND "
End If
If (Combo1.Text = "date") Then
Dim MyDate As Date
MyDate = MaskEdBox1.Text
'sQuery = sQuery & Combo1.Text & Combo2.Text & "'" & CStr(CLng(MyDate)) & "'"
'sQuery = sQuery & Combo1.Text & Combo2.Text & (Format("# " & (MaskEdBox1.Text) & " #", "dd/mm/yy"))
sQuery = sQuery & Combo1.Text & Combo2.Text & CStr(CLng(MyDate))
MaskEdBox1.Text = ""
frmquery.MaskEdBox1.Format = ""
'sQuery = sQuery & Combo1.Text & Combo2.Text & "#" & MaskEdBox1.Text & "#"
ElseIf (Combo1.Text = "projectLocation") Or (Combo1.Text = "proponent") Or (Combo1.Text = "signature") Or (Combo1.Text = "LOA") Or (Combo1.Text = "problemidentifiedwithcurrentmitigation") Then
sQuery = sQuery & Combo1.Text & Combo2.Text & "'" & MaskEdBox1.Text & "'"
MaskEdBox1.Text = ""
frmquery.MaskEdBox1.Format = ""
ElseIf (Combo2.Text = "LIKE") Then
sQuery = sQuery & Combo1.Text & Combo2.Text & "'" & MaskEdBox1.Text & "%';"
Else
sQuery = sQuery & Combo1.Text & Combo2.Text & "" & MaskEdBox1.Text & ""
End If
End sub
____________________________________________________________________________________________
___________________________________________________________________________________________
Private Sub cmdSearch_Click()
Call query(sQuery)
sQuery = ""
End Sub
____________________________________________________________________________________________
___________________________________________________________________________________________
Private Sub query(sql1 As String)
Set rsdyn = New ADODB.Recordset
rsdyn.CursorLocation = adUseClient
rsdyn.Open sql1, conndyn, adOpenDynamic, adLockReadOnly
'Where dgsource is a datagrid
Set dgsource.DataSource = rsdyn
dgsource.Refresh
If (rsdyn.RecordCount = 0) Then
MsgBox "No records found matching criteria entered, please re-enter query", vbExclamation, "No records found"
List2.clear
txtqueryresults = ""
MaskEdBox1 = ""
Form_Activate
Else
lblstat.Caption = "(" & rsdyn.RecordCount & ")" & " Item(s) " & "Found!"
End If
End sub
___________________________________________________________________________________________
Heres the code to export all records:
Private Function Export()
'This code exports a recordset to an excel spreadsheet
Dim sql1 As String
Dim x
Dim y
Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim xl As Excel.Worksheet
Dim icols
Dim MonitoringDB As Database
Dim dgsource As DataGrid
Dim FldCount As Integer
Dim rsdynCount As Integer
Dim i, k, c, r
Dim rsdyn As ADODB.Recordset
Set rsdyn = New ADODB.Recordset
Set rsdyn = Adodc1.Recordset
x = MsgBox("This function will export the current records in the DBGrid to an Excel sheet, " & _
"If you do not have Excel an error will occur, do wish to continue?", vbYesNo + vbQuestion, "Export Data?")
If x = vbNo Then Exit Function
If IsNull(Adodc1.Recordset) Then GoTo NoRS
Screen.MousePointer = vbHourglass
y = MsgBox("Exporting Data...Please wait", vbOKOnly, "Exporting Data")
'Declare application
Set xlApp = CreateObject("Excel.Application")
'Declare Workbook
Set xlBook = xlApp.Workbooks.Add(xlWBATWorksheet)
'Identify worksheet
Set xl = xlBook.Worksheets(1)
FldCount = rsdyn.Fields.Count - 1
'Excel Column counts are kept seperate as fields may be skipped
c = 1
For icols = 0 To rsdyn.Fields.Count - 1
'If the field is a system field then it skips it.
If rsdyn.Fields(icols).Name Like "s_" & Chr(42) Then GoTo SkipName
xl.Cells(1, c).Value = rsdyn.Fields(icols).Name
c = c + 1
SkipName:
Next
xl.Range(xl.Cells(1, 1), xl.Cells(1, rsdyn.Fields.Count)).Font.Bold = True
With rsdyn
.MoveLast
.MoveFirst
rsdynCount = .RecordCount - 1
r = 2
Me.Height = 5970
'4 loops are being performed at once here.
'Loop 'k' loops through the rows of records and
'with each row 'r' for the Excel sheet.
'Inside this loop is another nested loop.
'Loop 'i' is looping through the Fields and along
'with i 'c' is moving through the columns.
'As mentioned above the reason for seperate counts
'between the recordset and Excel sheet are due to
'skipping system fields.
For k = 0 To rsdynCount
c = 1
For i = 0 To FldCount
If rsdyn.Fields(i).Name Like "s_" & Chr(42) Then GoTo SkipField
xl.Cells(r, c).Value = rsdyn.Fields(i).Value
c = c + 1
SkipField:
Next i
r = r + 1
.MoveNext
Next k
End With
'After loop is complete, show the sheet
xl.Visible = xlSheetVisible
xlApp.Visible = True
xlApp.Quit
Set xl = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set rsdyn = Nothing
Screen.MousePointer = vbDefault
Exit Function
NoRS:
Screen.MousePointer = vbDefault
MsgBox "No Active recordset detected"
Exit Function
Exp_Err:
Screen.MousePointer = vbDefault
MsgBox Err.Number & ": " & Err.Description
Resume Next
End Function
Export Query Results To Notepad Using Sql Or Oracle
Hi,
Is there any code or any way i can export the results of my query into a notepad file...
lets say i do a select statement
select * from dept
How do i get the data exported into a notepad file?
thankx in advance
Export Results To An Excel File
Given a recordset (originating from an SQL Query call) which is used to populate a datagrid (as datasource). How could I allow the user to “export” the results into an Excel [.csv I assume] file?
Of course it must maintain all the columns and rows.
Export Results To An Excel File
Given a recordset (originating from an SQL Query call) which is used to populate a datagrid (as datasource). How could I allow the user to “export” the results into an Excel [.csv I assume] file?
Of course it must maintain all the columns and rows.
Export Results To Excel And Print Them
I m using VB5 to access and process data in a text file. The results are stored in variables in my program. Now I want to export results to Excel and print them in spreadsheet form with, but I don't know where to start.
Thanks for your help
Export Excel Sheet To .csv File Using VB6
I can't seem to find what I'm looking for. I'm attempting to read an xcel file and output a sheet to a .csv file. The code I'm using is SLOWER than molasses in january. It takes a second for each row to be processed. There has got to be a faster way. Not fancy, but I'm attempting to create the .csv file before finishing it up.
Here's my code:
Public Function ProcessXLS(ByVal srcFile As String, ByVal destFile As String, _
Optional AppendData As Boolean, Optional StrDelimiter As String)
Dim wkbObj As Object, ActSht As Integer, FNum As Integer
Dim BeginRow As Integer, BeginCol As Integer, strCellData As String
Dim EndRow As Integer, EndCol As Integer, i As Integer, StrDelim As String
Dim RowIdx As Integer, strDataLine As String, ColIdx As Integer
If StrDelimiter = "" Then StrDelimiter = "," 'default
LogMsg "Entering XLS for " & srcFile, True
On Error GoTo ERR_XLS
Set wkbObj = CreateObject("Excel.Application")
'open the excel spreadsheet
wkbObj.workbooks.Open srcFile, True
'==========================================
'get the name of all sheets in the workbook
Dim wsNames(100)
Dim wsNamesCnt As Integer
wsNamesCnt = 0
'just seeing what's available.
For Each sh In wkbObj.activeWorkbook.Sheets
wsNames(wsNamesCnt) = sh.Name
wsNamesCnt = wsNamesCnt + 1
Next sh
'active sheet???
ActSht = 0
'open output file
destFile = destFile & Trim(wsNames(ActSht)) & ".CSV"
FNum = FreeFile
If AppendData Then
Open destFile For Append Access Write As #FNum
Else
Open destFile For Output Access Write As #FNum
End If
With wkbObj.ActiveSheet.UsedRange
BeginRow = wkbObj.ActiveSheet.UsedRange.cells(1).Row
BeginCol = wkbObj.ActiveSheet.UsedRange.cells(1).Column
EndRow = wkbObj.ActiveSheet.UsedRange.cells(wkbObj.ActiveSheet.UsedRange.cells. Count).Row
EndCol = wkbObj.ActiveSheet.UsedRange.cells(wkbObj.ActiveSheet.UsedRange.cells. Count).Column
End With
For RowIdx = BeginRow To EndRow
StrDelim = ""
strDataLine = ""
Debug.Print Time
For ColIdx = BeginCol To EndCol
strDataLine = strDataLine & StrDelim & Chr$(34) & strCellData & Chr$(34)
' strDataLine = strDataLine & StrDelim & strCellData
StrDelim = StrDelimiter
Next ColIdx
Debug.Print Time
Print #FNum, strDataLine
Debug.Print Time
DoEvents
Next RowIdx
'==========================================
wkbObj.workbooks.Close
wkbObj.Quit
Set wkbObj = Nothing
END_XLS:
ProcessXLS = strRetCode
LogMsg "Exiting XLS", True
Exit Function
ERR_XLS:
strRetCode = Error(Err.Number) & " " & Err.Description
If IsObject(wkbObj) Then
If wkbObj.Name = "Microsoft Excel" Then
LogMsg ("Error XLS: " & strRetCode)
wkbObj.workbooks.Close
wkbObj.Quit
End If
End If
Resume END_XLS
End Function
Any help is appreciated.
Thanks
Matt.
Export Data Into An Excel Sheet
Hi,
I am trying to export some records from a table/query to an Excel sheet. I know how to do this in Access but, I dont know in SQL Server. can anybody know how to do this. Please explain to me
I have another question to ask. When we create an index in Access, How to open the table for a selected index.
Eg. I have two indexes for a table
Primary Stno Asc
Name Asc
Secon Course Asc
Name Asc
if I want to open my table in both different sorted orders how do I do.
Looking forward a speedy reply,
thanks
Kishor
How To Export The Data In Access To A New Excel Sheet?
Hi People,
Can any one help me on this?
I have successfully insert new data into my access file called db1.mdb using those codes as shown below
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsAdministratorDesktopFYP - B1058@Home Testingdb1.mdb;"
MyConn.Open
MyConn.Execute ("INSERT INTO monthlyinfo VALUES ('15/12/2005','3.57.53','D','$0.135')")
MyConn.Close
but right now I would like to export these data in the db1.mdb into a new excel spreadsheet so that I can manipulate those data as I like using excel instead. So how can I do it?
Anyone ?
Thank You
How To Export Data Report In Excel Sheet
Hi,
I am using datareports to generate the reports in VB. I want to transfer datareports to excel sheet. Please let me know the code to transfer datareports in excel sheet.
thanks
Newbie Need Help Export Data From Excel Spread Sheet To Ms Access Database
Hello,
I am fairly new to vba and wanted to learn how can i create a program in VBA in MS Access 2003 that will let me pick an excel file using a dialog then once i choose a file i want it want it to tranfer the record from the excel spread which has around 11 columns and has the same column names as the access database.
Basically the excel spread sheet has the updated information and needs to update the existing record in the access DB and add the new records contained in the excel spreadsheet that aren't in the access database.
===============
Columns names
===============
CustomerID, ContactFirstName, ContactLastName, BillingAddress, City, State/Province, PostalCode, DateBecameACustomer, ContactTitle, PhoneNumber, EmailAddress
Functionality
1) open excel file
2) find row using customerID
3) check each column in access
4) if any column is different in that row update changes
5) if row not found then add new record
6) do until EOF or End Of Sheet(s) (Note: Need to read every sheet contained in excel file)
7) End
Help Please {Access Query Results To Excel}
i am using vb code to access a database. in that code i am using a combo box that allows the user to choose what he/she would like to query by. i guess this brings about my question. once that have query the database to obtain the following results, you are able to view one by one each record that was obtained by the query. i am wondering is it possible to send the query results to MS excel. hopefully this makes sense.
Thanks a lot
Squeakie
Exporting Multiple Query Results To Excel With VB**RESOLVED**
Hey Hope everyone had a good weekend...
Heres what i need help on. I have quite a few queries in Access, and I want to run a script that will run those queries, and take their results and paste it into an excel spreadsheet consecutively. Any pointers/hints/tips/code that could help me to do this would be highly appreciated. Im pretty new to VB and VBA btw.
Thanks in advance!
doc
Edited by - doctor on 7/2/2004 8:05:05 AM
Export To Excel From SQL Query
It's not my day
I've searched all over and I can't find it!
I have a form with MSHFlexigrid on it. Grid is connected to ADODC that gets its RecordSource assigned dynamicaly by user selections.
I would like user to click on button and export what is in grid into Excel spreadsheet.
I've checked DTS, but, I guess, it wont work because it says that I "need to install the SQL Server client cools on the computers on which the packages are to be run" (I have EXE file located on the same machine as SQL Server, no client tools available for user's PC).
I've done this before in Access like this:
VB Code:
Private Sub cmdExportToExcel_Click() On Error GoTo Err_cmdExportToExcel_click Dim Db As Database Dim Qry As QueryDef Dim StrSql As String Dim ques As Integer ques = MsgBox("Are you sure you want to export the data to excel? This may take a few minutes.", vbYesNo, "Export Data?") If ques = 7 Then Exit Sub End If StrSql = Form.RecordSource Set Db = CurrentDb Set Qry = Db.CreateQueryDef("InventoryData", StrSql) Set Qry = Nothing Set Db = Nothing DoCmd.OutputTo acOutputQuery, "InventoryData", acFormatXLS, , True Resume exit_cmdExportToExcel_click exit_cmdExportToExcel_click: DoCmd.SetWarnings False DoCmd.DeleteObject acQuery, "inventoryData" DoCmd.SetWarnings True Exit Sub Err_cmdExportToExcel_click: Resume exit_cmdExportToExcel_click End Sub
Is there a way to do something similar in VB 6 and how?
Thanks in advance.
Export Query To Excel??
Hi, Ive pretty much exhausted all of my experience with VB...so i need some help.
Im using adodc, Trying to export a datagrid to an excel spreadsheet after it executes a user defined SQL query.
I only want the results of the query to be exported, not all the records in the recordset.
I have code (which works) to export all my records in the recordset to excel (see below).
One of the things i tried was using a datareport, the only thing wrong with that is i am unable to get all of my fields displayed even when i change to Landscape format, paper width error, so thats why i have to export.
What ive been trying to do is to pass in the sql query into this export function, but with no luck.
Any help i can get regarding the export of only the results of my query, would be much appreciated!
Thx!
Its a lot of code, but its the only way i can explain my problem:
___________________________________________________________________________________________
Private Sub cmdaddtoquery_Click()
If sQuery = "" Then
sQuery = "SELECT * FROM tblmonitor WHERE "
Else
sQuery = sQuery & " AND "
End If
If (Combo1.Text = "date") Then
Dim MyDate As Date
MyDate = MaskEdBox1.Text
'sQuery = sQuery & Combo1.Text & Combo2.Text & "'" & CStr(CLng(MyDate)) & "'"
'sQuery = sQuery & Combo1.Text & Combo2.Text & (Format("# " & (MaskEdBox1.Text) & " #", "dd/mm/yy"))
sQuery = sQuery & Combo1.Text & Combo2.Text & CStr(CLng(MyDate))
MaskEdBox1.Text = ""
frmquery.MaskEdBox1.Format = ""
'sQuery = sQuery & Combo1.Text & Combo2.Text & "#" & MaskEdBox1.Text & "#"
ElseIf (Combo1.Text = "projectLocation") Or (Combo1.Text = "proponent") Or (Combo1.Text = "signature") Or (Combo1.Text = "LOA") Or (Combo1.Text = "problemidentifiedwithcurrentmitigation") Then
sQuery = sQuery & Combo1.Text & Combo2.Text & "'" & MaskEdBox1.Text & "'"
MaskEdBox1.Text = ""
frmquery.MaskEdBox1.Format = ""
ElseIf (Combo2.Text = "LIKE") Then
sQuery = sQuery & Combo1.Text & Combo2.Text & "'" & MaskEdBox1.Text & "%';"
Else
sQuery = sQuery & Combo1.Text & Combo2.Text & "" & MaskEdBox1.Text & ""
End If
End sub
____________________________________________________________________________________________
___________________________________________________________________________________________
Private Sub cmdSearch_Click()
Call query(sQuery)
sQuery = ""
End Sub
____________________________________________________________________________________________
___________________________________________________________________________________________
Private Sub query(sql1 As String)
Set rsdyn = New ADODB.Recordset
rsdyn.CursorLocation = adUseClient
rsdyn.Open sql1, conndyn, adOpenDynamic, adLockReadOnly
'Where dgsource is a datagrid
Set dgsource.DataSource = rsdyn
dgsource.Refresh
If (rsdyn.RecordCount = 0) Then
MsgBox "No records found matching criteria entered, please re-enter query", vbExclamation, "No records found"
List2.clear
txtqueryresults = ""
MaskEdBox1 = ""
Form_Activate
Else
lblstat.Caption = "(" & rsdyn.RecordCount & ")" & " Item(s) " & "Found!"
End If
End sub
___________________________________________________________________________________________
Heres the code to export all records:
Private Function Export()
'This code exports a recordset to an excel spreadsheet
Dim sql1 As String
Dim x
Dim y
Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim xl As Excel.Worksheet
Dim icols
Dim MonitoringDB As Database
Dim dgsource As DataGrid
Dim FldCount As Integer
Dim rsdynCount As Integer
Dim i, k, c, r
Dim rsdyn As ADODB.Recordset
Set rsdyn = New ADODB.Recordset
Set rsdyn = Adodc1.Recordset
x = MsgBox("This function will export the current records in the DBGrid to an Excel sheet, " & _
"If you do not have Excel an error will occur, do wish to continue?", vbYesNo + vbQuestion, "Export Data?")
If x = vbNo Then Exit Function
If IsNull(Adodc1.Recordset) Then GoTo NoRS
Screen.MousePointer = vbHourglass
y = MsgBox("Exporting Data...Please wait", vbOKOnly, "Exporting Data")
'Declare application
Set xlApp = CreateObject("Excel.Application")
'Declare Workbook
Set xlBook = xlApp.Workbooks.Add(xlWBATWorksheet)
'Identify worksheet
Set xl = xlBook.Worksheets(1)
FldCount = rsdyn.Fields.Count - 1
'Excel Column counts are kept seperate as fields may be skipped
c = 1
For icols = 0 To rsdyn.Fields.Count - 1
'If the field is a system field then it skips it.
If rsdyn.Fields(icols).Name Like "s_" & Chr(42) Then GoTo SkipName
xl.Cells(1, c).Value = rsdyn.Fields(icols).Name
c = c + 1
SkipName:
Next
xl.Range(xl.Cells(1, 1), xl.Cells(1, rsdyn.Fields.Count)).Font.Bold = True
With rsdyn
.MoveLast
.MoveFirst
rsdynCount = .RecordCount - 1
r = 2
Me.Height = 5970
'4 loops are being performed at once here.
'Loop 'k' loops through the rows of records and
'with each row 'r' for the Excel sheet.
'Inside this loop is another nested loop.
'Loop 'i' is looping through the Fields and along
'with i 'c' is moving through the columns.
'As mentioned above the reason for seperate counts
'between the recordset and Excel sheet are due to
'skipping system fields.
For k = 0 To rsdynCount
c = 1
For i = 0 To FldCount
If rsdyn.Fields(i).Name Like "s_" & Chr(42) Then GoTo SkipField
xl.Cells(r, c).Value = rsdyn.Fields(i).Value
c = c + 1
SkipField:
Next i
r = r + 1
.MoveNext
Next k
End With
'After loop is complete, show the sheet
xl.Visible = xlSheetVisible
xlApp.Visible = True
xlApp.Quit
Set xl = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set rsdyn = Nothing
Screen.MousePointer = vbDefault
Exit Function
NoRS:
Screen.MousePointer = vbDefault
MsgBox "No Active recordset detected"
Exit Function
Exp_Err:
Screen.MousePointer = vbDefault
MsgBox Err.Number & ": " & Err.Description
Resume Next
End Function
Query In The Excel Sheet
Hi,
well, i was trying to do as you told, but with no luck.
I have an excel sheet with 5 columns and alot of rows.
I need to find a value from column2(B), then get the value from column 3, 4 and 5 from same line i found the searched value.
if i'm using the find command, all i get is true or false (if it was found) but how can i know the row and column which the value is in.
hope you'll find how to do so.
regards and thanks
Ofer.
Export Access Query To Excel
We have an Access 2000 application that uses Docmd.TransferSpreadsheet to export a query to Excel. We use TransferSpreadsheet in several places with no problem. However, one query will not export properly on some PC's but does fine on others even though the data is the same.
The Access code looks like this:
Docmd.TransferSpreadsheet, acExport, acSpreadsheetTypeExcel9, "QueryName", "Path", True
Issues:
The query is relatively complex involving 6 other queries and 1 table.
All users have recent builds of Office 2000, SR1
All users have NT 4, SP6
The Big Issue:
The export works on all PC's with IE 5.5 or 6, but not with IE 5.0. I had one user get Computer Support to upgrade from IE 5.0 to 5.5 (support couldn't find 6.0???) and the export still didn't work.
I think that the problem is related to IE, but can't prove it.
Any ideas?
Export Access Query To Excel
Could anyone help me with the code that would export a query to an existing Excel workbokk called "Vendor25K" and replace the data in the "Detail" sheet with the query data, then fire the Excel macros that are in this workbook? The query name is "Vendors".
Thank you in advance!!
Excel Sheet And Access Query
Hello, I've been trying to link an access query to a excel worksheet, but I can't figure out how.
I've tried to do it with the transfer spreadsheet action in a macro, but it only links a table, not a query.
the help mentions something about queries, but the link doesn't work.
Thanks,
BJ
Query And Editing A Excel Sheet
Hi Guys
I have an Excel sheet that I need to update on a daily bases,
the Excel sheet looks similer to this:
1a2a
2b5b
3c3c
4d8d
5e1e
6f6f
7g7g
8h4h
9i9i
0j0j
each number relate to the letter in the colum right access from it. if I change the letter I need a MACRO that can be created in MS visual basic to be able to look into the number colum in the other set to the left and change the letter accross to match the change that happened in the colums to the right.
Is there a way to this, what kind of querry I need to set and how.
Any help would be appreciated,
Thanks
Georges
Export Query In An Excel Through Visual Basic
Hi,
For eg: Select ipaddress from transfer
i want to export ipaddress column in an excel sheet and that too on a specific column, for Eg if i want the output in the third column of the excel sheet
Can any body help me how to write a code for that in VB
Thanks in Advance
Sneha
How To Export Query Result To Excel File ?
I am come from Hong Kong.
Recently, I am writing vb scripts to export query result to a excel file.
However, I don't know how to do so, can anyone teach me ?
Thanks for your kindly help
Creating A Query And Exporting To An Excel Sheet
Hi,
This is the piece of code
Private Sub GenerateTransferLatency(ByRef objWB As Excel.Workbook)
Dim objRS As New ADODB.Recordset
Dim objWS As Excel.Worksheet
Dim objWS1 As Excel.Worksheet
Dim lRow As Integer
Dim lSQL As String
Set objWS = objWB.Worksheets.Add
Set objRS = Nothing
lSQL = "SELECT COMPUTER, EQUIPMENTSERNUM, MONITOR_FLAG, UTC_END_TIME, INTERVAL, ACTUAL_UTC_END_TIME, EXPECTED_UTC_END_TIME, DIFF_IN_TIME,DIFF_IN_DAYS FROM (SELECT T1.EQUIPMENTSERNUM, T1.UTC_END_TIME, T1.INTERVAL,T1.COMPUTER, NVL(T2.MONITOR_FLAG,0) MONITOR_FLAG,(T1.UTC_END_TIME + (T1.INTERVAL / 1440)) ACTUAL_UTC_END_TIME, (SYSDATE + (4 /24)) EXPECTED_UTC_END_TIME,ROUND((((SYSDATE + (4 /24)) - (T1.UTC_END_TIME + (T1.INTERVAL / 1440))) * 1440)) DIFF_IN_TIME,ROUND((((SYSDATE + (4 /24)) - (T1.UTC_END_TIME + (T1.INTERVAL / 1440)))),2) DIFF_IN_DAYS FROM TRANSFER_MANAGER T1, EQUIPMENTDEFINITION T2, OSMDEFINITION T3 Where T1.EQUIPMENTSERNUM = T2.EQUIPMENTSERNUM And T2.OSMNAME = T3.OSMNAME AND T1.VALID_UNTIL = '31-DEC-2030' AND HOLD <> 4) Where DIFF_IN_TIME > 120 AND (EQUIPMENTSERNUM IN ( Select Equipmentsernum from Equipmentdefinition where CONTRACT_TYPE not like 'DISC%')) ORDER BY DIFF_IN_TIME DESC"
objWS.Cells.Font.Name = "Trebuchet MS"
objWS.Range("A1:I1").Merge
objWS.Range("A1:I1").HorizontalAl ignment = xlCenter
objWS.Range("A1:I1").VerticalAlignment = xlBottom
'objWS.Cells(1, 1) = IIf(optALL.Value, "Transfer Latency for ALL Units")
objWS.Cells(1, 1).Font.Bold = True
objWS.Cells(2, 1) = "COMPUTER"
objWS.Cells(2, 1).Font.Bold = True
objWS.Cells(2, 2) = "EQUIPMENTSERNUM"
objWS.Cells(2, 2).Font.Bold = True
objWS.Cells(2, 3) = "MONITOR_FLAG"
objWS.Cells(2, 3).Font.Bold = True
objWS.Cells(2, 4) = "UTC_END_TIME"
objWS.Cells(2, 4).Font.Bold = True
objWS.Cells(2, 5) = "INTERVAL"
objWS.Cells(2, 5).Font.Bold = True
objWS.Cells(2, 6) = "ACTUAL_UTC_END_TIME"
objWS.Cells(2, 6).Font.Bold = True
objWS.Cells(2, 7) = "EXPECTED_UTC_END_TIME"
objWS.Cells(2, 7).Font.Bold = True
objWS.Cells(2, 8) = "DIFF_IN_TIME"
objWS.Cells(2, 8).Font.Bold = True
objWS.Cells(2, 9) = "DIFF_IN_DAYS"
objWS.Cells(2, 9).Font.Bold = True
objWS.Range("A2:I2").Interior.Color = vbGreen
lRow = 3
objRS.Open lSQL, objConn, 3, 3, 1
Do While Not (objRS.EOF)
objWS.Cells(lRow, 1) = objRS(0)
objWS.Cells(lRow, 2) = objRS(1)
objWS.Cells(lRow, 3) = objRS(2)
objWS.Cells(lRow, 4) = objRS(3)
objWS.Cells(lRow, 4).NumberFormat = "mm/dd/yyyy hh:mm:ss"
objWS.Cells(lRow, 5) = objRS(4)
objWS.Cells(lRow, 6) = objRS(5)
objWS.Cells(lRow, 6).NumberFormat = "mm/dd/yyyy hh:mm:ss"
objWS.Cells(lRow, 7) = objRS(6)
objWS.Cells(lRow, 7).NumberFormat = "mm/dd/yyyy hh:mm:ss"
objWS.Cells(lRow, 8) = objRS(7)
objWS.Cells(lRow, 9) = objRS(8)
objWS.Cells(lRow, 10).NumberFormat = "0.000"
objRS.MoveNext
lRow = lRow + 1
Loop
objRS.Close
Set objRS = Nothing
objWS.Range("A1:I1", "A" & lRow & ":I" & lRow & "").Columns.AutoFit
Set objWS = Nothing
End Sub
This successfully excutes in an excel sheet where we have 9 columns, after this i need to add an extra column i,e, 10 th coloumn by execting the below query i.e.case statement.
Select Case lSQL
Case "Select * from QUERYSTATUSINFO WHERE BYTESTRANSFERRED
48 AND QUERYDISPATCHTIME>SYSDATE-1"
MsgBox "2048 error", vbInformation
Case "Select * from TRANSFER_MANAGER WHERE HOLD=1"
MsgBox "Unit on hold OSM CONNECTIVITY", vbInformation
Case "Select * from TRANSFER_MANAGER WHERE HOLD=2"
MsgBox "outage", vbInformation
Case "Select * from TRANSFER_MANAGER WHERE HOLD=3"
MsgBox "New Unit", vbInformation
Case "Select * from TRANSFER_MANAGER WHERE HOLD=4"
MsgBox "DISCONNECTED", vbInformation
Case Else
MsgBox "Did not recognized this statement"
End Select
Can some body help me out , how to create the last column and fill it throgh this Select queries.
Thanks
Sneha
Export Results In Empty Word Doc
I'm trying to export a Crystal Report to a Word document via code. I get no errors when executing my code; but when I open the file, there is only the headers: no details. It works when I export through the CrystalReportViewer object.
Run Sql On A Delimited Text File And Export Results
I have a delimited text file that I need to work with an sql statements and put the result in another delimited text file to be send to the MVS system. I already define the schema file for the text file, I manage to put the fields in the text file in a DBgrid but now I dont know how to run sql on it and export the results to a text file. Can anyone help me ?
Failed To Export The Report, Error In MS Excel Export Format DLL
Hi,
I have a little problem with Crystal Reports (CR 8) :
I try to export one of my reports from a VB program. When I try to export it in "Comma Separated Values" format everything is ok (except this is not the format I need
). When I try to export it in excel format it works, on some PCs but not on others ?
VB code sample :
rpt.ExportOptions.FormatType = crEFTExcel80Tabular
rpt.ExportOptions.DestinationType = crEDTDiskFile
rpt.ExportOptions.UseReportDateFormat = True
rpt.ExportOptions.UseReportNumberFormat = True
rpt.ExportOptions.DiskFileName = txtExportDirectory.Text & ExportName
rpt.Export False
I tried all the excel formats (crEFTExcel50, crEFTExcel50Tabular, crEFTExcel70 ...) but on some PC's I always get the "Exporting Records" window for a second and then the error "Failed to export the report, Error in MS Excel Export Format DLL".
Can anyone help me out ?
Thanks !
Export Single Sheet To File
I'd like to export a single excel sheet to another excel file.
I cant find any nice way of doing this...
Can someone give me a pointer in the right direction. Is there an inbuilt function that will do this?
Thanks.
Export Sheet Tables To VBA Arrays
I have an addin (xla) that includes several worksheets. While migrating the xla to a COM addin (dll), I realized that I can not add any of my worksheets into the COM addin, therefore I will need to convert the tables on my worksheets to arrays in VBA.
Does anyone know if a tool exists to do this job automatically?
Export Not Understanding Linked Sheet
i have some dataset info that is exported to excel no problem. Except when i insert a linked sheet value. For example...
i have three sheets that i write to MySheet1, MySheet2, MySheet3 when i put
something like ws.get_Range(someCell,someCell).Formula = "MySheet2!A5" into MySheet1 Excel doesn't read the link instead it just renders the string. When i open the. I'm at a loss. any help would be very welcome.
fyi..
i have also tried using value2 also and it does behave a little differently but end result it the same. I don't have a problem inserting other formula with .Formula but referencing another sheet is causing some problems
VB In Excel - Trying To Display Cells From Sheet To Sheet.
I'm going to go ahead and appologize for asking this, but I have no idea where to even start to search.
What I am trying to do is run and excel macro that will look for an specific enrty in column A. The entry it is searching for can happen multiple times. I want it to then display the information in columns B-F, respective to the the criteria in column A. (If the search is met in column A, that row I want displayed on a new sheet within the workbook)
I'm pretty sure that I can get info to display from one sheet to another. But my problem is how to run the loop so that it will search column A, and dipslay all respective information in columns B-F, if their column A meets the criteria. And stop when it gets to the bottom of the data, obviously.
Any help is so much appreciated. Also if you could just refer me to another section/topic, that will work too. Thanks in advance.
-g2
One Excel Sheet Monitoring Another Sheet's Events
I've written some macros that open an excel workbook and when an account number is typed into a user defined cell or cells the name associated with that account number is plugged into another user defined cell from the workbook the macros opened. I think my co-workers would find these macros very useful, the problem is none of them are very computer literate, and I don't want to have to go around and set these macros up on their excel workbooks. I would like to have these macros stored in a single sheet, but be triggered by the events of the sheets created by my co-workers without them having to insert any code into the sheets they create... just insert the sheet with the macros. Does anybody know if this is possible?? I appreciate any of the help I can get!!! I'm getting frustrated!!!
Help With Query Results
Hello all,
i'm trying to run a query:
SELECT table1.description, sum(table1.time_diff) AS footage, table1.machine, Sum(1) AS recordcount
FROM table1
GROUP BY table1.description, table1.machine;
and would like to add the query results into another database. could you all give some inputs?
thanks,
Sum Of SQL Query Results
Hi
i have a query with multiples tables, i have created the correct query to get values for a field named "cost". I don't want to display all the record i want to display the total of the cost of each record found.
Hope that makes sense
Thanks in advance
Not Getting Results From Query
Simple task...
Type in a number, find it, then show the owner.
Database goes to RS.EOF = True without finding the number and I know it's there.
Private Sub cmdEnter_Click()
Dim strPin As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
optFound.Value = False
optNFound.Value = False
strPin = txtInput
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& " DATABASE=ahmsys;" _
& "UID=root;PWD=abc; OPTION=3"
conn.Open
Set RS = New ADODB.Recordset
RS.Open "SELECT * FROM tenants WHERE ten_pin = " & strPin & " ", conn, adOpenDynamic, adLockOptimistic, adCmdText
If RS.EOF Then
optNFound.Value = True
Else
txtOut = RS("ten_name")
optFound.Value = True
End If
End Sub
This should be easy....... Thanks, Mikey
To Little Results In Query
Hello,
For a program I'm writing I need a report of all relations and there post adres
when the relation has no post adres it should still apear in the report.
to accomplish i tried the following query
SELECT * FROM Relations
LEFT JOIN Adres_Relation on Relation.ID = Adres_Relation.Relation_ID
WHERE (Adres_Relation.Adres_Type IS NULL OR Adres_Relation.Adres_Type = 'P')
it fails, because when the relation has a visit adres (type = 'V') adres_type is null won't be returned
adres_type is null will only be returned when the relation has no adres.
does anyone know how i could resolve this problem
(without getting duplicatie relations in my report)
thank you in advance
Query With Weighted Results
Hi, i need a little help with a query.
I have a table that looks like this:
name weight
------------------
John 1
Brian 0
Bill 2
I would like to make a query that weights the results, so that the result of the query will be:
name
---------
John
John
Brian
Bill
Bill
Bill
The logic is, that the query have to put all the names in the table one time, and furthermore put a name in the table 1 time if the weight is 1, 2 times if the weight is 2 etc. etc.
Can anyone please help with that?
Query Results Into Text Box.
I have a simple query that I got help on from this board yesterday. I need to take the results of this query and put them into my text box with the original name of "Text2."
Dim MyConn As ADODB.Connection
Private Sub Command1_Click()
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:db3.mdb;"
MyConn.Open
sSql = ("SELECT [Total Of Each] FROM Table1 WHERE [Item] = '" & Text1.Text & "'")
' Now I would like the resulting total of the above query to go into text2
Text2.Text = ??????
End Sub
Displaying Query Results In VB App
I have a db of patients. When placing a new booking for a patient, I would like to be able to search existing records and populate the fields based on existing info if a record is found. I can create the code and queries to search by a few different criteria (we'll say last name for my question).
My question being, what are some ways to present the results? Datagrids or flexgrids are one option, but I would like the user to be able to click on the name (if there is more than one Smith for example) and have that name/address populate the fields and I am not sure if you can do that with the grids. Any suggestions would be greatly appreciated.
Cheers
Strange Query Results.
The code:
Code:
Private Sub cmdSubmit_Click()
If ars.State = adStateOpen Then ars.Close
sql = "SELECT Scores.Score, Shooters.FName, Shooters.LName FROM Scores, Shooters WHERE Scores.MatchDate = #" & cboDate.Text & "#"
ars.Open sql, adcn, adOpenDynamic, adLockReadOnly
Do Until ars.EOF
lstResults.AddItem ars.Fields("Shooters.LName").Value & ", " & ars.Fields("Shooters.FName").Value & " " & vbTab + ars.Fields("Scores.Score").Value
ars.MoveNext
Loop
ars.Close
End Sub
The happenings:
Date 3/31/04 scores were entered for all members and if that date is used the results are correct.
3/30/04 or any other date that was listed all the members are shown with the same score. Not sure why it's doing this any ideas?
Access Query Results To VB
Having a problem viewing my query results.
I have a VB form with several textboxes on it
that I add, update and delete user info, using
an access back end.
I'm trying to select specific data from the database
for example, a users location... once it finds it send
it to the vb form updating or populating the other
textboxes as well. I'm using a sql select statement
to search for the specific criteria, and that part is working
ok... my question is how do I get the data to populate my
form. Should I loop through the recordset, and if so how, or
is there some other method I'm missing.
See example below!
Select * from tblContractor where location= txtsite.text
what code do I need to populate the textboxes below
Txtuser name
txtNumber
txtPrivileges
txtCompany
txtSite
THanks ~ hope this makes sense
|
TRACKER
