Datagrid Transfer To Excel VB6 Bug?????
Hi all,
I've got a program that transfers information from my datagrid to excel. It works PERFECTLY for a database of less than 40 rows. BUT more than 40 gives a 6148 runtime error!?!?!?! I'm been looking at this thing all day! HELP!
Where there's an arrow it shows what VB deems as my error.
'get total number of fields
Dim total_columns As Integer 'counter for total number of columns
Dim total_rows As Integer 'counter for total number of rows
'get total number of columns
total_columns = MY_AdoRecSet.Fields.Count
'get total number of rows
total_rows = MY_AdoRecSet.RecordCount
'Go through the X and Y axes to enter the datagrid information into the
'excel worksheet. The loop goes through the column then the next row.
Dim current_row As Integer
Dim current_column As Integer
For current_row = 1 To total_rows Step 1
----> MY_grid.Row = current_row - 1 'select the row in the grid
For current_column = 1 To total_columns Step 1
MY_grid.Col = current_column - 1 'select the column in the grid
If action = PRINTER Then
MY_xl_worksheet.Cells(current_row + Yoffset + 1, _
current_column + Xoffset).Value = MY_grid.Text
Else
MY_xl_worksheet.Cells(current_row + Yoffset, _
current_column + Xoffset).Value = MY_grid.Text
End If
Next
Next
If action = PRINTER Then
Call print_setup(MY_xl_worksheet, MY_combobox)
'Print out the Information
MY_xl_worksheet.PrintOut
End If
If action = FILE Then
If IsMissing(path) Then
MsgBox "path name not defined"
End
Else
Call saveXLS(MY_xl_worksheet, path)
End If
End If
printgrid = 0
Exit Function
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Transfer DataGrid To Excel[]
Hi,
I want to transfer Data from MySQL server to a excel file.
i used a datagrid to populate the whole data, now the problem is i can't write the whole data in the excel file...
using a MSHflexgrid prints only 2049 lines of data.
i have to write around 20000 lines of data in Excel Sheet.
_____________________________________________________________
Private Sub Command1_Click()
Call DataGrid_To_Excel(grdDataGrid, 300, 20)
'grdDataGrid.ApproxCount
End Sub
____________________________________________________________
Public Sub DataGrid_To_Excel(TheDataGrid As DataGrid, TheRows As Integer, TheCols As Integer, Optional GridStyle As Integer = 1, Optional WorkSheetName As String)
Dim objXL As New Excel.Application
Dim wbXL As New Excel.Workbook
Dim wsXL As New Excel.Worksheet
Dim intRow As Integer ' counter
Dim intCol As Integer ' counter
If Not IsObject(objXL) Then
MsgBox "You need Microsoft Excel to use this function", _
vbExclamation, "Print to Excel"
Exit Sub
End If
'On Error Resume Next is necessary because
'someone may pass more rows
'or columns than the datagrid has
'you can instead check for this,
'or rewrite the function so that
'it exports all non-fixed cells
'to Excel
On Error Resume Next
' open Excel
objXL.Visible = True
Set wbXL = objXL.Workbooks.Add
Set wsXL = objXL.ActiveSheet
' name the worksheet
With wsXL
If Not WorkSheetName = "" Then
.Name = WorkSheetName
End If
End With
' fill worksheet
For intRow = 0 To TheRows
TheDatagrid.Row = intRow
For intCol = 0 To TheCols
TheDatagrid.Col = intCol
wsXL.Cells(intRow, intCol).Value = TheDatagrid.Text
Next
Next
' format the look
For intCol = 1 To TheCols
wsXL.Columns(intCol).AutoFit
'wsXL.Columns(intCol).AutoFormat (1)
wsXL.Range("a1", Right(wsXL.Columns(TheCols).AddressLocal, _
1) & TheRows).AutoFormat GridStyle
Next
End Sub
_____________________________________________________________
using this code i can write 300 lines of data in excel. and the data gets repeated after row 208.
Any solution ?
How To Transfer Data In A Datagrid To Another Datagrid..?
Please help me in my project so that i can pass..
Im a freashman student in high school,
Im making a simple Computer Hardware Inventory system,
Datagrid1:
Item Description Serial Number Date Recorded
Mouse Optical SN-2007 03/27/2007
Datagrid2:
Item Description Serial Number Date Recorded
I want my data in datagrid1 will transfer to Datagrid2
please solve my problem..
youre help, is a big help to me!
Thank you in advance!! Godspeed!!
VB6 Datagrid Row Transfer
Hi everyone-a bit of help would be much appreciated. I need some code where I can select a row in my datagrid manually and send that whole row to another datagrid on a different form. Thanks for your help!
Transfer Rows From One Datagrid To Another
Hi,
I would like to know what I am missing. I have imported an excel sheet into a datagrid and have added checkboxes. I have also set the checkboxes to be true on default. I need to know how to be able to transfer those ticked rows to another datagrid. This is to be done by clicking on another button. It needs to look at how many and which rows have been left ticked and then transferred over to another datagrid.
The users will only be touching the checkboxes if they need to unselect them.
Here is my code:
Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Create an OpenFileDialog in order to browse for an Excel file.
Dim fileDialog As New OpenFileDialog
'Use a DialogResult to determine whether the user selected a file or cancelled.
Dim dialogResult As DialogResult
'Setup the OpenFileDialog
With fileDialog
.Title = "Select Excel File"
.Filter = "Excel (*.All files)|*.*"
.CheckFileExists = True
dialogResult = .ShowDialog
If dialogResult = dialogResult.OK Then
'Call the ShowExcelFile routine as we have a file.
ShowExcelFile(.FileName)
End If
End With
End Sub
Public Sub ShowExcelFile(ByVal excelFile As String)
'Using ADO.NET, populate a DataTable with the contents of the first sheet of an Excel file.
'more logic will be required if you need to display a certain work sheet.
Dim connection As OleDbConnection
Dim adapter As OleDbDataAdapter
Dim excelData As DataTable
Try
'Instantiate the connection object using a connection string suitable for Excel
connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & excelFile & ";" _
& "Extended Properties=Excel 8.0")
'Prepare the OleDbDataAdapter to select data from the first worksheet.
'NB: This assumes that the first worksheet is called Sheet1.
adapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection)
'Instantiate the excelData DataTable and populate it by using the Fill method of
'the OleDbDataAdapter.
Dim dc As DataColumn
Dim dr As DataRow
excelData = New DataTable("ExcelData")
Dim ds As DataSet
ds = New DataSet("ExcelData")
dc = excelData.Columns.Add("Yes", System.Type.GetType("System.Boolean"))
dc.DataType.GetType("System.Boolean")
excelData.Columns.Item("Yes").DefaultValue = True
dr = excelData.NewRow
ds.Tables.Add(excelData)
DataGrid1.DataSource = ds
adapter.Fill(excelData)
'Clear any bindings from the DataGrid and set the DataSource of the DataGrid
'to the new excelData DataTable
DataGrid1.DataBindings.Clear()
DataGrid1.DataSource = excelData
Catch ex As OleDbException
MessageBox.Show(ex.Message)
Catch ex As InvalidOperationException
MessageBox.Show(Ex.Message)
Catch ex As Exception
MessageBox.Show(Ex.Message)
Finally
'Tidy up used objects
If Not connection Is Nothing Then connection.Dispose()
If Not adapter Is Nothing Then adapter.Dispose()
If Not excelData Is Nothing Then excelData.Dispose()
End Try
End Sub
'This is to get the checkbox to react to the first click
Private myCheckBoxCol As Integer = 0 'my checkbox column
Private Sub DataGrid1_MouseUp(ByVal sender As Object, ByVal e As MouseEventArgs) Handles DataGrid1.MouseUp
Dim hti As DataGrid.HitTestInfo = Me.DataGrid1.HitTest(e.X, e.Y)
Try
If hti.Type = DataGrid.HitTestType.Cell AndAlso hti.Column = myCheckBoxCol Then
Me.DataGrid1(hti.Row, hti.Column) = Not CBool(Me.DataGrid1(hti.Row, hti.Column))
End If
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
' I need something to go here to retrieve the ticked rows.
End Sub
End Class
Can somebody please help? I'm new to VB and i'm using Visual Studio 2003.
Thanks,
rosmiq
How To Transfer Datagrid Contents To Textboxes?
What I like to do is to make the datagrid cells clickable. When I double-click a row for example, a form would appear where all the data in that row would appear. For ex:
Item No Item Item Desc.
876 [Strap] Ok
When I double-click on the cell where strap is enclosed, a form would appear that looks like this:
Item No [876]
Item [Strap]
Item Desc. [Ok]
The form would then allow me to edit the data (I know how to do the updates, I just don't know how to make the data on the datagrid appear on the textboxes). How do I do that? Thanks.
Help Transfer Listview To Excel
Hi I have a problem that I'm not able to solve, I have a listview with data of 7 decimal position (double) when I have to transfer from listview to Excel it cuts the comma and consider the listview text a string. I have tried with the NumberFormat but it doesn't work. What can I do???
wsXL.Range(wsXL.Cells(3, 2), wsXL.Cells(ListView2.ListItems.count, ListView2.ColumnHeaders.count - 2)).NumberFormat = "###0.00000"
If ListView2.ListItems.count > 0 Then
' fill worksheet
For lRow = 1 To ListView2.ListItems.count
For lCol = 1 To ListView2.ColumnHeaders.count
If lCol = 1 Then
wsXL.Cells(lRow, lCol).Value = ListView2.ListItems(lRow).Text
Else
wsXL.Cells(lRow, lCol).Value = ListView2.ListItems(lRow).ListSubItems(lCol - 1).Text
End If
Next lCol
Next lRow
This is what it shows in excel:
Codice Territorio Territorio AS001 AS001
1996 1997
019084 Agrigento 62.505.384.063.173 9.294.184.179
001006 Alessandria 227.646.609.202.229 1.923.535.974
011042 Ancona 248.526.651.758.278 2.190.416.626
whilst in listview the data are:
019084 Agrigento 625, 05384063173 929,4184179
001006 Alessandria 2276,46609202229 1923,535974
Transfer To Excel Or Text Box???
I have set up a search using my vb application. It displays days into a listbox. I need that data to be transfered into either excel???? or a textbox???? i can not get this to work, can someone throw me some code to work with??? thanks!
Transfer Data From VB To Excel
Hello,
I'm trying to transfer some data from a VB form to an existing excel spreadsheet with text boxes, check boxes and option buttons on it. On the VB form, I have 5 text boxes and when I click on a button I want the information on these text boxes to be loaded on the spreadsheet's text boxes and then print the spreadsheet as a report.
I know how to open the excel application from vb then load my existing spreadsheet in memory but then I can't find a way to fill the text boxes of my spreadsheet.
Would somebody have a suggestion on this problem?
Chris.
Slow Transfer To Excel
Hi there,
I have an application that uses excel object to fill an excel worksheet from VB 6. It works ok but it seems rather slow.
I loop in a collection of arrays and fill every row/column.
Code:
If Not moExcel Is Nothing Then
Set oWorkBook = moExcel.Workbooks.Add
Set oWorkSheet = oWorkBook.Worksheets(1)
iRow = 0
With oWorkSheet
For Each vntArray In pkArrays
iRow = iRow + 1
For iCol = 0 To UBound(vntArray)
.Cells(iRow, iCol + 1).Value = vntArray(iCol)
Next
Next
.Columns.AutoFit
End With
End If
Is there any smarter way of doing this?
Transfer Of Data From Vb/vb.net To Excel
Transfer of data from VB to excel
Does anyone has sample codes on how to:
1)transfer the inputs from a user form to an excel database(.xls) whenever a cmdAdd button is clicked on the form
2)The form will reappear again and waits for another sets of input whereby the add button will update again on the excel database...thanks!
Transfer Access - Excel
Hi,
I'm using Access 97.
I'm exporting an Access table to an excel file with the following instruction:
DoCmd.TransferDatabase acLink, "Microsoft Access", sBDExpo, acTable, "pddos", "vin_pddos", False
It generates correctly the excel file.
When I want to import the excel file into an Access table with the instruction:
DoCmd.TransferSpreadsheet acImport, 0, "rpl_pddos", "rpl_pddos.xls", True
it inserts a lot of null records before the real data.
Why is it doing it? How can I make sure it doesn´t happen?
Please, heelp!!
Amota
Automate Transfer From Access To Excel Using VB
hi guys,
I'm trying to transfer data from access query to excel. I know I can do this with access macro, which is cool, cos the query is run but not opened.
However, I need to the data to be copied to specific cells in excel. Is there a way to use VB to automate this procedure. or is there any other way to automate it?
Dim oExcel As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set oExcel = New Excel.Application
oExcel.Visible = True
Set oWorkbook = oExcel.Workbooks.Add
Set oSheet = oWorkbook.Worksheets(1)
oSheet.Range("A4").Value = "12/11/2005 13:00:00"
oSheet.Range("B4").Value = "1"
oSheet.Range("C4").Value = "3"
oSheet.Range("D4").Value = "0.02"
oWorkbook.SaveAs ("C:Documents and Settingsook1.xls")
Set oSheet = Nothing
Set oWorkbook = Nothing
oExcel.Quit
Set oExcel = Nothing
but this doesn't append to the worksheet, which is NOT what I want. Instead, it kinda delete the old data and put in the new one. How should I correct this (i.e. make this code do appending and not delete old data)?? Also, I DO NOT want it to create new excel wrkbook all the time. I just want it to open an existing one and chunk in the data in to the specific cells. pls help
THX HEAPS!!
Data Transfer From A List Box To Excel
Hi. I was wondering if anyone could help me with something which is probably quite simple but I can't seem to find on MSDN.
I am basically wanting to transfer data from a VB list box to a column in an Excel worksheet.
Thanks for any help you can give.
Date Transfer To Excel Sheet
Hi All!
I have a particularly perplexing problem trying to get a date from a textbox (within a userform) to transfer to an excel sheet in the same format.
Textbox1 is initialised and shows the date formatted as "dd/mm/yyyy".
What I need to happen is this date format transferred to a cell iin the same format.
What I continue to get is the cell showing the date as "mm/dd/yyyy"
I have tried forcing the cell formatting. This does not work. I have tried to do it programmatically, as below:
Code:
Dim InputDate As Double
InputDate = cdate(Textbox1.Value)
Cells(r,1) = Format(InputDate, "dd/mm/yy")
These have not yeilded the result I am looking for.
Please help. Will greatly appreciate any and all help.
P.S. I have also searched the forum for this topic, and it seems that there are not any useable solutions available.
EXCEL Data Transfer Within A Network
EXCEL is running on both of two PC's within a network. Workbook ("Data") on PC1 and Workbook("DataLog") on PC2 are opened.
With Code in Worksheets ("Data"):
Range(Sheets(1).Cells(8, 4), Sheets(1).Cells(28, 12)).SpecialCells(xlCellTypeVisible).Copy _
Workbooks("DataLog").Sheets(2).Cells(1,1) .... I can easily copy data from one Workbook to another on the s a m e PC.
How do I have to modify the code to manage the same thing, but from one PC to the other?
??
Range(Sheets(1).Cells(8, 4), Sheets(1).Cells(28, 12)).SpecialCells(xlCellTypeVisible).Copy _
\Hnpc2C:ArchivDataLog ......Sheets(2).Cells(1, 1)
??
MS Flexgrid To Excel Date Transfer
I am using a code to transfer the data from MSFlesgrid to Excel. It is transferring the data well But in case of a column which contains date is creating problem.
The date field is saved to database as:
rs1.Fields(8) = Format(Text12.Text, "dd/mm/yyyy")
In the Dbase, the date field is taken Date/Time, Format Short date dd/mm/yyyy
Now when this column in transferred to excel it is showing the date as dd/mm/yyyy format but actually it is taking the same as mm/dd/yyyy format for eg., for calculation purpose or if I change the date format in excel as "dd-mmm-yyyy"
For eg., if the date is 07/10/2007 ie 07th Oct., 2007 in Excel it is coming as 07/10/2007 but excel is recognizing it as 10th July 2007.
My regional settings etc are in dd/mm/yyyy format.
Why this problem is coming and how to sort it out?
How To Transfer Data From Excel To Mysql?
Hi all. I got a huge list of data in excel(7000 records) . I want to transfer it to mysql database. Could any one show me how to do it using a fast and easy method.Thanks
Transfer Of Data From Other Forms To Excel...
how come i cannot transfer the values from other (previous forms) to excel spreadsheet..but i can transfer the values from current form to it....
appXL.ActiveSheet.Cells(intClickCount, 4).Value = txtDate.Text
where txtDate.Text is the values from the previous forms...
(error: object require)
however the input values from the Current form eg:
appXL.ActiveSheet.Cells(intClickCount, 9).Value = txtUnitCost.Text
where txtUnitCost.Text is the value from the current forms can be display..?
Transfer MDB Records To Excel Sheets
Hi! Was wondering if anyone has a code so that the fields of an Access database could be directly transferred to a new Excel worksheet. (With the current knowledge I have, I just make a link to the MDB and then copy the entire table, open a new excel sheet and then paste everything? {so downright manual!)---- could this be simplified/automated by a code). Thank you for your time.
DATA TRANSFER FROM SQL SERVER TO EXCEL
Hi!
I have a table on SQL SERVER, and it has been connected to my vb application already.I display my data on DataGrid to users.Users can see data on datagrid using query.I want to export these data to EXCEL.How can I accomplish this job?
Thanks in advice.
Speed Up Transfer Of Data To Excel
How can I speed up the transfer of data from a text file to Excel? Here is the code I have so far but it is extremly slow. Is it because I am adding record by record? I would use ADO to do this but the text file has well over 150,000 lines. Any help would be appreciated. Thanks.
Set objExcel = New Excel.Application
Set objWorkbook = objExcel.Workbooks.Add
ctr = 2
x = 1
Call AddExcelHeader
For i = 0 To UBound(sFileNames)
Open FilePath & sFileNames(i) For Input As #1
Do While Not EOF(1)
Line Input #1, InputData
ctr = ctr + 1
If ctr Mod 133 = 0 Then
lblStatus.Caption = CStr(ctr) & " records exported!"
DoEvents
End If
If ctr Mod 65535 = 0 Then
' Formats the Excel spreadsheet data
objWorkbook.Worksheets(x).Range("A:E").Columns.AutoFit
objWorkbook.Worksheets(x).Range("A1:E1").Font.FontStyle = "Bold"
objWorkbook.Worksheets(x).Range("A1:E1").Rows.HorizontalAlignment = xlCenter
x = x + 1
ctr = 2
End If
Call AddExcelHeader
objWorkbook.Worksheets(x).Cells(ctr, 1) = Trim$(Mid$(InputData, 1, 10))
objWorkbook.Worksheets(x).Cells(ctr, 2) = Trim$(Mid$(InputData, 12, 10))
objWorkbook.Worksheets(x).Cells(ctr, 3) = "'" & Trim$(Mid$(InputData, 23, 5))
objWorkbook.Worksheets(x).Cells(ctr, 4) = Trim$(Mid$(InputData, 29, 8))
objWorkbook.Worksheets(x).Cells(ctr, 5) = Trim$(Mid$(InputData, 38, 8))
Loop
Close #1
Next
Swi
Transfer ADO Recordsets To Excel Or Access
I already have a VBA macro developed that develops a SQL statement based on variable criteria and queries an Oracle database and returns an ADO recordset. I then paste the data into an open Execl workbook using the CopyFromRecordset command. This works great. However, idealy I would like to export the recordset to a CLOSED workbook that resides on a network server. Is there a way to open two connections (one to the Oracle server and another to the closed Excel workbook) and simply transfer the recordset from one to the other. That would make life wonderful.
Another thing that I would like to be able to do is transfer the same recordset to a new Access table.
Oh, by the way. Criteria for the query changes. They will return different fields, depending on the need.
Thanks for any help.
Outlook To Excel Data Transfer
Hi,
I have developed a number of custom forms in Outlook and found that I can see the data (custom fields) that I need when I save the Outlook Template as a text file. The next trick is to automate the save as text process (using the subject line as the filename) and then open the text file and transpose the list into a table in Excel.
As a newbie to the programming world I was hoping for some help from one of you good folk from VBCity.
Look forward to your reply,
Thanks
Data Transfer Between MS Access And Excel
Hi all!
I wrote small article on the subject, think that may be of use for somebody:
http://www.zmey.1977.ru/Access_To_Excel.htm
I also gladly will discuss it here...
Best regards,
Zmey2
Data Transfer Into Pocket Excel
I have a small program in EVB that sends a request to a device, which in turn sends back a simple string of data. I am wondering if anyone can give me ANY advice on how I might be able to import the data into Microsoft Pocket Excel. Once again, ANY suggestions or ideas would be greatly appreciated!
Thank you!
Transfer Excel File To Access MDB (DAO)
Hey Gurus!
I need help again on how to transfer Excel file to Access MDB (DAO).
I'm having error like this one: The Microsoft Jet database engine couldn't find <TableName>. Make sure the object exist & that you spell the its name and path name correctly!
Here's my code:
Private Sub ReadInputFile()
Dim strTblName As String
Dim strWorkSheet As String
Dim strAccessFile As String
Dim strExcelFile As String
strAccessFile = gVars.sNewDBPathAndName
strExcelFile = gVars.InputFile
strTblName = gVars.strTableName
If strTblName = "" Then
MsgBox ("Please enter a table name!")
Else
Screen.MousePointer = vbHourglass
strWorkSheet = strTblName
Set dbDatabase = OpenDatabase(strExcelFile, True, False, "Excel 8.0")
dbDatabase.Execute "SELECT * into [;database=" & strAccessFile & "]." _
& strTblName & " FROM [" & strWorkSheet & "]"
Screen.MousePointer = vbDefault
MsgBox ("Transfer completed!!")
End If
Exit Sub
Need help ASAP!
I've attached my program.You can download it if you want to!
Thanx!
glenford@secomp-intl.com
Transfer Excel File To Access MDB (DAO)
Hey Gurus!
I need help again on how to transfer Excel file to Access MDB (DAO).
I'm having error like this one: The Microsoft Jet database engine couldn't find <TableName>. Make sure the object exist & that you spell the its name and path name correctly!
Here's my code:
Private Sub ReadInputFile()
Dim strTblName As String
Dim strWorkSheet As String
Dim strAccessFile As String
Dim strExcelFile As String
strAccessFile = gVars.sNewDBPathAndName
strExcelFile = gVars.InputFile
strTblName = gVars.strTableName
If strTblName = "" Then
MsgBox ("Please enter a table name!")
Else
Screen.MousePointer = vbHourglass
strWorkSheet = strTblName
Set dbDatabase = OpenDatabase(strExcelFile, True, False, "Excel 8.0")
dbDatabase.Execute "SELECT * into [;database=" & strAccessFile & "]." _
& strTblName & " FROM [" & strWorkSheet & "]"
Screen.MousePointer = vbDefault
MsgBox ("Transfer completed!!")
End If
Exit Sub
Need help ASAP!
I've attached my program.You can download it if you want to!
Thanx!
glenford@secomp-intl.com
Transfer Access Table To Excel
Please help. I have an Access database table that I create an Excel spreadsheet using TransferSpreadsheet. This works fine, except that the hyperlinks are no longer links. In Access I can export to excel and check the SaveFormatted and the created spreadsheet has the links. How can I do this from VB?
thanks,
CSmith
Transfer Word Formfield Data To Excel
This sounds as though it would be a simple task. I am referencing a text form field (as a bookmark named ID) in a "protected" word template and would just like to get the value out of the form field and place it in an existing Excel worksheet. I have a command button that will take the desired info from the textfields and place them as an excel record. There is no .value or anything that I can find to work. The other problem is that if I try and use .Text or some other ending, an error appears telling me that it can't get the information from a "protected" document. The template must be protected however so that the employees don't go screwing it all up. The bit of code I'm troubled with is as follows:
With xlWB.Worksheets(1)
Do
If IsEmpty(.Cells(i, 1)) Then
Chkcell = True
'go to desired bookmark
ID = Me.Bookmarks("ID").Range.??????????? (why no .value?)
'ID = Me.FormFields("ID").Range (same problem)
Else: i = i + 1
End If
Loop Until Chkcell = True
End With
Thanks for any help you guys can give me.
Problem:transfer Access Data To Excel
Private Sub Command1_Click()
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Set oXLApp = New Excel.Application
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
oXLSheet.UsedRange.Clear
oXLSheet.Range("A1").CopyFromRecordset Data1.Recordset
oXLSheet.Paste
oXLApp.Visible = True
Set oXLSheet = Nothing
Set oXLBook = Nothing
Set oXLApp = Nothing
End Sub
when i debug this code, there is no error but at the same time there is no data in excel worksheet. i just want to transfer access datas to excel. i cannot understand why i can't see access datas in excel.
thanks...
Transfer Data From Table In ACCESS TO EXCEL
Hi Everybody
Would really apreciate if you could find the bug in my code for transferring data in a table in access to excel sheet.
Code:
Sub ExportData()
Dim objAccess As Access.Application
Dim strname As String
strname = "mysheet"
Set objAccess = New Access.Application
With objAccess
.OpenCurrentDatabase "H: est.mdb"
.docmd.transferspreadsheet acExport, acspreadsheettypeexcel9, _
strname, "H:Chap15.xls", -1, "import!A1:C4"
.CloseCurrentDatabase
.Quit
End With
Set objAccess = Nothing
End Sub
On executing this code i am getting the error
"Run Time error 3011
The microsoft jet database engine could not find the object".Make sure the object exists and path name is correct."
I have double checked the names and path name and the same code works fin when i import data to access from excel with just the acExport changed to acImport.
Please help me out.Thanks.
kvs
Crystal Report 7.0 Data Transfer To Excel
hello
im trying to export crystal report 7.0 data to EXCEL but its not transfering properly in same format that report is showing...data in the column is getting shifted to another column...why its happeing...i tried adding TABs also in the crystal report...i hv vertical lines in the report..
is there any solutions..
Problem:transfer Access Data To Excel
Private Sub Command1_Click()
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Set oXLApp = New Excel.Application
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
oXLSheet.UsedRange.Clear
oXLSheet.Range("A1").CopyFromRecordset Data1.Recordset
oXLSheet.Paste
oXLApp.Visible = True
Set oXLSheet = Nothing
Set oXLBook = Nothing
Set oXLApp = Nothing
End Sub
when i debug this code, there is no error but at the same time there is no data in excel worksheet. i just want to transfer access datas to excel. i cannot understand why i can't see access datas in excel.
thanks...
Transfer VB Userform Info To Excel File
Using VBA in Excel, I am trying to take information from a user form created in VBA and tranfer it to certain Excel cells. What is the code to complete this?
*RESOLVED*simple Data Transfer From Excel To VB
Hi,
I am trying to copy one specific cell of data from an excel file into VB. I have found some code previously posted and have tried running it to see how it works. I am receiving the following error message "User Defined Type Not Defined" with regards to "Dim xlApp As Excel.Application", "Dim xlApp As Excel.Workbook" and "Dim xlApp As Excel.Worksheet".
Can someone please help me with this?
Thanks,
burzrk
CODE (previously posted):
------------------------------------------------------------------------
Dim iRow As Integer
Dim iCol As Integer
Dim iLastRow As Integer
Dim iLastCol As Integer
Dim sCellAdd As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
' set reference to Application object
Set xlApp = CreateObject("Excel.Application")
' set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("C:FileName.xls")
' set the reference to Worksheet object
Set xlSheet = xlBook.ActiveSheet
With xlSheet
iLastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
iLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
End With
For iRow = 1 To iLastRow
For iCol = 1 To iLastCol
Debug.Print xlApp.Cells(iRow, iCol)
Next iCol
Next iRow
Edited by - burzrk on 7/11/2005 7:33:09 PM
Probelm Transfer Data To Excel Format
I got a problem to download data from SQL to excel, after 3 minutes ago Windows message-Component Request Pending. popup said that-- The acttion cannot be completed because of the other application is busy . choose "Switch to" to activeate the busy ......
Can everyone help me on this problem. my code ample like this
Private Sub CmdSave_Click()
On Error Resume Next
Dim xl As Excel.Application
Dim fc As Integer
Dim m As String
Dim n As Integer
Dim rsv As String * 60
Dim c As Integer
Dim dgc As Long
Dim dgr As Long
Dim txt As String * 60
Dim wb As Workbook
m = MsgBox("Are you sure you want to save the records into excel format?", vbOKCancel, "Save to File")
If m = vbOK Then
fc = rs1.Fields.Count
Set xl = New Excel.Application
Set wb = xl.Workbooks.Add
xl.DisplayAlerts = False
CmdSave.Enabled = False
Cmd_run.Enabled = False
xl.Visible = False
wb.SaveAs ("C:windowsdesktop123.xls"), xlNormal, "", "", False, False
wb.Worksheets("sheet2").Delete
wb.Worksheets("sheet3").Delete
--Fields Name
For i = 1 To fc
txt = rs1(i - 1).Name
xl.Cells(1, i).Value = txt
Next
ProgressBar1.Visible = True
Do While Not rs1.EOF
n = n + 1
c = rs1.AbsolutePosition / rs1.RecordCount * 100 ''
Label5.Caption = c & "%"
Label5.FontSize = 6
ProgressBar1.Value = c
Label6.Caption = "Progressing.......!"
Label6.FontSize = 6
Label6.FontItalic = True
Me.Refresh
If c = 100 Then
Label6.Caption = "Completed!"
End If
For i = 1 To fc
rsv = rs1(i - 1).Value
xl.Cells(n + 1, i).Value = rsv
Next
rs1.MoveNext
Loop
CmdSave.Enabled = True
Cmd_run.Enabled = True
MsgBox "Progress completed!", vbInformation, "Transfer File"
Label6.Caption = ""
Label5.Caption = ""
ProgressBar1.Visible = False
xl.Visible = True
End If
End Sub
How To Transfer Data From Access To An Excel File
Hi All,
This is my first post at this forum (friend recommended it).
I have recently started writing code for Access and Excel. My current project involves running a small database to store data and using an Excel spreadsheet to "Report" the data. Previously we used the spreadsheet alone saving multiple copies with each record in it. We want to keep using the spreadsheet as it has some complicated charts that I don't want to try and reproduce in Access.
I searched on the internet and found out how to use the ShellExecute API to open the correct file but now I need to transfer the data from the database to specific cells in the spreadsheet. What is the best way to do this?
Originally I tried to use variables and have the excel file reference the variables on opening but the spreadsheet could not "see"them. So now I wanted to write some code in the Access module that would enter the data into the cells in the Excel spreadsheet, as that way I can update the data in the spreadsheet if the user moves to a different record in the DB.
Thanks heaps
PS also is there a way to close the excel file from Access using VB, ie when the DB is closed?
Dynamic Transfer Of Datas From Excel To Access
Friends,
Pls assist me. I am in fix........
I have some datas say around 50000 rows in an Excel Sheet. I want this records to be dynamically transferd at a click of a button in VB from Excel to Access....
Pls help....
Thanks in advance.
|
TRACKER
