Writing An Excel Cell To An Access Database
I am importing a word document into Excel then using a macro to import the data to a template. When I run the macro, the last procedure is to write a few cells to an Access database. Is this possible???????
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Adding A Combo Box In An Excel Cell With Values From An Access Database
I wish to add an editable combo to all the cells in one of the columns of an Excel spreadsheet in such a manner that the values in the combo box's list come from a field in an MS Access database. I also want the combo box to be editable to allow the user to enter text other than the text in the list. How do I achieve this?
Writing Excel Cell Attribute
I am using the following code to write an excel spreadsheet with VB6. I need to set some cells format type to "Text" or "String" as the field has a zero as the first character. How can I set individual cell types?
vb Code:
'****************************************************** ' Write all the members of aray to output spreadsheet '****************************************************** With outBk For x = 0 To ict - 1 x1 = Int(x / 26) ' Use to see if > 26 rows x2 = x Mod 26 ' Set letter within range xcell = "" If x1 > 0 Then xcell = Chr$(x1 + 64) ' Set first of two character xcell = xcell & Chr$(x2 + 65) & Format$(outrow) ' Set last character .Range(xcell).Select .ActiveCell.FormulaR1C1 = aray(x + 1) Next End With
Thanks much!
Writing EXCEL Cell Attribute To Spreadsheet
I am using the following code to write an excel spreadsheet from vb6. I need to set the "text" attribute on for some of the cells because a character field happens to be a number and it gets interpreted as a number when the leading character is a zero. Thus, 096 becomes 96. If I go into an excel spread sheet and format the cell to "text" it works fine. Just need means to set cell type (or column type/format) to "text".
With outBk
For x = 0 To ict - 1
x1 = Int(x / 26) ' Use to see if > 26 rows
x2 = x Mod 26 ' Set letter within range
xcell = ""
If x1 > 0 Then xcell = Chr$(x1 + 64) ' Set first of two character
xcell = xcell & Chr$(x2 + 65) & Format$(outrow) ' Set last character
.Range(xcell).Select
.ActiveCell.FormulaR1C1 = aray(x + 1)
Next
End With
Thanks!
How To Link An Excel Cell To An Access Table Cell
I am trying to import an populated excel form into access using just specific cells (where the data is located) only. I am able to open excel and get the data from the cells but how can i assign them back to my access table? This is like importing a populated excel form into access database.
I used these to get my excel info
Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = False
excel_app.Workbooks.Open FileName:=mypathname & myfilename
Set excel_sheet = excel_app
x= excel_sheet.cells(10, 4).Value 'To get the excel cell value'
y= excel_sheet.cells(12,4).Value
Now I want x to be assigned to table "AppendTable" in column "AppColumn1"
and y in column "AppColumn2"
help!!!
Reading The Contents(formula) Of An Excel Cell And Writing It To A Second Sheet
hello
is it possible to read the contents of an excel cell(the contents being a formula) e.g. reading the cell A30 which has the value
value a30 =c30*e30
and then storing it to a variable say cellstore in VB and then writing a contents of cellstore (which should be =c30*e30) to the second sheet in the workbook
is it possible to use say cell.formula if there is such a thing?
Writing A Cell Address Of A Cell In 1 Worksheet To A Cell In Another In Same Workbook
Hi……..
Its an urgent requirement….
I have an excel sheet. I need to write the macros in VB for the following requirement:
There are three worksheets in a single workbook.. worksheet3 contains some values in every cell. whenever user writes in something in the cell in worksheet1, the column number and row number from worksheet3 must get written automatically in the adjucent cell in worksheet1 only.
For example:
A worksheet 3 contains PLZ A,B in row 5 and column 7. These rows and columns are user defined. If i write PLZ 31, 20 in F26 in worksheet 1 then automatically G26 must contain value 75, G27 must contain a value 31 and G28 must contain a value 20.
I can't say when this value should get populated. Either on the lost focus of F26 or i need to run a macro manually for F26.
what would be the code in both the cases? Is the former case possible? If i select group of cells like F26 present in worksheet1 and altogether run a macro then would it give the result?
Thanks in advance for your reply.
Regards...
Priyadarshini
I Am Exporting An Access Query To Excel, How Can I Define The Excel Cell Size/format
I am using TransferSpreadsheet to Export a Query to Excel with a button from Access.
How can I define in Excel when I export it, the size of the cells, the type of letter (Arial, Bold), The Background color.
Private Sub Impacto_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:="Impact", _
Filename:="C:Impact.xls", _
hasfieldnames:=True
End Sub
Find Location Of A Cell In Excel-import From Excel Into Access
Hi
I want to import some columns from an excel spreadsheet into an access table using vb6.
I am going abt this by finding the location of the cell but am having problems this way
Code:
Private Sub ImportExcelAttend()
Dim imA As New ADODB.Recordset 'Recordest for temp table
Dim ex As New Excel.Application
Dim I, J, a As Integer
Dim TheFile As String
imA.Open "select * from tbl_tempAttend", MyConn, , adLockOptimistic
ex.Workbooks.Open txtLocation.Text
ex.Cells(I, J) = "%att"
however i cannot get it to work. any help this way would be appreciated.
OR if you know another way to import these few colums with unknown location of cells into access i would be very grateful. Location will always change every time.
Regards
JohnnyH
Msflexgrid Directly Writing On The Cell,resize Cell Auto?
Hello,
First:***** MSFlexgrid control:when I click the control , I want to access to grid and directly write on selected cell , not to use with inpubox or textbox...etc.How?
Second:*****How can I let the msflexgrid control resize its cell width according to text length?
Thanks......
Copying Formula From Cell To Cell When Writing VB Code
I am a VB beginner and have so far created a macro to
insert a new line in a spreadsheet but cannot work out how
to copy a formula into one of the new cells.
The spreadsheet consists of a row of headings across 8
columns and currently holds data for 3 members of staff in
the 3 rows below the headings. Headings are staff name, ID
No, MON thru FRI and then a weekly total (for each
employee) in column 8. In the row below the staff names I
have a daily total for each day for all staff. The Data
is "Hours worked".
I have created a macro to insert a new line, in the event
a new staff member joins the company. I seem to have made
this work so far but come up against a problem after the
line is inserted. There are formulas all along the bottom
row for each day and also in column 8 for weekly totals.
When the new line is inserted column 8 of the new line has
no formula. How do I copy it in in VB code? Also the new
row is not formatted as far as borders go so I am left
with the new row with no borders. Can anyone suggest ways
of overcoming this problem. I would appreciate any help
Here is my primitive VB code:
Sub InsertNewMechanic()
'
' Insert New Mechanic Macro
' Macro created by Carl Strevett to insert new line for
entry of new mechanic
'
'
' New Entry InsertNewMechanic
'
' Set variable
Let index1 = 1
'Go to Home cell "A1"
ActiveSheet.Range(Cells(1, 1), Cells(1, 1)).Select
'Find Daily Total
Do While ActiveCell <> "Daily Total"
Let index1 = index1 + 1
ActiveSheet.Range(Cells(index1, 1), Cells(index1,
1)).Select
Loop
'Insert Line
Selection.EntireRow.Insert
'Find Column Eight
Let index1 = index1
ActiveSheet.Range(Cells(index1, 8), Cells(index1,
8)).Select
' Move up one row
Let index1 = index1 - 1
ActiveSheet.Range(Cells(index1, 8), Cells(index1, 8)).Copy
' Copy Formula
Let index1 = index1 + 1
ActiveSheet.Range(Cells(index1, 8), Cells(index1,
8)).Select
End Sub
Cheers, Carl Strevett
Writing To MS Access Database
I have a VB6 application that uses ADO to write to an MS Access database, via an ODBC link. I am writing entries to a log file in Access depending on events happening in my application.
My problem is that the process works fine in debug mode, but in normal mode it doesn't always write the records. Should I be putting in something to slow it down like a wait or something, or there a magic answer.
Any suggestions would be most welcome, thanks.
Writing To An Access Database.
Currently I am coding a program in VB to use an access database. After much hard work and help off here I can read from the database into a felxi grid and then use the grid to get data. This is done using the code below that has SQL within it.
Can someone help me write to the database now? Do I use the same code or something else. I need to be able to add records and amend them too.
Thanks so much for all the help.
Code:
Private Sub fillFrommatch()
' Open Connection
Set m_adoCN = New ADODB.Connection
With m_adoCN
.Provider = _
"Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"C:Documents and SettingsRichardDesktopStrikerPCStrikerDBase.mdb"
.Open
End With
' Open Recordset
Set m_adoRS = New ADODB.Recordset
With m_adoRS
.ActiveConnection = m_adoCN
.Source = _
"SELECT * FROM [Match] order by ID"
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
' Populate FlexGrid
Set MatchGrid.DataSource = m_adoRS
m_adoCN.Close
End Sub
Writing To Access Database From VB.NET
Hello,
I am working on a school project that has me reading and writing to an access database. It is a program that allows for foreign currency exchange. I can read the database but I cannot write to it. I keep getting a "No value given for one or more required parameters" Unhandled Exception. I have turned off the primary key in the database to try and get it to write and ensured there was no required fields.
Private Sub cmdpur_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdpur.Click
Code:
Dim uspur As Decimal = 0
Dim jpypur As Decimal = 0
Dim europur As Decimal = 0
Dim cnypur As Decimal = 0
Dim usdamt As Decimal = 0
Dim cnyamt As Decimal = 0
Dim jpyamt As Decimal = 0
Dim euramt As Decimal = 0
Dim offamt As Decimal = 0
Dim transamt As Decimal = 0
Dim trans As Integer
Dim transdate As Date = Date.Now
Dim message As String
Dim name As String
Dim acctno As Integer
Dim dt As DataSet = New DataSet
Dim connstr As String = "provider = microsoft.jet.oledb.4.0;" & _
"data source = l:prog4db.mdb"
Dim sqlstr As String = "select * from transtable"
Dim sqlin As String = "insert into transtable (trans, transdate, acctno, name, usdamt, cnyamt, jpyamt, euramt, transamt, offamt) values (trans, transdate, acctno, name, usdamt, cnyamt, jpyamt, euramt, transamt, offamt)"
Dim dataadapter As New OleDb.OleDbDataAdapter(sqlstr, connstr)
Dim datawriter As New OleDb.OleDbDataAdapter(sqlin, connstr)
dataadapter.Fill(dt)
dataadapter.Dispose()
uspur = Convert.ToDecimal(txtusdpur.Text)
jpypur = Convert.ToDecimal(txtjpypur.Text)
europur = Convert.ToDecimal(txteurpur.Text)
cnypur = Convert.ToDecimal(txtcnypur.Text)
txtpurtot.Text = uspur + jpypur + europur + cnypur
If txtpurtot.Text <> txtcurramt.Text Then
MessageBox.Show("Purchase amount does not equal starting amount", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
If rbusd.Checked = True Then
If cbusdbuy.Checked = True Then
MessageBox.Show("You are attempting to purchase the same currency as starting currency", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
If cbcnybuy.Checked = True Then
cnyamt = txtcnypur.Text * txtcnyrate.Text
usdamt = usdamt - cnyamt
offamt = usdamt
transamt = txtpurtot.Text
name = InputBox("Enter the name of the account holder")
acctno = InputBox("Enter the account number")
datawriter.Fill(dt)
datawriter.Dispose()
Any help is appreciated
Thank You,
Matt
Writing Values To MS Excel From Database
Hi,
Have a quick question on a problem thats been driving me nuts all night. Here is what I am looking to do. I am accessing a MS Access database and grabing files and putting them into a recordset. I then want to put some of the information from this recordset in an Excel template which is opened up.
Here is where I am stuck. I can get all the way to the point of opening up the Excel file. However once opened up I cannot get the recordset data to print in the cell at all.
Here is my code that does not work. It runs like it processes everything except the part that writes to the opened Excel file.
Code:
Set oExcel = Excel.Application
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open("C:Path To File.xls")
Set oWS = ActiveWorkbook.Worksheets("Cover")
While Not rsCust.EOF
With rsCust
If IsNull(.Fields("CompanyName").Value) Then
oWS.Cells(11, 3) = "Test" 'Places Test If No Value For Company Name
Else
oWS.Cells(11, 3) = rsCust!CompanyName 'Places Company Name If There Is A Value
End If
.MoveNext
End With
Wend
This code however will work and will print Test in the cell on the opened Excel file.
Code:
Set oExcel = Excel.Application
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open("C:Path To File.xls")
Set oWS = ActiveWorkbook.Worksheets("Cover")
oWS.Cells(11, 3) = "Test" 'Why Will This Only Work If This Line Of Code Is In This Spot??
While Not rsCust.EOF
With rsCust
If IsNull(.Fields("CompanyName").Value) Then
oWS.Cells(11, 3) = "Test" 'Places Test If No Value For Company Name
Else
oWS.Cells(11, 3) = rsCust!CompanyName 'Places Company Name If There Is A Value
End If
.MoveNext
End With
Wend
Thanks for your help, and if you need me to elaborate any further to clear up any questions please let me know.
Undeleting Cell In Access Database
I just used the application wizard to create my first simple database program. The flexgrid loaded all the data fine and I was able to edit cells fine.
After someone makes changes to a cell, how do I set up an undelete in code?
Is It Possible To Import One Excel File As One Cell Into Database?
Hi, all,
I have many excel files, each of them has some data and a chart. I am wandering if it is possible that in Access, we can have a table or something which we can import one individual file as one cell of the table. Or we can a access table just with the file name and the link to the access file?
If it is not possible, any suggestion to arrange these hundreds of excel files? It really looks messy to me now.
thanks.
Read/writing To An Access Database
Hi,
I am trying to set up a user login system which reads and writes to an access database. could anyone help me with some cose which allows me to connect to a database and read/write to it?
Many Thanx
jjohall2000
Problems Writing To MS Access Database
Here’s the scenario…
I have 2 Combo Box’s, 2 Text Box’s and a Command Button on a Form.
cboName / cboUnit / txtDayPrice / txtNightPrice / cmdApplyPrices
Location of Database - C:Database Code TestDatabase.mdb
Tables - Fields: -
Names – Ref, Name
Units – Ref, Display_Name
Prices – Ref, Names_Ref, Units_Ref, Day_Price, Night_Price
Reason for 3 Tables - I have plans to expand the Names and Units Tables
Each Combo Box is populated from the Database, as follows: -
cboName: -
Table – Names
Field – Name
cboUnit: -
Table – Units
Field – Display_Name
When I click the Command Button, I want it to put the Ref of the Item selected in cboName into the Names_Ref Field of the Prices Table, the Ref of the Item selected in cboUnit into the Units_Ref Field of the Prices Table, the txtDayPrice.Text into the Day_Price Field of the Prices Table and the txtNightPrice.Text into the Night_Price Field of the Prices Table
I’m pretty sure I’ll be fine getting it to store the values in my Text Box’s in the correct fields, so if anyone could give me a hand getting it do the other bit, I’d much appreciate it.
I’ve tried using the following code, which worked fine with the Text Box’s, although now I’ve added code to try and do the rest, it just spits out errors: -
Private Sub cmdApplyPrices_Click()
Dim cnConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim dDayPrice As Double
Dim dNightPrice As Double
Dim rsNames As New ADODB.Recordset
Dim sSQLNames As String
Dim rsUnits As New ADODB.Recordset
Dim sSQLUnits As String
cnConnection.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" _
& "C:Database Code TestDatabase.mdb"
cnConnection.Open
Set cmdCommand = New ADODB.Command
dDayPrice = txtDayPrice.Text
dNightPrice = txtNightPrice.Text
sSQLNames = "SELECT `Name`, `Ref` FROM `Names` WHERE Names.Name = " & cboName.Text & ""
rsNames.Open sSQLNames, cnConnection, adOpenKeyset, adLockPessimistic
sSQLUnits = "SELECT `Display_Name`, `Ref` FROM `Units` WHERE Units.Display_Name = " & cboUnit.Text & ""
rsUnits.Open sSQLUnits, cnConnection, adOpenKeyset, adLockPessimistic
With cmdCommand
.ActiveConnection = cnConnection
.CommandText = "INSERT INTO Prices (Names_Ref, Units_Ref, Day_Price, Night_Price) VALUES ('" & rsNames.Fields("Ref") & "','" & rsUnits.Fields("Ref") & "','" & dDayPrice & "','" & dNightPrice & "')"
.CommandType = adCmdText
.Execute
End With
cnConnection.Close
rsNames.Close
rsUnits.Close
End Sub
Cheers!
Kirk
Writing/Reading To Access Database
I am looking for help to write and read data to an access database, i have a form created in VB and i want to allow the user enter data to this form and it will be written to the access database.
can anyone help?
Need Help With Opening And Writing To A Excel File From A SQL Database
Hi all,
I attached a excel file for everyone that helps to look at...
This xls file comes to us and I import it into our SQL database (OrdersTable). So the data is in the table. I update the table with the invoice#, Tracking#, Shipdate, amount from our shipping machine.
The vendor that send this file to us wants us to add and populate the invoice#, tracking#, shipdate, and amount back onto this exact excel file and send it to them.
I am able to have all the information in the OrdersTable with a column of newtrackingno marked as True That will let me know of the new shipped orders.
Is it possible to open the exact excel file as a database table and then query thru it to populate the certian fields I need to with the ponumber being the Key in both the SQL database table and the excel file and update it?
Please look at the excel file and you will see what gets sent and what needs add to the file before sending it back..
Also this excel file could have a large amount of orders on it.
If this is possible could someone show me a sample code to get me going on this.
Thanks
Clark
Writing To Access Database At A Scheduled Time
Hi,
Is there a way to read records from a text file and read them into an Access Database at a specific time everyday?
I've done this with VB6 & Oracle database, where I will scheduled the exe file to run everyday at say 2pm.
But is it possible with Access?
Please help!!! coz it's been quite a while since I used the Access database!
Thanks!!
Writing Tab Delim Txt File To Access Database
Have a dilema!
Does anyone know of a way in which you can get a tab delim txt file into an access database automatically using a VB progam. The text file will be big (data wise) ...bout million records or so. And have 10 fields.
Any ideas / coding would be much appreciated!
Cheers in advance
Tom
Issue Writing Data To An Access Database
Here is my problem.......
I have a read-only (I have also tried it as r/w) datagrid populated by a dataset. Another event adds a new row to the datagrid. This row shows up correctly on the datagrid. I use the following bit of code to update the Access db.
DsCustomer1........dataset
Customer...........table
CustomerDetail.....structure containing data to write to db
Dim rowCustomer As DataRow = DsCustomer1.Customer.NewRow
rowCustomer.Item("NameLast") = CustomerDetail.NameL
rowCustomer.Item("NameFirst") = CustomerDetail.NameF
rowCustomer.Item("Address") = CustomerDetail.Address
rowCustomer.Item("City") = CustomerDetail.City
DsCustomer1.Customer.Rows.Add(rowCustomer)
The problem is that the data never shows up in the Access table. I have to be missing something simple.....but for the life of me I can't figure it out.
I'm Having Problems Writing Info To An Access Database
I am trying to write info from text boxes to an Access 2000 database. My primary key in the database is set to Autonumber. I am trying to write to field2, field3, field4 etc. in the database from textbox2, textbox3, textbox4 etc. from the form. I do not write to filed1 in the database because that's my autonumber primary key. I am using a data environment to access the database. Can someone help me out? Here is my code.
Private Sub cmdAdd_Click()
With dataEnvironment1.rsconTable1
.Open
.MoveLast
'''''''''''''I don't use field1 because of the autonumber''''''''
!field2 = text2.text
!field3 = text3.Text
!field4 = text4.Text
.Update
.Close
End With
End Sub
When I hit the command button the info gets written to the database, but it only overwrites the very last record in the database. I can't seem to figure out how to add a new record in the database. I think it might be a problem with the autonumber. Can someone give me some suggestions? Thanks a bunch!!!
Access Database Reading/Writing Problem
Hi
I am using MS-Access 2000 as a database and vb as programming language. When I try to write a database file and then reload it in the same function or flow, sometimes it fails.
Reason seems that it is not finished writing yet.When I use sleep 500 or so then it works fine.
Can anybody provide the right solution.
I am using ADODB.Connection for connection and ADOX for database creation.
Error Help Writing From Access To Excel
Hi,
This is a very odd error that I cannot figure out at this point. I keep getting the error "Application defined or object defined error", which seems to be overly broad.
I take data from access tables and output it to excel spreadsheets. I output to excel colums A through AC. When the data I'm writing to the spreadsheet is coming from the recordset that I'm using I have no problem. However, every few rows, I have to run a total row, it's in the total row where I have a problem. The code for writing out the total row is below.
Code:
ActiveCell.offset(row, Crb) = Risk_book_hold
ActiveCell.offset(row, Cbranc) = branch_hold
ActiveCell.offset(row, Crd) = run_date_hold
ActiveCell.offset(row, Cdn) = Deal_Name_hold & " Total"
'F
ActiveCell.offset(row, Cnc) = "=SUM(F" & top & ":F" & bottom & ")"
'I
ActiveCell.offset(row, Clo) = "=SUM(I" & top & ":I" & bottom & ")"
'J
ActiveCell.offset(row, Clco) = "=SUM(J" & top & ":J" & bottom & ")"
'K
ActiveCell.offset(row, Clsf) = "=SUM(K" & top & ":K" & bottom & ")"
'L
ActiveCell.offset(row, Claf) = "=SUM(L" & top & ":L" & bottom & ")"
'M
ActiveCell.offset(row, Clacf) = "=SUM(M" & top & ":M" & bottom & ")"
'N
ActiveCell.offset(row, Clfcf) = "=SUM(N" & top & ":N" & bottom & ")"
'O
ActiveCell.offset(row, Cluf) = "=SUM(O" & top & ":O" & bottom & ")"
'P
ActiveCell.offset(row, Clongoinglcf) = "=SUM(P" & top & ":P" & bottom & ")"
'Q
ActiveCell.offset(row, Clotherlcf) = "=SUM(Q" & top & ":Q" & bottom & ")"
'R
ActiveCell.offset(row, Clr) = "=SUM(R" & top & ":R" & bottom & ")"
'S
ActiveCell.offset(row, Cof) = "=SUM(S" & top & ":S" & bottom & ")"
'T
ActiveCell.offset(row, Clmie) = "=SUM(T" & top & "T:" & bottom & ")"
'U
ActiveCell.offset(row, Clatii) = "=SUM(U" & top & "U:" & bottom & ")"
'V
ActiveCell.offset(row, Clii) = "=SUM(V" & top & "V:" & bottom & ")"
'W
ActiveCell.offset(row, Clcofe) = "=SUM(W" & top & "W:" & bottom & ")"
'X
ActiveCell.offset(row, Clni) = "=SUM(X" & top & ":X" & bottom & ")"
'Y
ActiveCell.offset(row, Clglos) = "=SUM(Y" & top & ":Y" & bottom & ")"
ActiveCell.offset(row, Cfo) = Risk_book_hold
'AC
ActiveCell.offset(row, Clri) = "=SUM(AC" & top & ":AC" & bottom & ")"
The last row printed is:
'S
ActiveCell.offset(row, Cof) = "=SUM(S" & top & ":S" & bottom & ")"
I get the error "Application defined or object defined error" when it tries to process the following line of code.
'T
ActiveCell.offset(row, Clmie) = "=SUM(T" & top & "T:" & bottom & ")"
I have tried everything to debug this and have no idea what's going on. Nothing changes from one row to the next, except the row itself. The variables top and bottom just mark the rows that should be summed in that column.
Any help or illumination would be greatly appreciated. Thanks.
Writing/Appending To Access From Excel (VBA)
I have searched this forum and some others and have been unable to find anything useful on this topic.
I am trying to write to and append data within an Access database from VBA code within Excel, I can query the Access database using a SELECT statement that gets built within the VBA but if I try using INSERT INTO I am unable to get it to work.
If anyone could point me in the right direction on this matter I would be very grateful.
Thanks,
Rich
The code I was using to query the data is this:
Code:
Sub checkSiteData()
Dim sSQl, siteID As String
siteID = "G01"
sSQl = "SELECT tblSiteData.[Site ID], ....................... tblSiteData.Comments " & _
"FROM tblSiteData " & _
"WHERE (((tblSiteData.[Site ID])='" & siteID & "'));"
With Sheets("tempSiteData").QueryTables(1)
.Destination = Range("A1")
.Sql = sSQl
.BackgroundQuery = False
.Connection = "ODBC;DSN=MS Access Database;DBQ=G:Site Data.mdb;DefaultDir=G:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.Refresh
End With
End Sub
Edited by - rickp101 on 7/8/2004 8:55:28 AM
Problem With " And ' Chars When Writing To Access Database
I'm very much a newbie and I'm not sure where to look to solve this.
I have an access DB that I'm writing to using SQL statements. I'm using the ADODB object as discussed in the VB Database Tutorial elsewhere on this site.
When writing a string of characters to the table's field in the DB, if the string contains a single quote or double quote character I'm getting an error in the SQL statement. I understand why, it's because the ' or " in the string is getting interpreted as the end quote needed for the value of a field.
For example, here's an example of the SQL statement I'm sending to the DB:
INSERT INTO Topic (DocumentName, ChapterTitle, TopicTitle, Notes, DateModified, IsAddenda, ProjectName) VALUES ('Test_11_Hyper.doc', 'Creating HyperView Reports
', 'Example of Using Basic Scripting to Dynamically Change the Gauge's Value
', 'Test notes...','9/26/2003', False, 'PC-DMIS 3.6'
(In this particular command, the value for TopicTitle is where I'm having the problem)
My code looks like this:
strSQL = "INSERT INTO Topics (DocumentName, ChapterTitle, TopicTitle, Notes, DateModified, IsAddenda, ProjectName) VALUES ('" & strFileName & "', '" & strChapterTitle & "', '" & strTopicText & "', '" & strNotes & "', '" & dteDate & "', " & blnIsAddenda & ", '" & strProjectName & "')"
'MsgBox strSQL 'Used for testing
myDB.Execute (strSQL)
How do I make it so that these characters in the strings don't get interpreted as a value terminator?
Reading, Writing And Deleting Records In Access Database, Please Help!
Hia!
I'm a total amateur to VBS but need help on a VBS script. This is used in a HMI system from Siemens called WinCC, used for process viewing and control.
What I need to is to write to a table in Access, then read these values and then delete records (rows) in Access.
By using help functions in WinCC I have this set up:
I created an Access database with the WINCC_DATA table and columns (ID,
TagValue) with the ID as the Auto Value.
Then I can write to Access by the following code:
Dim objConnection
Dim strConnectionString
Dim lngValue
Dim strSQL
Dim objCommand
strConnectionString = "Provider=MSDASQL;DSN=SampleDSN;UID=;PWD=;"
lngValue = HMIRuntime.Tags("Tag1").Read
strSQL = "INSERT INTO WINCC_DATA (TagValue) VALUES (" & lngValue & ");"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = strSQL
End With
objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing
This actually works fine, pure luck I guess!
Can anyone help me with scripts for:
1. Reading one record (row) according to a ID number
2. Deleting the a record (row) according to a ID number
A complete script would of course be the best thing for a "stupid" amateurlike me! But I would be very thankful for any help!
Best regards
Vadar
Problem Reading And Writing Pictures From Database(Access Or MS-SQL 7.0)
Hi Friends,
I'm Attaching a table in Access having one column with datatype "Picture". Please tell me how to insert picture into that table using VB. and same tell me how to read that picture into my imagebox or picturebox using VB.
MS-SQL 7.0 also has same datatype "Image", Tell me how do i do same things with that using VB
(solved) Writing Image From Access Database Into A File
Hi there!
I have a problem with saving an bmp which is stored in an access file (2000) to my hard disk.
I've seen the posts around here about this topic ... tried quite a few things, but couldn't get it to work so far.
Here is my code:
Code:
Dim strTempPic As String, lngImageSize As Long, lngOffset As Long
Dim varChunk, varBild
lngOffset = 0
lngImageSize = 0
lngImageSize = rs.Fields("Bild").ActualSize
Do While lngOffset < lngImageSize
varChunk = rs.Fields("Bild").GetChunk(conChunkSize)
varBild = varBild & varChunk
lngOffset = lngOffset + conChunkSize
Loop
Debug.Print LenB(varBild)
strTempPic = App.Path & " est.bmp"
Open strTempPic For Binary As #1
Put #1, , varBild
Close #1
(see also complete sourcecode with db and images in zipped attachment)
the variable varBild seems to contain the right image as the size looks correct [~15kb], but the saved file I got was much smaller (~8kb).
Thanks a lot for your help.
Nina
How Do You Code In Visual Basic Writing To An Access Database?
I currently am writing information captured on a form to
a text file which I am importing into Access with VB. I now want to write the information directly to access. What are the steps invloved in doing so. Anyone's help would be
greatly appreciated!!!
Thanks,
Bill
b.ward@vanderbilt.edu
Writing To An Access Database RUNTIME ERROR 3251
I am trying to write a variable to an ACCESS database. I setup the ODBC Connection without problem and part of the app already pulls in data from the database etc. However when I run the app and attempt to write to an ACCESS Table from the VB application I get a RUNTIME ERROR '3251'
"The operation requested by the application is not supported by the provider"
Here is my code:
...[Prior does other functions and captures the variable]...
Dim adoConnection As ADODB.Connection
Dim rst As ADODB.Recordset
Dim connectString As String
' Create a new connection
Set adoConnection = New ADODB.Connection
' Creat a new Recordset
Set rst = New ADODB.Recordset
' Build connection string
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:FFLEY2003.mdb"
adoConnection.Open connectString
' Open Table and write the player to the team
rst.Open "Master_Teams", adoConnection
rst.MoveFirst
Do While Not rst.EOF
If IsNull(rst!Team1) Then
rst!Team1 = playerSelected ERROR HERE!
GoTo closeRecords:
End If
rst.MoveNext
Loop
closeRecords:
rst.Close
Set rst = Nothing
getout:
End Sub
Changing Cell/row Fontsize In Excel From Access
Using Excel 97 and Access 97.
In the code i have below (In an Access module) which works fine, i want to be able to change the fontsize and background color of the title (the bit in bold below) in the active Excel spreadsheet. Does anyone know how to do this please ?
Can i do something like......
XLapp.ActiveSheet.Cells(1,1).Color = SomeColor
just can't seem to nail the syntax down for it.
Code:
Open FileLocation + outputDir + outputName For Output As #1
''''Put in chart title
XLapp.ActiveSheet.Cells(1, 1) = currentUID
XLapp.ActiveSheet.Cells(1, 2) = Left(outputDir, Len(outputDir) - 1)
For I = 1 To Rcount1
Print #1, Description(I) + tb + "<B>" + ThisYear(I) + "<$>" + tb + LastYear(I)
XLapp.Sheets("Summary of Response Rates").Select
''''Add values to checking spreadsheet
XLapp.ActiveSheet.Cells(I + 1, 1) = Description(I)
XLapp.ActiveSheet.Cells(I + 1, 2) = ThisYear(I)
XLapp.ActiveSheet.Cells(I + 1, 3) = LastYear(I)
Next I
Close #1
End Function
thanks
Paul
Please Help Me Its Urgent, How To Select A Cell In Excel From Access.
Hallo friends,
This is Kiran I have been trying to open an excel file and search in it for a specific number and select that cell from access.
Previously I got a problem that it is always opening a new copy of the file but I have solved this problem by using GetObject( ) function which is opening every time only the same file but if I open a file with this function.
The selection of a cell is working If there is only this file is opened , if another Excel file is also opened then the line which has selecting a cell in the excel file is giving an error.
But our users will definitely open other excel files when they are working with my project so I must find a way which can work even other excel files are opened.
As I spent so many days on this, now am in a peek stage please help me. Any help will be great for me.
Here is my code.
Set myXl = GetObject("C:Dokumente und EinstellungenKiran KarnatiDesktopEXCELBook2.XLS")
XlBookCnt = myXl.Parent.Windows.Count
If XlBookCnt > 1 Then
For i = 1 To XlBookCnt
If myXl.Parent.Workbooks(i).FullName = "C:Dokumente und EinstellungenKiran KarnatiDesktopEXCELBook2.XLS" Then
myXl.Application.Visible = True
myXl.Parent.Windows(i).Visible = True
Label48.Caption = myXl.Worksheets("Sheet1").Cells(1, 2).Value
myXl.Worksheets("Sheet1").Cells(1, 10).Select
End If
Next i
Else
myXl.Application.Visible = True
myXl.Parent.Windows(1).Visible = True
Label48.Caption = myXl.Worksheets("Sheet1").Cells(1, 2).Value ‘ ß this line is has error
myXl.Worksheets("Sheet1").Cells(1, 10).Select
End If
Search And Select A Cell In Excel From Access
Hallo everybody,
I have searched our forums to find wether any thread that can help me but unfortunately I did't find it.
I found somethings but I don't need that much of complexity because I just have to open an excel file in a specific location and I have to search a for value in that sheet and I have to select that cell .
I have to do this when the user clicks a button on an access form.
I wrote a code and its opening the file and also the specific sheet but not searching beacause I don't know how to get the value of that cell and also I have abserved that it is working only alternative times not everytime. I did't understand why it is working so...
please help me... this is the code that I wrote
Set xlApp = New Excel.Application
varPath = "C:Dokumente und EinstellungenKiran KarnatiDesktopEXCELBook2.xls"
With xlApp
.Visible = True
' and now I have to open a perticular Workbook whose path is in varPath variable and Perticular Sheet
Set xlWB = .Workbooks.Open(varPath, , False)
With xlWB.Sheets("Sheet1")
For i = 1 To 100
s1 = "C" & i
s2 = Range(s1).Value
If Val(s2) = 100 Then
Range(s1).Select
End If
Next i
End With
End With
Thank you in advance.
Kiran.
Find Excel Cell Value In Access Field
hello,
I'm new to all of this and i'm finding myself overwhelmed with my current task, so I was wondering if i could possibly get some help because it's really important to me
Currently i'm trying to write a module access (visual basic) in order to find the value of an excel cell in an access database
basically, i'm trying to go through each cell in excel (in the 2nd column) and see if it is anywhere in the 2nd field of access. if so...i'm trying to copy that specific row in access and paste it into a new table in access
thanks in advance.
Writing An Access Query To Excel File (.xls)
Hi there!
I'm usin VBA to make applications in Access97, I want to run a query and get a result collection, and write the collection to a Excel file (.xls)
I found a function for it with a parameter xls but that is a year ago and I lost the note. may be some docmd.
Thanks for the help.
Excel Writing Data To/from Access Table?
I have decided to try to use an Access Table as an external datasource and have ODBC handle the data tranfer. Can this be done to where excel acts like the front-end of a database and writes data back to that table, adds records etc., instead of just importing the data.
Writing Vba To Launch Excel Charts From Access
I have an access program where the user can click a button which produced a report for them. But now they want to be able to view that report in graph format. As there are no graph type facilities in Access, I think what I need is to be able to open excel through code, pass the data used in the report and somehow get that to automatically display as a graph.
Is that even possible?
Can I do something with ole objects?
Exporting An Access Memo Field To An Excel Cell
I have a fairly simple application that I am building with the Access-VB editor and I am unable to get a memo field to copy over successfully. The chunk of code that is passing the access field to the excel object looks like this:
Set xlApp = New Excel.Application
Set xlWkbook = xlApp.Workbooks.Open("C:Documents and Settings
yan.obrayDesktopAccess DB TestFeeder.xls", 0, False)
For Each xlWksheet In xlWkbook.Worksheets
'Take a value from the database and insert it into the excel document
Dim rsTemp As New ADODB.Recordset
rsTemp.Open "SELECT * FROM system WHERE System.System_Acronym='" & sSystem & "'", CurrentProject.Connection, adOpenStatic
xlWksheet.Cells("2", "B") = rsTemp("System_Name")
xlWksheet.Cells("4", "C") = rsTemp("System_Acronym")
xlWksheet.Cells("4", "J") = rsTemp("System_DSD")
xlWksheet.Cells("9", "A") = rsTemp("System_Description")
xlWksheet.Cells("6", "B") = rsTemp("System_Type_ID")
xlWksheet.Cells("6", "N") = rsTemp("System_Category_ID")
xlWksheet.Cells("6", "F") = rsTemp("TEI_ID")
xlWksheet.Cells("4", "P") = rsTemp("System_Last Update")
Next xlWksheet
Everything is fine except for the line "xlWksheet.Cells("9", "A") = rsTemp("System_Description")". When the System_Description of whatever row in the recordset is being passed is small (under 256 characters I'm assuming) it shows up fine in the cell that I specified but if it is larger than 256, "#VALUE!" is all that shows up in the cell. The System_Description is a memo field so I'm sure that has something to do with it but I don't know how to work around this.
Importing Excel Cell Contents Into Access Variables
I am trying to import values from Excel cells into variables in Access.
I have used the transferspreadsheet method to import a range into a table (which works fine) but i would like something similar to import the results into a variable. could the transferspreadsheet method work to import into something other than a table?
Pb With Cell Content Type Using ADO To Access Excel File
I used ODBC to access an Excel file. The problem is that ODBC seems to determine by itself if a column must contain numeric or alphabetic fields even if all the cells of the Excel table are defined as Text format. And if a numeric value exists in an alphanumeric column the value returned by ODBC is Null instead of the numeric value, and same if alpha in numeric column
Is there a possibility to force ODBC to read all the cells as alphanumeric values?
Thank you very much for an answer
Marie-Claire
Here is the code:
Private Sub RunButton_Click()
Dim sTablePath As String
Dim SQL As String
Dim sValA As String
Dim sValB As String
Dim iLine As Integer
' Table content
' 0 ABCD
' 1 1 ----------------------------------> here result 1 Null
' A A ----------------------------------> here result Null A
' 3 $%02
' 4 B
' 5 1C
' #1 1 ----------------------------------> here result Null 1 (because 1 has a specific style in Excel as text)
' 8 E
' 9 1 1
' Open a connection to access to excel table
Set GV_conn = New ADODB.Connection
sTablePath = "D:Test_ExcelTest_excel_adoNumericAlpha.xls"
GV_conn.Provider = "Microsoft.Jet.OLEDB.4.0"
GV_conn.ConnectionString = _
"Data Source= " & sTablePath & _
";Extended Properties=""Excel 8.0;HDR=No"";"
GV_conn.CursorLocation = adUseClient
GV_conn.Mode = adModeRead
GV_conn.Open
If GV_conn.State <> adStateOpen Then
Text1.Text = "Connexion to Excel table failed"
Exit Sub
End If
' Get the recordset
Set GV_rs = New ADODB.Recordset
GV_rs.ActiveConnection = GV_conn
GV_rs.CursorLocation = adUseClient
' adOpenStatic means that the table content will not change during the program
' even if some user is updating the table during the process
' adLockReadOnly = no lock - read only
SQL = "SELECT * FROM [sheet1$] "
GV_rs.Open SQL, GV_conn, adOpenStatic, adLockReadOnly, adCmdText
iLine = 1
While Not GV_rs.EOF
' Column A (numeric)
If IsNumeric(GV_rs.Fields(0).Value) Then
sValA = "num " & Str(GV_rs.Fields(0).Value)
Else
If IsNull(GV_rs.Fields(0).Value) Then
sValA = "null "
Else
sValA = "str " & GV_rs.Fields(0).Value
End If
End If
'Column B (alphanumeric)
If IsNumeric(GV_rs.Fields(1).Value) Then
sValB = "num " & Str(GV_rs.Fields(1).Value)
Else
If IsNull(GV_rs.Fields(1).Value) Then
sValB = "null "
Else
sValB = "str " & GV_rs.Fields(1).Value
End If
End If
' Edit a line
List1.AddItem " Line " & Str(iLine) & " Column A = " & sValA & " Column B = " & sValB
' next line
GV_rs.MoveNext
iLine = iLine + 1
Wend
' close recordset
GV_rs.Close
Set GV_rs = Nothing
' End process
RunButton.Enabled = False
GV_conn.Close
Set GV_conn = Nothing
Text1.Text = "end processing"
StopButton.SetFocus
End Sub
Query Data From Access By Writing A Code In Excel
Here is what I am doing - create a code in Excel to bring data from Access Database in the same folder. Two problems on the following code: 1) when I used double quote for DatePart , the quoted part (like "yyyy") is highlighted as compile error. 2) it also shows the problem in the line of " .Open, , 3, 3 "
I got this code from a friend and just make minor change and expect it to work propertly. It turned out i met these two problems. Please give me a hint how to fix it. Many thanks.
Code:
Sub Import2()
Dim cn As Object, rs As Object, myCallYear As String, myCallMonth As String
Dim MySql As String, dbfullname As String, myCnt As Long
dbfullname = "C:Documents and SettingsxyzDesktopFFR.mdb"
myCallYear = Sheets("Reference").Range("E13").Value 'Pass Year
myCallMonth = Sheets("Reference").Range("E14").Value 'Pass Month
MySql = "SELECT DatePart(''yyyy'',[TransactionTime]),DatePart('m',[TransactionTime]),[Category],[Subcategory],Sum([DollarSpend]),[TransactionTime]" & _
"FROM tblExpense GROUP BY DatePart('yyyy',[TransactionTime]),DatePart('m',[TransactionTime]),Category,Subcategory" & _
"Having DatePart("yyyy",[TransactionTime]) ='" & myCallYear & "'" & "AND DatePart("m",[TransactionTime]) ='" & myCallMonth & "'Order By DatePart("yyyy",[TransactionTime]), DatePart("m",[TransactionTime]);"
myCallYear = Empty
myCallMonth = Empty
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbfullname & ";" 'Create DB connection
Set rs = CreateObject("ADODB.Recordset")
With rs
Set .ActiveConnection = cn
.Source = MySql 'Pass your SQL
.Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
myCnt = .RecordCount
If myCnt > 0 Then
.MoveLast: .MoveFirst
'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
3 fields in the sql pass
Sheets("Data").Range(Cells(2, 14), Cells(myCnt + 1, 21)).CopyFromRecordset rs
End If
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub
Writing Data From Access To Excel - Run Time Error '1004'
I am trying to transfer data from Access database to an Excel workbook embedded in OLE box in my VB6 program. The program runs fine till it reaches a particular record in the Access database. For copying that particular record it gives "Run-time error '1004' Application defined or object defined error.
When I go to Debug mode and look for the highlighted line it always stops on that particular record. So I have deleted that record from the database and tried again. Now it gives error when it reaches the record after the deleted record.
In debug mode, if I choose the Continue option the program continues and finishes copying the data into excel.
Appended is the code
Code:
Option Explicit
Dim wkBook As Excel.Workbook
Dim wkSheet As Excel.Worksheet
Dim lRow, i As Integer
Dim rsLGR As ADODB.Recordset
Dim rsReject As ADODB.Recordset
Dim rsClass As ADODB.Recordset
Private Sub cmdDump_Click()
'copying data from Database to Excel Sheet
lRow = 4
Set rsLGR = New ADODB.Recordset
rsLGR.Open "SELECT * FROM EqpInfo WHERE WorkOrderNo = '" & rsInsp!WorkOrderNo & "' Order By ItemCode", _
db, adOpenStatic, adLockOptimistic
Set rsReject = New ADODB.Recordset
rsReject.Open "SELECT * FROM EqpTestInfo WHERE WorkOrderNo = '" & rsInsp!WorkOrderNo & _
"' Order By ItemCode", db, adOpenStatic, adLockOptimistic
Set rsClass = New ADODB.Recordset
rsClass.Open "SELECT * FROM MainItemInfo ", db, adOpenStatic, adLockOptimistic
i = 1
rsClass.MoveFirst
If rsLGR.RecordCount > 0 Then
While Not rsClass.EOF
rsLGR.MoveFirst
While Not rsLGR.EOF
If InStr(5, rsLGR("ItemCode"), rsClass("ItemCode"), vbTextCompare) Then
If Not IsNull(rsLGR!ItemCode) And Len(Trim(rsLGR!ItemCode)) > 0 Then
[color=Red]wkSheet.Cells(lRow, 1) = rsLGR!ItemCode[/color] End If
If Not IsNull(rsLGR!ItemDescription) And Len(Trim(rsLGR!ItemDescription)) > 0 Then
wkSheet.Cells(lRow, 2) = rsLGR!ItemDescription
End If
If Not IsNull(rsLGR!DetlDescription) And Len(Trim(rsLGR!DetlDescription)) > 0 Then
wkSheet.Cells(lRow, 3) = rsLGR!DetlDescription
End If
If Not IsNull(rsLGR!Degree) And Len(Trim(rsLGR!Degree)) > 0 Then
wkSheet.Cells(lRow, 4) = rsLGR!Degree
End If
If Not IsNull(rsLGR!SWL) And Len(Trim(rsLGR!SWL)) > 0 Then
wkSheet.Cells(lRow, 5) = rsLGR!SWL
End If
If Not IsNull(rsLGR!SerialNo) And Len(Trim(rsLGR!SerialNo)) > 0 Then
wkSheet.Cells(lRow, 6) = rsLGR!SerialNo
End If
If Not IsNull(rsLGR!Model) And Len(Trim(rsLGR!Model)) > 0 Then
wkSheet.Cells(lRow, 7) = rsLGR!Model
End If
If Not IsNull(rsLGR!PartNo) And Len(Trim(rsLGR!PartNo)) > 0 Then
wkSheet.Cells(lRow, 8) = rsLGR!PartNo
End If
If Not IsNull(rsLGR!PlantIDNo) And Len(Trim(rsLGR!PlantIDNo)) > 0 Then
wkSheet.Cells(lRow, 9) = rsLGR!PlantIDNo
End If
If Not IsNull(rsLGR!Location) And Len(Trim(rsLGR!Location)) > 0 Then
wkSheet.Cells(lRow, 10) = rsLGR!Location
End If
If Not IsNull(rsLGR!Manufacturer) And Len(Trim(rsLGR!Manufacturer)) > 0 Then
wkSheet.Cells(lRow, 11) = rsLGR!Manufacturer
End If
If Not IsNull(rsLGR!ManufDate) And Len(Trim(rsLGR!ManufDate)) > 0 Then
wkSheet.Cells(lRow, 12) = rsLGR!ManufDate
End If
If Not IsNull(rsLGR!ManufCertNo) And Len(Trim(rsLGR!ManufCertNo)) > 0 Then
wkSheet.Cells(lRow, 13) = rsLGR!ManufCertNo
End If
If Not IsNull(rsLGR!Status) And Len(Trim(rsLGR!Status)) > 0 Then
wkSheet.Cells(lRow, 14) = rsLGR!Status
End If
rsReject.Find ("ItemCode = '" & rsLGR!ItemCode & "'")
If Not rsReject.EOF Then
If Not IsNull(rsReject!ProofLoad1) And Len(Trim(rsReject!ProofLoad1)) > 0 Then
wkSheet.Cells(lRow, 15) = rsReject!ProofLoad1
End If
If Not IsNull(rsReject!LoadTestCertNo) And Len(Trim(rsReject!LoadTestCertNo)) > 0 Then
wkSheet.Cells(lRow, 16) = rsReject!LoadTestCertNo
End If
If Not IsNull(rsReject!Pass1) And Len(Trim(rsReject!Pass1)) > 0 Then
wkSheet.Cells(lRow, 17) = rsReject!Pass1
End If
If Not IsNull(rsReject!Defect) And Len(Trim(rsReject!Defect)) > 0 Then
wkSheet.Cells(lRow, 18) = rsReject!Defect
End If
If Not IsNull(rsReject!Comments) And Len(Trim(rsReject!Comments)) > 0 Then
wkSheet.Cells(lRow, 19) = rsReject!Comments
End If
Else
rsReject.MoveFirst
End If
i = i + 1
lRow = lRow + 1
End If
rsLGR.MoveNext
Wend
rsClass.MoveNext
Wend
MsgBox "All Items in the Lifting Gear Register are copied"
rsLGR.MoveFirst
Else
MsgBox "There are no items in the Lifting Gear Register"
End If
End Sub
Private Sub cmdSaveData_Click()
'saving data into Access datatbase
End Sub
Private Sub cmdSaveExcel_Click()
oleExcel.object.SaveAs ("C:WebLOLERLGR.xls")
End Sub
Private Sub oleExcel_GotFocus()
oleExcel.Enabled = True
oleExcel.SourceDoc = "C:WebLOLERLGR.xls"
oleExcel.Action = 0
Set wkSheet = oleExcel.object.Worksheets(1)
lRow = 1
wkSheet.Rows(lRow).Font.Bold = True
wkSheet.Rows(lRow).Font.Size = 20
wkSheet.Cells(lRow, 10).Font.Color = RGB(0, 0, 255)
wkSheet.Cells(lRow, 10) = "Lifting Gear Register"
lRow = lRow + 1
wkSheet.Rows(lRow).Font.Bold = True
wkSheet.Cells(lRow, 16).Font.Color = RGB(0, 0, 255)
wkSheet.Rows(lRow).Font.Size = 12
wkSheet.Cells(lRow, 16) = "If Load Tested"
wkSheet.Cells(lRow, 19).Font.Color = RGB(255, 0, 0)
wkSheet.Cells(lRow, 19) = "If Rejected"
lRow = lRow + 1
wkSheet.Rows(lRow).Font.Bold = True
wkSheet.Columns(1).ColumnWidth = 14
wkSheet.Cells(lRow, 1) = "Item Code"
wkSheet.Columns(2).ColumnWidth = 22
wkSheet.Cells(lRow, 2) = "Item Description"
wkSheet.Columns(3).ColumnWidth = 30
wkSheet.Cells(lRow, 3) = "Detailed Description"
wkSheet.Columns(4).ColumnWidth = 7
wkSheet.Cells(lRow, 4) = "Degree"
wkSheet.Columns(5).ColumnWidth = 12
wkSheet.Cells(lRow, 5) = "SWL"
wkSheet.Columns(6).ColumnWidth = 12
wkSheet.Cells(lRow, 6) = "Serial No."
'wkSheet.Columns(7).ColumnWidth = 14
wkSheet.Cells(lRow, 7) = "Model"
'wkSheet.Columns(8).ColumnWidth = 14
wkSheet.Cells(lRow, 8) = "Part No"
wkSheet.Columns(9).ColumnWidth = 12
wkSheet.Cells(lRow, 9) = "Plant ID No"
wkSheet.Columns(10).ColumnWidth = 20
wkSheet.Cells(lRow, 10) = "Location"
wkSheet.Columns(11).ColumnWidth = 15
wkSheet.Cells(lRow, 11) = "Manufacturer"
wkSheet.Columns(12).ColumnWidth = 18
wkSheet.Cells(lRow, 12) = "Date of Manufacture"
wkSheet.Columns(13).ColumnWidth = 20
wkSheet.Cells(lRow, 13) = "Test Certificate No"
wkSheet.Columns(14).ColumnWidth = 10
wkSheet.Cells(lRow, 14) = "Status"
wkSheet.Columns(15).ColumnWidth = 12
wkSheet.Cells(lRow, 15).Font.Color = RGB(0, 0, 255)
wkSheet.Cells(lRow, 15) = "Proof Load"
wkSheet.Columns(16).ColumnWidth = 25
wkSheet.Cells(lRow, 16).Font.Color = RGB(0, 0, 255)
wkSheet.Cells(lRow, 16) = "Load Test Certificate No"
wkSheet.Columns(17).ColumnWidth = 12
wkSheet.Cells(lRow, 17).Font.Color = RGB(0, 0, 255)
wkSheet.Cells(lRow, 17) = "Test Result"
wkSheet.Columns(18).ColumnWidth = 20
wkSheet.Cells(lRow, 18).Font.Color = RGB(255, 0, 0)
wkSheet.Cells(lRow, 18) = "Defect"
wkSheet.Columns(19).ColumnWidth = 25
wkSheet.Cells(lRow, 19).Font.Color = RGB(255, 0, 0)
wkSheet.Cells(lRow, 19) = "Inspectors Comments"
' Range("A1:C1").Select
' With Selection
' .HorizontalAlignment = xlGeneral
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = True
' End With
' With shtLGR.Shapes
'
' .AddLine(0,0,74,74)
End Sub
The Red line is giving the error.
Please help me solve the riddle.
Regards
Varrey
Writing Array Contents To An Excel Spreadsheet Or Access Table
Help,
I have a VBA procedure in MS Access that stores items in an array. I have tried every way possible to print the data to a spreadsheet or table.
Is this possible in VBA. Everything else is working except this part.
I have tried using
WRITE #1 Results(I,J)
I keep getting a compile error and cannot locate the proper coding for it.
Kathleen
Writing Array Contents To An Excel Spreadsheet Or Access Table
Hy folks;
Sorry to but in, (tell me to go away) but I have been trying to do a similar task but to no avail.
I have written a cash-till application that currently outputs data to textfile, i then have to manually copy and paste the text in to a speadsheet. I have tried many methods of getting VB to open and output to the spreadsheet but nothing seams to work.
I have read (understand the priciplas) this thread but when I copy and paste the code sited above I get runtime errors "User type Defined Not Defined" it relates the the very first line of code ;
Dim objExcel As Excel.Application
I am using VB6 and windows and Office XP, if there is an easy way just to output to a designated cell eg today = spreadsjeet "accounts 04" sheet = "cash-book" and cell = "d4" tomorrw will be cell ="d5" etc...
any help apreciated
|
TRACKER
