|
I have been having trouble with Excel myself so I don't know if I can be of any help to you. My suggestion would be to try recording a macro of what you want to do. Copy and paste the code it generates into your subs and then tweak it to suit your needs. That got my program "close" to working but finding answers for Excel seems to be very difficult.
Hope this helps, Garner
|
|
|
|
|
Thanks for the suggestion - I didn't think about recording a macro.
I was able to get a working solution that is adequate for my needs. I basically followed the example that I found in the Object Browser's help for "GetObject" (in VBA library)
It seems cumbersome to have to use the 2 API functions (to find a window handle to Excel and then "post" it to the Object table via a windows message), but in the end it works.
I will now need to control operation in Excel (worksheet) from within Access and VBA. Ultimately, I will use a chart in my Excel file as a template, changing the values according to latest data in Access.
Best regards,
Johnny
|
|
|
|
|
Johnny,
Since you seem to know how to use VB & Excel together, is there any chance that you would have time to look at my Jan. 6th Post, "VB/Excel interaction"? I have tried everything that I can think of and I still can't make it work. Nobody else has answered.
Thanks in advance, Garner
|
|
|
|
|
When tabbing in my datagrid after the third column I want to set focus back to the first column.
I thought about setting the tab stops to false after the third column or writing some kind of event handler? Any help would be appreciated.
|
|
|
|
|
I get the following error "Exception from HRESULT: 0x800A03EC." when I get to this stage of my procedure.
.Range("A" & i.ToString).Value = myRdr("LastName")
Any ideas? I have forwarded the code. Also how to I make sure I have closed EXCEL
Thanks in advance
Private Sub ExcelExtract(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Extract.Click
Dim sProcName As String
Dim sInfoMsg As String
Dim sCriteria As String
Dim i As Double = 3
Dim myRdr As OleDb.OleDbDataReader
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
sProcName = "ExcelExtract_Click"
'Makes Excel invisible to the user until spreadsheet is populated
excelApp.Visible = False
Try
'Populate the Floor Report DataSet used for the Excel spreadsheet
sCriteria = ""
sCriteria = "SELECT LastName, FirstName, Age FROM EmployerTable
Dim myCommand As New OleDb.OleDbCommand(sCriteria, sOleDbConnection)
myRdr = myCommand.ExecuteReader
Catch ex As Exception
sProcName = "ExcelExtract_Click - Populate Report Dataset"
Me.Cursor = System.Windows.Forms.Cursors.Default
ErrorMsg(mcNAME, sProcName, sInfoMsg)
End Try
With excelWorksheet
'Format the Excel cells
.Range("A1..C2").Font.Bold = True
.Range("A1..C2").ColumnWidth = 15
.Range("A1").Value = "REPORT as at " & Now
.Range("A2").Value = "Surname"
.Range("B2").Value = "Forename"
.Range("C2").Value = "Age"
Try
'Place the values into the spreadsheet
Do Until myRdr.Read = False
.Range("A" & i.ToString).Value = myRdr("LastName")
.Range("B" & i.ToString).Value = myRdr("FirstName")
.Range("C" & i.ToString).Value = myRdr("Age")
i = i + 1
Loop
Catch ex As Exception
End Try
End With
excelApp.Visible = True
End Sub
|
|
|
|
|
Your myRdr statements are returning the values of LastName, FirstName, and Age in their native database formats. Try it this way:
While myRdr.Read()
.Range( String.Format("A{0}", i) ).Value = myRdr.GetString(0) ' LastName
.Range( String.Format("B{0}", i) ).Value = myRdr.GetString(1) ' FirstName
.Range( String.Format("C{0}", i) ).Value = myRdr.GetInt32(2) ' Age
i+=1
End While
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
It is now working.
Thanks for the your help
|
|
|
|
|
I get the same error when I try the following
.Range(String.Format("A{0}", i)).Value = myRdr.GetDecimal(0)
|
|
|
|
|
Is the first column is your dataset a string or a number?
I'm willing to bet it's a string. Calling GetDecimal on a database string value will throw an exception.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
It is a number.
When I type in GetFieldType it say that it is a decimal. The way I have got round this problem was Convert.ToString. Doing this seems to work. I now have other fields that have 2 decimal points i.e. factor , 2.34 and have applied the Convert.ToString way.
|
|
|
|
|
I do not want the user to be able to sort the DataGrid by clicking on a column heading. I'm using the following piece of code yet it does not prevent sorting.
Me.DataGrid1.AllowSorting = False
What else must I do?
Thanks
-- modified at 11:33 Tuesday 10th January, 2006
Found answer
In the TableStyles you can set AllowSorting = False
|
|
|
|
|
What are you binding the DataGrid to? In some cases, you need to turn Sorting off on the bound source too.
Better yes, show us the code you're using to setup the datagrid and bind the datasource to it.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-- modified at 12:37 Tuesday 10th January, 2006
|
|
|
|
|
The datagrid is bound to a dataview. Turning off sorting at the datagrid does not work. Turning it off at the tablestyle does. Below is the requested sample code.
Me.DataGrid1.DataSource = DataTableOrdersDetail.DefaultView
'Me.DataGrid1.AllowSorting = False
Dim grdTableStyle1 As New DataGridTableStyle
grdTableStyle1.AllowSorting = False
With grdTableStyle1
.MappingName = "DataTableOrdersDetail"
.RowHeadersVisible = False
With .GridColumnStyles
.Add(New DataGridTextBoxColumn)
With .Item(0)
.MappingName = "QuantityOrdered"
.HeaderText = "Ordered"
.Width = 55
.NullText = ""
End With
-- modified at 8:35 Thursday 12th January, 2006
A question with the above code: If the datagrid datasource is connected to a dataview shouldn't column 0 mappingname use the dataview also? It does not work if I try it.
|
|
|
|
|
I am reading from an XML file and populating a Dataset object. To make sure that the dataset is being populated, I created dataviews since I have multiple tables created from the XML file in the dataset.
As I have to take care of the relationships between tables, I have to populate a table in the database from the results of the dataset/dataviews generated. What I tried to do is creating an insert stored procedure but am stuck on how to get the parameters values from the dataset/dataview. Any help would be appreciated!
objDataset.ReadXml(strFile)
Dim dvLayers As New DataView(objDataset.Tables("Layer"))
Dim dvDatasets As New DataView(objDataset.Tables("Dataset"))
Dim dvREnderer As New DataView(objDataset.Tables("SIMPLERENDERER"))
Dim dvLineRenderer As New DataView(objDataset.Tables("SIMPLELINESYMBOL"))
Dim dvPolyRenderer As New DataView(objDataset.Tables("SIMPLEPOLYGONSYMBOL"))
Dim dvMarkerRenderer As New DataView(objDataset.Tables("SIMPLEMARKERSYMBOL"))
|
|
|
|
|
These DataView's are useless since you're only putting the data from a single table into each one and you haven't changed what your viewing in each table. You're actually just returning the exact same thing each tables default view would return.
What does your INSERT SQL stuff look like? That's where the bulk of the work is going to be done.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I used dataviews and bound them to datagrid to check if the data was read correctly since I do not have the schema of the XML data. Actually below is my insert statement and is pointing to a stored procedure.
objDataset.ReadXml(strFile)
'create a dataview object for the books table in the dataset
Dim dvLayers As New DataView(objDataset.Tables("Layer"))
Dim dvDatasets As New DataView(objDataset.Tables("Dataset"))
Dim dvREnderer As New DataView(objDataset.Tables("SIMPLERENDERER"))
Dim dvLineRenderer As New DataView(objDataset.Tables "SIMPLELINESYMBOL"))
Dim dvPolyRenderer As New DataView(objDataset.Tables("SIMPLEPOLYGONSYMBOL"))
Dim dvMarkerRenderer As New DataView(objDataset.Tables("SIMPLEMARKERSYMBOL"))
Dim cmdInsertLyr As New SqlCommand("", ConDb.Connect)
cmdInsertLyr.CommandText = "dbo.[Insert_LyrView]"
cmdInsertLyr.CommandType = CommandType.StoredProcedure
cmdInsertLyr.Connection.Open()
cmdInsertLyr.Parameters.Add("@LyrID", "")
cmdInsertLyr.Parameters.Add("@LyrName", "")
cmdInsertLyr.Parameters.Add("@LyrType", "")
cmdInsertLyr.Parameters.Add("@Visible", "")
cmdInsertLyr.Parameters.Add("@ID", "")
cmdInsertLyr.Parameters.Add("@Map_id", "")
cmdInsertLyr.Parameters.Add("@dtName", "")
cmdInsertLyr.Parameters.Add("@dttype", "")
cmdInsertLyr.Parameters.Add("@workspace", "")
cmdInsertLyr.ExecuteNonQuery()
and following is the stored procedure am using.
CREATE PROCEDURE [Insert_LyrView]
(
@LyrID int,
@LyrName [nvarchar](250),
@LyrType [nvarchar](250),
@Visible [nvarchar](250),
@id int,
@map_id int,
@dtname [nvarchar](250),
@dttype [nvarchar](250),
@workspace [nvarchar](250)
)
AS INSERT INTO [LayerView]
(LayerID,
LyrName,
LyrType,
Visible,
id,
map_id,
dtname,
dttype,
workspace
)
VALUES
(@LyrID,
@LyrName,
@LyrType,
@Visible,
@id,
@map_id,
@dtname,
@dttype,
@workspace
)
GO
Am stuck in passing the values from the dataset to the parameters to the cmdinsertlyr!!!And I left the values of the parameters empty!!!
|
|
|
|
|
How many tables does this [LayerView] combine? IIRC, you can only make changes to one base table of a view at a time.
Pick a dataset table, say Layers. All you have to do is iterate through the records in the table, assign the values from each column in the table to the parameters in the SQLCommand and execute the command, one record at a time. If you have 1000 records in your Layers table, then you'll assign the values from each column to the parameters of your SQL command and execute it 1000 times.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
hi
i m facing problem when export data from access data file to sql server with the help of DTS.
can any body provide any solution for this.
its urgent
thanks in advance
|
|
|
|
|
You didn't ask any specific questions, so the best anyone can do if refer you to a couple of articles on the subject:
DTS Custom Task in VB.NET[^] on VBDotNetHeaven
Best Practices for Using DTS ...[^] on MSDN
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-- modified at 11:08 Tuesday 10th January, 2006
|
|
|
|
|
hi
i m getting following error
"QueryInterface for interface DTS.CustomTask failed"
and
my code is
Dim oPackage As New DTS.Package2
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.BulkInsertTask
Try
oConnection = oPackage.Connections.New("SQLOLEDB")
oStep = oPackage.Steps.New
'oTask = oPackage.Tasks.New("DTSBulkInsertTask")
oTask = oPackage.Tasks.New("DTSExecuteSQLTask")
'DTSExecuteSQLTask'
oCustomTask = oTask.CustomTask
With oConnection
oConnection.Catalog = "cdms"
oConnection.DataSource = "SBS-DEVELOPMENT\SQLCLASSIC"
oConnection.ID = 1
oConnection.UseTrustedConnection = True
oConnection.UserID = "cdms"
oConnection.Password = "cdms"
End With
oPackage.Connections.Add(oConnection)
oConnection = Nothing
With oStep
.Name = "GenericPkgStep"
.ExecuteInMainThread = True
End With
With oCustomTask
.Name = "GenericPkgTask"
.DataFile = "E:\PROJECT ASSIGNED\Projects\CDMS\CDMS_Phase_2\CDMS_Phase_2_Requirements\encounter05\encounter05.mdb"
.ConnectionID = 1
.DestinationTableName = "cdms..tblMemberEncounters"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With
oStep.TaskName = oCustomTask.Name
With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With
oPackage.Execute()
Catch ex As Exception
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try
|
|
|
|
|
Copy, paste, and pray, huh?
It's obvious that's what you did because there's far more to getting a package to work that just this code. That's 108 page document in the second link has entire section dedicated to building a custom DTS package and getting it to work. There's far more to it than just a little code.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi
I have an application where the front end is in ASP and the dll which supports it is written in C#.
I am using 'regasm' command to register it for com inter-operatibility.
C# dll methods are working fine for string type parameters, but not for LONG and INT type parameters. It throws an error saying:
__________________________________________________________________________
Microsoft VBScript runtime error '800a01c2'
Wrong number of arguments or invalid property assignment: 'GetReportsData'
__________________________________________________________________________
I am passing the LONG parameter from ASP. Example:
_________________________
Method in C#
public string takeint(long param)
{
//code
}
________
Call from ASP
var1=object.takeint(Clng("100"))
_________________________
This operation throws above mentioned error.
What are the compatible datatypes for LONG and INT of ASP in C# as I can't change the ASP call, but I can change the C# code?
Thanks.
--------------------------------
From: saurav goel
|
|
|
|
|
A long in ASP corresponds to an int (Int32) in C#. (There is no data type in ASP that corresponds to a long (Int64) in C#.)
---
b { font-weight: normal; }
-- modified at 7:20 Tuesday 10th January, 2006
|
|
|
|
|
C# questions belong in the C# forum, not VB.NET.
Posting the same question in multiple forums (cross posting) is also considered very rude.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
* How to store data in a public variable(global) and make it available throughout the application?
Mayur
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
