Getting Data From EXCEL, Efficiently
Hello all!
I have experienced a huge time delay in looping through cells in a worksheet to compare, manipulate, get... data.
So I am wondering what is the most efficient way to retrieve data from EXCEL to manipulate, store, compare, etc... without having to loop through the text?
Would it be to turn the spreadsheet into a recordset to compare to another recordset.
Essentially, that is what I am doing. I am trying to compare data from EXCEL to a recordset I retrieve from ACCESS.
Thanks for helping VB City!
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Efficiently Loading Stock Price Data Into Excel
Hi everyone!
I refer to Peter's article at http://www.eggheadcafe.com/articles/20020108.asp
(quote: "The neat thing about this is that by simply changing the SQL Statement and supplying the name of ANY text file that resides in that folder, you can use the filesystem object (for example) to get a list of the files in the folder, and then read each file into a separate recordset object by simply looping through your array of stock price filenames and dynamically changing the SELECT statement to match on each iteration.")
and I'd like to ask the following:
I am looking for an efficient way to load stock price data into Excel from text files (or any other database stored locally , i.e. I'm not talking bout web queries) for testing purposes.
To be able to measure portfolio risk correctly, the data has to be loaded on a daily basis(*), one by one.
Does anyone have a sample file (e.g. for Excel VBA) which allows to learn the basics?
Would SQL, ADO or an Access database be the most appropriate solution for this?
(*) As I want to avoid recalculation of the same values (indicators, stops, risk, etc.) over and over again, I consider keeping all the calculated data in a database and calculate only one row (the newly added) in Excel.
I hope I explained everything clear enough.
I also post an excellent article on "VBA: Efficiency and Performance". - Enjoy!
Thanks for help and time.
Any help is appreciated.
Regards,
Thomas <tompf@gmx.at>
Scanning Through Excel More Efficiently
Hey guys,
I'm looking to see if there is a better way to scan through an excel sheet. Right now what I've been doing is:
Code:
For i = 1 To Worksheets(1).UsedRange.Columns.count
For j = 1 To Worksheets(1).UsedRange.Rows.count
If Worksheets(1).UsedRange.Cells(j, i).Text = "mytext" Then
count = count + 1
End If
Next j
Next i
This has proved pretty efficient for me, but there seems to always be someone who has a better way. Any takers?
Many Checkboxes To Control What Data VBA Outputs- How To Code Efficiently?
I have the following code to create headings on a worksheet based on what the user has selected. What's worse is that there is identical code that actually outputs data on the sheet. Can anyone suggest a more efficient way to do something like this?
If I could cycle through an array of checkbox values perhaps this would speed things up but even then, each if statement would be a bit different. Maybe there is someway to use bitwise AND and ORs in the if statements??
Any ideas would be appreciated- I'm guessing there are many better ways to structure this.
Thanks,
Dave
Code:
If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Angular Speed LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Angular Speed LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Angular Speed LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("U Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("U Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("U Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("U Angle LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("D Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("D Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("D Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("D Angle LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("R Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("R Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("R Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("R Angle LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("L Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("L Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("L Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("L Angle LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Running Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Running Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Running Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Running Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Inrush Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Inrush Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Inrush Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Inrush Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Stall Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Stall Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Stall Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Stall Current LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("Vertical Overrun Drift Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("Vertical Overrun Drift Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("Vertical Overrun Drift USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("Vertical Overrun Drift LSL")
If (FormMain.ckValue) Then setCellValueAndMoveRight ("Horizontal Overrun Drift Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("Horizontal Overrun Drift Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("Horizontal Overrun Drift USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("Horizontal Overrun Drift LSL")
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.
Thank you.
How To Do This Efficiently?
In my interface I have just finished my 2d version. I am now implementing the 3d version.(same thing but in 3 dimensions) I will basically have to use almost the same forms as I did in my 2d application but with a little modifications. So what I did is I just created new forms and copied the controls over(the text boxes and everything) and I am using new global variables to hold the variables to prevent confusion. Now I have almost 30 forms (and global variables for each form). I am beginning to get the feeling that maybe I am doing this inefficiently.. Could anyone please let me know if there is an efficient way to do this.
How Can I Efficiently Search An Array?
Hi,
I have a program which inserts numbers into an array. It first searches the array to determine if that number is already there. If the number isn't there, it will insert that number into the appropriate position in the array. The array doesn't need to be sorted at any point, because the number is inserted in numerical order, i.e. if the numbers in the array are 2,3,6,9 and the number to be inserted is 7, it will insert it between 6 and 9.
Once the array reaches a significant size - over 1000 entries say - the array searching algorithm becomes very inefficient. It searches the entire array until it finds the number, at which point it stops. Its a brute force approach and I'm sure there must be a more efficient way to do it. I suspect that an efficient algorithm would follow similar logic to a QuickSort routine (which is the fastest sorting algorithm).
Can anyone help?
Cheers.
Writing Efficiently From VBA To A Worksheet
Hi.
I’m trying to make a write operation to a worksheet as quick a possible.
I’ve made an array (one dimensional string, but I can use a Variant if necessary), and I want this to be written to a given row, staring in column A. One way to do it is too loop through the array using
Sheet(“sheet 1”).cells(row, i) = array(i)
in the loop. But as far as I know this is not the most efficient way of doing it.
I have also tried using
Sheets(“sheet 1”).range(<proper dimension>) = array
This works, but some of my array elements contain formulas, and these end up being written in the cell rather than the result of the formula (if array(0) contains (“=Sum(A1:A2)” then that text will appear in the cell rather then the sum of A1 and A2).
Anyone?
Thanks for your time
-Terry
Efficiently Writing To SQL Table Using VB6
Below is a sub that I am using to write data received from a modbus connection to a PLC. I am using the data to keep a history file in a SQL database. To be of any use the data must be gathered very frequently and written in to database very often. Is there a more efficient way of writing this data to SQL than the method below. It looks like my loop times are around 11 to 12 seconds and need to be 1 to 2.
Private Sub WriteDigitalDataToSQL()
Dim ConnectionOne As ADODB.Connection
Dim rstHistory As ADODB.Recordset
Dim ConnectionString As String
Dim SQL As String
Dim i As Integer
Dim DataType As String
Dim ServerName As String
Dim Database As String
Dim Count As Integer
ServerName = "10.1.1.2"
Database = "WIG"
Set ConnectionOne = New ADODB.Connection
ConnectionString = "driver={SQL Server};server=" & ServerName & ";database=" & Database & ";UID=sa;PWD="
ConnectionOne.Open ConnectionString
Set rstHistory = New ADODB.Recordset
rstHistory.CursorType = adOpenKeyset
rstHistory.LockType = adLockOptimistic
i = 0
Count = 0
If Mod1.Result = 0 Then
Do While i < 480
DataType = "Digitals"
If chkVbDigitalTable(i).Value = 1 Then
SQL = SQL + "Insert into history values (" & "'" & Now & "'" & "," & "'" & DataType & "'," & i & "," & VisualBasicReceiveDigitalsTable(i) & ")"
Count = Count + 1
End If
i = i + 1
Loop
rstHistory.Open SQL, ConnectionOne
ConnectionOne.Close
If rtbLog.Text = "" Then
rtbLog.Text = Now & ": " & "Added " & Count & " digital items to the history file"
Else
rtbLog.Text = rtbLog.Text & vbCrLf & Now & ": " & "Added " & Count & " digital items to the history file"
End If
Else
If rtbLog.Text = "" Then
rtbLog.Text = Now & ": No digital items written...loss of communication"
Else
rtbLog.Text = rtbLog.Text & vbCrLf & Now & ": No digital items written...loss of communication"
End If
End If
End Sub
Help With Deleting Efficiently From A Table.
Hi all
I have a program that utilises three tables. Table one is a master that is read from, table two is a temp table where only the required info is written to from the master (and data can be added to the records), and the third is for archiving. I am using an apache database with SQL. The fields from the tables are populated into an MSFlexgrid.
I basically have nine fields per record (so one row of a table). Each record can be updated during the course of it's life. The way I am updating a record (if for example I want to add the word "Completed" to a field within the record), is to delete it from the temp table, then re-insert it using info from the program:
Code:
checklist.Execute ("DELETE FROM checklisttemp_table WHERE (job_time = '" & opschecklist.Text2.Text & "' AND sys_name = '" & opschecklist.Text4.Text & "' AND job_name = '" & opschecklist.Text5.Text & "')")
checklist.Execute ("INSERT INTO checklisttemp_table (job_time, sys_name, job_name, job_status, sign_user, jobname_day)values ('" & edit.Label2.Caption & "', '" & edit.Label3.Caption & "', '" & edit.Label4.Caption & "', '" & edit.Text1.Text & "', '" & opschecklist.Text7.Text & "', '" & edit.Text2.Text & "')")
My problem is, I need a more efficient way of deleting the record before adding it again, as if I have two records that are simular, they will both be deleted.
Unless anyone can think of a better way to do this.
AArrgghh...help.......
Thanks
Lee123
Integer To Binary Efficiently
Hi,
I need to do the following as efficiently as possible:
Convert an integer to Binary and put the 1s & 0s into an array.
This is what I have so far:
Code:
' Convert decimal to binary and fill the array
intQuotient = MyInteger
Do While intQuotient > 0
intReminder = intQuotient Mod 2
intQuotient = intQuotient 2
BinaryArray(ctr) = intReminder
ctr = ctr + 1
Loop
How can I do this any faster?
Is there a function to convert Integer to Binary or something?
Normalising A Database Efficiently.
Ive written some code to normalise a database but it seems to be slow. I've look for some examples on the web and not found any. There is a wizard in access and that seems to be able to work i out pretty quickly. Where am i going wrong ?
Im sure Its just bad programming technique and my lack of knowledge.
I have one large import table of round 30000 records. plus other to import later
tbl_import_temp
Title, Artist, Brand, Format, TitleID,AristID, BrandID, FormatID
My Nomal tables are ...
tbl_titles
TitleID, Title
tbl_Artist
ArtistID, Artist
and so on.....
here is my code......
Code:Function CheckNormalForm(FieldName, TableName)
Dim RS As New ADODB.Recordset
Dim I As Integer
Dim strValue As String
Dim arrGetRows()
strSQL = "SELECT DISTINCT " & FieldName & " FROM tbl_Import_Temp"
Set RS = cn.Execute(strSQL)
arrGetRows = RS.GetRows
RS.Close
For I = 0 To UBound(arrGetRows, 2) ' cycle through distinct rows
strValue1 = arrGetRows(0, I)
strValue = Replace(arrGetRows(0, I), "'", "''")
strSQL = "SELECT " & FieldName & "," & FieldName & "ID FROM " & TableName & " WHERE " & FieldName & " Like '" & strValue & "'"
Set RS = cn.Execute(strSQL)
If RS.EOF Then ' item did not exist in normal table
RS.Close
RS.Open TableName, cn, adOpenDynamic, adLockOptimistic, adCmdTable
RS.AddNew ' add the item
RS(FieldName) = strValue1
RS.Update
End If
intID = RS(FieldName & "ID") ' get its ID
RS.Close ' put the ID in the temp import table to create relationships later
strSQL = "Update tbl_import_temp SET " & FieldName & "ID = " & intID & " WHERE " & FieldName & " Like '" & strValue & "'"
cn.Execute strSQL
Next
Set RS = Nothing
End Function
can anyone give advice on how else to do this ? We import quite a lot of flat files into this database but once a few have been done the process gets slower and slower. I want to get this faster without using SQLServer. (im on access)
Thanks
Synchronize Multiple Databases Efficiently.
4 companies: One database each. (That means 4 DB's)
They all have a table full of vendor information.
I need to check the consistency of the data in the DB's. That is, I need to make sure that every record is present in every DB. While I would be able to do this by looping through each database, reading the primary key and connecteing to each other database to compare the keys, I was wondering if anyone had any insight on a 'better' way of doing this.
Efficiently Assemble Conn In Module Help
I have a connection string public in module. and thay are working fine, code is this:
VB Code:
'in moduleOption ExplicitPublic Const STRING_1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="Public Const STRING_2 = "datadbInventory2.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123;"Public Const STRING_3 = "datacashRegister2.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123;"Public userpath1 As StringPublic userpath2 As StringPublic Function strString1(strUser) As StringstrString1 = STRING_1 & strUser & STRING_2 End FunctionPublic Function String1(strUser) As StringString1 = STRING_1 & strUser & STRING_2 End FunctionPublic Function strString2(strUser) As StringstrString2 = STRING_1 & strUser & STRING_3End FunctionPublic Function String2(strUser) As StringString2 = STRING_1 & strUser & STRING_3End Function 'in formPrivate Sub Form_Load()Dim TmpVal As StringDim net As StringDim loca As String TmpVal = GetSetting("protect", "server", "optserver", "True") 'this is option value If TmpVal = "True" Then net = "\" & GetSetting("protect", "CompName", "txtserver", "") 'this return a value "\toshiba1" 'userpath1 = String1(net) userpath2 = strString1(net) MsgBox userpath2Else loca = GetSetting("protect", "LocalDrive", "txtclient", "") 'this return a value "C:" 'userpath1 = String2(loca) userpath2 = strString2(loca) MsgBox userpath2End IfEnd Sub
1.Any one can give me more efficient way to minimize code into 2 string to use in form (example:conn1 for dbinventory2.mdb and conn2 for cashregister2.mdb)
2.after that I want to put down whole code in module into
Any replay is very much appreciate
best regards,
Efficiently Limit A File To 100 Lines
I am developing a small app that will write a line to a file fairly frequently and I'd like to guard against the file getting longer than about 100 lines. Anyone have an efficient method to do that?
Stucked---how To Write This Loop Efficiently
I have a one dimension dynamic array say, my_array(), for example, this time, it's been populated at run time as
my_array(0)=6
my_array(1)=7
my_array(2)=8
obviously, the actual value and length of array can vary at run time
I want to write a select case such as
select case my_number
case 1 to 6
'do something here...
case 7 to 13
'do something here..
case 13 to 20
'do something here...
'hope you could see the pattern here...
how do I write this more effectively and generically
Moving Shapes Efficiently - For Games
Hi there,
I've been trying to develop small games in vb like pacman, tetris etc but I think I may be moving the shapes around the form in an inefficient manner. For instance to move a shape from side of the form to the other I would write the following in a timer procedure (interval set at say 50ms):
Private Sub timer1()
shape1.left = shape1.left + 25
End Sub
Although this works, it doesn't really make the shape move very smoothly. Is there another way that I can move a shape around on a form??
Best Way To Populate Multiple Combo Boxes Efficiently??
Hi everyone,
I have a form which is going to act as a "bulk entry" sheet for the daily work statistics of the users of my application.
It contains a picture box containing a row of controls which repeats 50 times. One of the controls is the "action" box which will be a combobox populated from a database table. Whenever I have used comboboxes like this in the past I have always populated them in the dropdown event. However as this control is repeated and the drop down action is therefore potentially repeated 50 times, it surely doesnt make sense to hit the db and populate the combo for each instance of the control that is used does it??
Can anyone suggest a more efficient/better practice way of doing this please. Should I be considering something like a disconnected recordset (I'd then only hit the db once and the data to populate each instance of the action control would already be there) or is there an even better way to do this ???
Does Windows Allocate Memory For VB Programs Efficiently?
Well, first I apologize for asking a similar question in another thread - but I feel I did not express myself well enough in that one - and this is a really important issue for me and I would really appreciate any feedback.
This started because I fired up the task manager in XP and tried to watch the memory allocation/deallocation for my program. I was not happy with what I saw (looked like my program had a memory leak) so I went through my code and made sure all of my object references were set to Nothing, when I was done with them. Then I set all my form references to nothing and I even put code in the form terminate event as an extra check that my forms were destroyed.
None of this made any difference so I started doing some experiments with very simple programs. I still noticed the following:
1.) When forms are destroyed, the memory is not freed.
2.) Message boxes result in an increase in memory usage with no corresponding decrease.
3.) In my bigger program I noticed "greedy" behavior. In other words, if I created and destroyed a bunch of objects, memory would increase and not decrease, but then if I created the objects again, memory would NOT increase. It was almost like the program was holding on to memory in case it may need it again later. I feel for a true memory leak, this would not happen - memory allocation would just keep increasing.
Does any of this make any sense? Could setting objects to Nothing in VB code not be enough? Could a system call be needed to force the program to release memory?
Could the garbage collector be on strike?
Thanks in advance.
How To Download Images Efficiently With WebBrowser Control In VB.NET?
I want to download all the images inside an HTML page. I have created a WebBrowser control; navigate to a URL; and cast the webpage as mshtml.HTMLDocument:
Code:WebBrowser.Navigate(txtURL.Text)
Dim HTMLDoc As mshtml.HTMLDocument = WebBrowser.Document
After the call to the Navigate function, all the images in that page should be downloaded. My problem is that I don't know how to extract and save the images directly from HTMLDoc. Currently I am using another way to do the task: get the image src from HTMLDoc and use Internet Transfer Control to download the images. This seems stupid since the same picture is downloaded twice.
Some early post suggested to save the whole webpage in order to get the images. But I don't want to do that, coz in the source file the ".src" attributes within the IMG tags will be changed - I want to extract some information from the original ".src" attributes.
Anyone has a solution to this? Many thanx!
POST Data From Excel On A Website And Receive Data Into Excel, Too
Hi there, I have a challenge!
How can I combine the two next scripts so I can POST data to a website (from my Excel) and the data from the called website to be retrieved into a new worksheet?
[POST SCRIPT]
Code:
'**************************************** Post form data - begin
'Data from Excel
Sub test()
Dim CUI as Range
'CUI is a Unique Identification Code for Employers
Set CUI=[A1]
Call PostRequest("http://www.mfinante.ro/contribuabili/link.jsp?body=/contribuabili/agenti_cod.jsp", _
"cod", _
CUI)
End Sub
'sends form fields specified In Names/Values arrays To the URL
Sub PostRequest(URL, Names, Values)
Dim I, FormData, Name, Value
'Enumerate form names And it's values
'and built string representaion of the form data
Name = URLEncode(Names)
Value = URLEncode(Values)
If FormData <> "" Then FormData = FormData & "&"
FormData = FormData & Name & "=" & Value
'Post the data To the destination URL
IEPostStringRequest _
URL, _
FormData
End Sub
'sends URL encoded form data To the URL using IE
Sub IEPostStringRequest(URL, FormData)
'Create InternetExplorer
Dim WebBrowser: Set WebBrowser = CreateObject("InternetExplorer.Application")
'You can uncoment Next line To see form results As HTML
WebBrowser.Visible = True
'Send the form data To URL As POST request
Dim bFormData() As Byte
ReDim bFormData(Len(FormData) - 1)
bFormData = StrConv(FormData, vbFromUnicode)
WebBrowser.Navigate URL, "_Self", , bFormData, _
"Content-Type: application/x-www-form-urlencoded" + Chr(10) + Chr(13)
Do While WebBrowser.busy
' Sleep 100
DoEvents
Loop
'WebBrowser.Quit
End Sub
'URL encode of a string data
Function URLEncode(Data)
Dim I, c, Out
For I = 1 To Len(Data)
c = Asc(Mid(Data, I, 1))
If c = 32 Then
Out = Out + "+"
ElseIf c < 48 Then
Out = Out + "%" + Hex(c)
Else
Out = Out + Mid(Data, I, 1)
End If
Next
URLEncode = Out
End Function
'**************************************** Post form data - end
[RETRIEVING DATA INTO A WORKSHEET]
Code:
Option Compare Text
Option Explicit
Sub DownloadItemFromHTMLpage()
'an example given below - insert the URL you want
'the website' address after POST
Application.Workbooks.Open ("http://www.mfinante.ro/contribuabili/link.jsp?body=/contribuabili/agenti_cod.jsp")
'you will need to know the range to select for the required
'download, or, you can add in a 'Find' function here to search
'for it - the example given below is for a known range...
Range("A40").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.Close
End Sub
Invalid Data In Excel Spreadsheet/count Group Of Items In Excel Sprdsh
Please help:
Problem#1:
I populated an excel spreasheet with data from access table, using CopyFromRecordset function. The process was successful and all the fields displayed correctly in the spreadsheet, except the date field which displayed:"########". What did I do wrong?
Problem#2:
What vb code can I used to count group of items in the spreadsheet.
Any insight will be very helpful.
Thanks,
C.
Can I Query Data From Multi Excel Tables And Insert Them Into Another New Excel Table
Hi everyone,
Can I query data from multi excel tables and insert them into another new excel table via VC?
i.e. I have excel table A, and excel table B, they have one same column "num"
I hope I can join these two tables into one new excel table via this public coulmn, all the steps should be finished via VC (please tell me if you have other suggestion about the program tool ^_^)
Seems that the function of "Microsoft query" in the Excel is OK, But I hope I can do a good UI and user don't need to write sql by himself, so it is better if you can tell some reference about how to query from muti-tables (I think it should be muti-datasource) and insert this result to another excel table,Thanks a lot :-)
AND how to implement it? please give me more doc if it is possible, Thanks!
I'm not familar with sql syntax about excel, could you please tell more about this? thanks!
Thanks
Lindsay
Excel-VBA Q: Mill's Posting Excel 101: Filtering Data Using Multiple Combobox
Sub: Excel-VBA Q Mill's posting Excel 101: filtering data using multiple combobox - how to indirectly reference the
I joined this group yesterday. Thanks to all who are actively participating. I read Mill's posting 'Excel FAQ - Excel 101 ' ; thanks for the sample code.
I love Excel, and has been using it for 7 years - good at formula, macros etc, not programming with VBA. Now I want to venture into that area. The last time I used VB was in 1998 - VB5, worked just on one project. I am good at OOA, OOD, and have done C++ programming.
I have a question with reference to the code found in the Lesson 3 - Having items in one ComboBox filter items in another ComboBox.
In the FilterList() subroutine, could I indirectly specify the argument for the Case statement through a cell reference instead of directly specifying it in the subroutine.
e.g.
Select Case strCompany
Case $D$1
Select Case strCompany
Case "Apple"
Actual code from Mill's posting:
Sub FilterList()
Dim strList As String, strCompany As String, strEmployee As String
On Error GoTo FilterListError
strCompany = Range("B1").Text
Select Case strCompany
Case "Apple"
strList = "=$F$2:$F$6"
Case "IBM"
strList = "=$G$2:$G$4"
Case "Microsoft"
strList = "=$H$2:$H$4"
End Select
Regards & Thanks
How To Import Excel Data From A Closed Excel File To Access Table?
Hi friends,
i have the following Excel sheet
A B
1 BILL PAID
2 CM20 650.00
3 CM32 1750.12
I want to import the same data into an Access table with the same field name.
Any help?
Thankx and regards
Haris
_____________________________________________________________________
Edited by - harisraz on 6/18/2006 1:33:00 AM
Adding Data To An Excel Spreadsheet, Deleting Data, Making Calculations, Etc
I'm trying to add data to my excel database using a form that i created.
Database.bmp is my main screen.
Form.bmp is the form that i would like to use to add data to the excel sheet.
Does anybody know the proper way to do this? Should i even be using excel to do this?
I also need to make things that will report Interest over 1 year, add money to accounts, create certificates to print, etc.
How would i initiate a calculator between certain cells?
Is there anyway to make it so that you can double click a cell and edit the invidiual customer?
I need some guidance, Thanks in advance!
Write Data To Pocket Excel Or Excel 2000?
Hi,
I'm working on a nifty little counting program that will insert its data into an Excel 2000 worksheet... I know that in Excel 2000 you can use it's built in version of VBA to do it....
Private Sub Hotkey_Click()
Dim counter As Single
counter = Count.Caption + 1
Count.Caption = counter
Cells(7, 11) = Count.Caption <---see right there...
ActiveWorkbook.Save <---Look, I'm saving the data...
End Sub
Private Sub UserForm_Activate()
Count.Caption = 0
End Sub
Now this works fine for a PC but I would like to make a version of this counter using eVB, is there any way I can export data to an excel 2000 worksheet on a network drive?
Thanks,
ERic
Placing SQL Data In Excel And Then Converting To Graph Data.
The following code is working pretty good at pulling data over into Excel and then making a graph based on the data. My only two complaints is
1.) I want above the actual SQL data that's listed in both column 'A' and column 'B' a header that describes what each column is.
2.) For some reason the graph will not display the first row of data. Everything else listed below row 1 gets placed in the graph.
I think if I could move the data down past row1 when it pulls the data over from recordset to Excel then I could accomplish both 1) and 2)
But I don't understand the code too well since I pulled it from a textbox.
I've got the following in a global module:
Code:
Option Explicit
Public gobjExcel As Excel.Application
Public Function CreateExcelObj() As Boolean
On Error GoTo CreateExcelObj_Err
CreateExcelObj = False
'Attempt to Launch Excel
Set gobjExcel = New Excel.Application
CreateExcelObj = True
CreateExcelObj_Exit:
Exit Function
CreateExcelObj_Err:
MsgBox "Couldn't Launch Excel!!", vbCritical, "Warning!!"
CreateExcelObj = False
Resume CreateExcelObj_Exit
End Function
Public Sub CloseExcel()
If Not gobjExcel Is Nothing Then
gobjExcel.DisplayAlerts = False
gobjExcel.Quit
End If
CloseExcel_Exit:
Set gobjExcel = Nothing
Exit Sub
CloseExcel_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume CloseExcel_Exit
End Sub
Then at my command button on my main form I have the following:
Code:
Private Sub cmdSearch_Click()
Dim sMonth As String
Dim sYear As String
Dim rstCount As ADODB.Recordset
Dim fld As ADODB.Field
Dim rng As Excel.Range
Dim objWS As Excel.Worksheet
Dim intRowCount As Integer
Dim intColCount As Integer
sMonth = cboMonth.ListIndex + 1
sYear = frmOrderSelectmonth.cboYear.Text
frmOrderSelectmonth.Hide
Dim adData As ADODB.Recordset 'primary recordset for viewing
Set adData = New ADODB.Recordset
Dim sqTotal As String
If optSales.Value = True Then
sqTotal = "SELECT [tblOrders].[fSalesID], Count([tblOrders].[fOrder]) AS Total From tblOrders Where " & _
"(((Month([fEntrydate])) = '" & sMonth & "') And ((Year([fEntrydate])) = '" & sYear & "')) " & _
"And ([tblOrders].[fVoid] = False) GROUP BY [tblOrders].[fSalesID];"
adData.Open (sqTotal), frmOrderMain.g_cData, adOpenDynamic, adLockOptimistic
If adData.BOF Or adData.EOF Then
MsgBox "No Data found!"
Exit Sub
End If
End If
If optData.Value = True Then
sqTotal = "SELECT fUser, Count([fOrder]) AS Total FROM tblOrders WHERE "
sqTotal = sqTotal + "Month([fEntrydate]) = '" & sMonth & "' And " & _
"Year([fEntrydate]) = '" & sYear & "' And fVoid = False GROUP BY fUser;"
adData.Open (sqTotal), frmOrderMain.g_cData, adOpenDynamic, adLockOptimistic
If adData.BOF Or adData.EOF Then
MsgBox "No Data found!"
Exit Sub
End If
End If
'Display Hourglass
DoCmd.Hourglass True
'Attempt to create Recordset and launch Excel
If CreateExcelObj() Then
gobjExcel.Workbooks.Add
Set objWS = gobjExcel.ActiveSheet
intRowCount = 1
intColCount = 1
'Loop though Fields collection using field names
'as column headings
For Each fld In adData.Fields
If fld.Type <> adLongVarBinary Then
objWS.Cells(1, intColCount).Value = fld.Name
intColCount = intColCount + 1
End If
Next fld
'Send Recordset to Excel
objWS.Range("A1").CopyFromRecordset adData, 500
'Format Data
With gobjExcel
.Columns("A:B").Select
.Columns("A:B").EntireColumn.AutoFit
.Range("A1").Select
.ActiveCell.CurrentRegion.Select
Set rng = .Selection
'.Selection.NumberFormat = "$#,##0.00"
'Add a Chart Object
.ActiveSheet.ChartObjects.Add(135.75, 14.25, 607.75, 301).Select
'Run the Chart Wizard
.ActiveChart.ChartWizard Source:=Range(rng.Address), _
Gallery:=xlColumn, _
Format:=6, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels _
:=1, HasLegend:=1, Title:="Number of orders assigned to Salespeople", CategoryTitle _
:="", ValueTitle:="", ExtraTitle:=""
'Make Excel Visible
.Visible = True
End With
Else
MsgBox "Excel Not Successfully Launched"
End If
DoCmd.Hourglass False
cmdCreateGraph_Exit:
Set rstCount = Nothing
Set fld = Nothing
Set rng = Nothing
Set objWS = Nothing
DoCmd.Hourglass False
End Sub
Whether you're dealing with SalesID or User's total count
It would look like this in the queries:
Stephen 24
Jesse 18
Frank 10
Amber 17
I just want to place above that data the headers in Excel:
Name Total
Stephen 24
Jesse 18
Frank 10
Amber 17
then it would work great.
Any help is appreciated!
Thanks
Modifying Excel Data Using Data Control Object
This is a re-post of a previous message with a new wrinkle.
I am creating a form filled with text boxes. These text boxes will contain values that are also cell values of an Excel spreadsheet. I have included a Data Control object on the form that points to the Excel spreadsheet. Each of the text boxes on the form points to a particular field (cell) in the spreadsheet through it's DataSource / DataField properties. The Excel file contains only 2 rows of information. The 1st row is the data description, and the 2nd row contains the data. I have discovered that when I change the values in the text boxes, the corresponding cell values in the Excel spreadsheet do not change. If I then close the VB application, the Excel values are updated. My question is: how do I "force" the Excel worksheet to be updated without closing the VB application?
Transfering Data Form A Data Grid To Excel
Hi guys.
I have a program in VB6 which load data from an access table into a data grid, now I need to trasfer that data in the grid to an excel sheet. How can I do this?.
Thanks in advance
Excel - How To Get Formatted Data, Not Original Data Through Code
Hi all,
I am reading through an excel column, and I need to get a number from each cell. However, I don't want the original number i.e. 65.382745, I want the number the user has specified by defining the number of decimal places i.e. 2 places 65.38, 3 places 65.383.
I am currently using the sheetname.cells(row,col) property to get at the data, how do I get the formatted data?
TIA
Noogle
Edited by - original_noogle on 10/6/2003 7:59:13 AM
Copy Excel Data To New Excel Workbook HELP!
I have a excel workbook with thousands of peoples names and info. A persons record can be in the workbook more than once. I want to create a macro or somthing in excel to copy all of the same people with the last name Smith for example to a new workbook. How do I go about doing this, anyone with example code or help.
Thanks
Extract Data From/write Data To Excel?
Hi
I'm making a program in VB which needs to get & give data to an Excel-sheet. However, I don't know how to copy data to and from an open Excel-workbook :S
Can you guys help me?
How To Get Data From EXCEL And Use It And Put Some New Back To EXCEL.
This is a multi-part message in MIME format.
--------------InterScan_NT_MIME_Boundary
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_02C3_01C15E10.BC027590 "
------=_NextPart_000_02C3_01C15E10.BC027590
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
I want some help , I'm new and it is many years since I haveprogramming.
And now I want to learn, and I know the best way is to try and fail.
So I have start on a project where I will read a EXCEL Sheet with 4colums
and I want to have a window with 4 text boks.And if I search for anumber or
a word and seek in one of them it will fill in the word, string ornumber and put what is in the other 3 rows
and fill in the other 3.
As you can see I nead all the help I can get :-)
Anyone that can help me ?
The sheet is like this
column 1 column 2 column 3 column 4
400000 This is a test L-12345 The test is goingwell
I want a window with 4 txt box that In can seek from the EXCEL SHEET and
I also want to put back data to the excel sheet if it is empty column orrow...
I know I ask for a lot, but I had to try to get all the help I can aslong as I want to learn.
Thank you everyone
Erik
email@removed
------=_NextPart_000_02C3_01C15E10.BC027590
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1"http-equiv=Content-Type>
<META content="MSHTML 5.00.2919.6307" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial size=2>Hi</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>I want some help , I'm new and it ismany years
since I have programming.</FONT></DIV>
<DIV><FONT face=Arial size=2>And now I want to learn, and I know thebest way is
to try and fail.</FONT></DIV>
<DIV><FONT face=Arial size=2>So I have start on a project where Iwill read a
EXCEL Sheet with 4 colums</FONT></DIV>
<DIV><FONT face=Arial size=2>and I want to have a window with 4 textboks.And if
I search for a number or </FONT></DIV>
<DIV><FONT face=Arial size=2>a word and seek in one of them it willfill in the
word, string or number and put what is in the other 3 rows</FONT></DIV>
<DIV><FONT face=Arial size=2>and fill in the other 3.</FONT></DIV>
<DIV> </DIV>
<DIV>As you can see I nead all the help I can get :-) </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>Anyone that can help me ?</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>The sheet is like this</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>column 1
column 2 column 3
column 4</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>400000 This is
a test L-12345
The test is going well</FONT></DIV>
<DIV> </DIV>
<DIV>I want a window with 4 txt box that In can seek from the EXCELSHEET and
</DIV>
<DIV>I also want to put back data to the excel sheet if it is emptycolumn or
row...</DIV>
<DIV>I know I ask for a lot, but I had to try to get all the help I canas long
as I want to learn.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>Thank you everyone</FONT></DIV>
<DIV>Erik</DIV>
<DIV><A
href="mailto: email@removed ">erik.al email@removed <A></DIV></FONT></DIV></BODY>< /HTML>
------=_NextPart_000_02C3_01C15E10.BC027590--
--------------InterScan_NT_MIME_Boundary--
Get Data From Excel And Match With SQL Data
Hi all,
My boss asked me to write a program which will open a SQL database, run a query, pull some data (4 columns), concatenate this data with some text, and save the result into a tab delimited text file. Somehow (with the help of postings in this forum and some luck) I got the program working. Now we found out that 2 columns contain wrong info, so the fix is to get data from an Excel file. The idea is to read the 2 columns in the Excel file, match them with the other 2 good columns from SQL database, and then output them to the tab delimited text file (of course after concatenation). To make it even worse, the Excel is password protected for open and modify. First of all, is this doable?
I searched this forum and this is what I’ve been trying to use, unsuccessfully I might add:
Code:
Private Sub Form_Load()
Dim xlApp As Excel.Application
Dim xw As Excel.Workbooks ' for references to workbooks!
' First see if Excel is already running
On Error Resume Next
Set xlApp = GetObject("C:Test.xls")
On Error GoTo 0
Set xw = xlApp.Workbooks
xw.Open " C:Test.xls "
labelStatus.Caption = "Loaded successfully!"
' Get SQL data and do manipulations here. ...
'xw.Close
End Sub
When I run this I get a “Run-time error 91 – Object variable With block variable not set” on this line:
Code:
Set xw = xlApp.Workbooks
When I step thru the program I can also see that xlApp is set to Nothing…
Can anyone help please?
Thanks a lot,
mike
Serial Data In, Data Out To Excel Help
I need help getting serial recieved data from VB 6.0 into an Excel spreadsheet. I can get the data in with no problem but do not know how to get it into Excel. The spread sheet needs to be up to 26000 rows by 10 colums. I can get the number of rows it that helps. What I want to do is:
open the worksheet
bring in a serial data (integer)
put it into the correct Excel cell
get the next data
save and close the spreadsheet
This is my first atempt at using VB and Excel.
Thanks for the help.
How To Get The Data From A Excel Sheet And Use The Data.
This is a multi-part message in MIME format.
--------------InterScan_NT_MIME_Boundary
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_01AC_01C15D7E.BEC52C60 "
------=_NextPart_000_01AC_01C15D7E.BEC52C60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
I want some help , I'm new and it is many years since I haveprogramming.
And now I want to learn, and I know the best way is to try and fail.
So I have start on a project where I will read a EXCEL Sheet with 4colums
and I want to have a window with 4 text boks.And if I search for anumber or
a word in one of them it will take what is in the row and fill in theother 3.
Anyone that can help me ?
The sheet is like this
column 1 column 2 column 3 column 4
400000 This is a test L-12345 The test is goingwell
Thank you everyone
------=_NextPart_000_01AC_01C15D7E.BEC52C60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1"http-equiv=Content-Type>
<META content="MSHTML 5.00.2919.6307" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>I want some help , I'm new and it ismany years
since I have programming.</FONT></DIV>
<DIV><FONT face=Arial size=2>And now I want to learn, and I know thebest way is
to try and fail.</FONT></DIV>
<DIV><FONT face=Arial size=2>So I have start on a project where Iwill read a
EXCEL Sheet with 4 colums</FONT></DIV>
<DIV><FONT face=Arial size=2>and I want to have a window with 4 textboks.And if
I search for a number or </FONT></DIV>
<DIV><FONT face=Arial size=2>a word in one of them it will take whatis in the
row and fill in the other 3.</FONT></DIV>
<DIV><FONT face=Arial size=2>Anyone that can help me ?</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>The sheet is like this</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>column 1
column 2 column 3
column 4</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>400000 This is
a test L-12345
The test is going well</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>Thank youeveryone</FONT></DIV></BODY></HTML>
------=_NextPart_000_01AC_01C15D7E.BEC52C60--
--------------InterScan_NT_MIME_Boundary--
Fill A Vba Form With Excel Workbook Data, Updating This Wrkbook W/ Data From The Form
Okay I have a basic to intermediate understanding on using vba and Excel utilities. I need help with the workflow of my application rather than specific procedures.
I've got data on only one worksheet where each record is defined by an account id. Every cell on each record is populating a specific textbox on a form. The form is a basic interface for users, moving from one record to next. So mostly the data will flow from the worksheet to the form with the exception of one field for each record. The user will update that field as needed on the form. There are only 30 records or so on the worksheet. I can't rationalize learning Access and/or some database connection since there are only a few records and few fields.
This is an example of the field headers and one record:
Account Id, Account Name, Total Balance, Past Due Balance, Status
011,ChevyChaseAccount,$1200,$100,Customer sent in check (not posted).
I can think of only one way to update the form with the data and that is piece by piece:
Code:
Private Sub UserForm_Initialize()
With frmDetail
'.txtAccountName = ActiveSheet.ActiveCell.Offset(0, 2).Value (this gives me an error since I can't use the offset property for the code in forms)
.txtAccountName = ActiveSheet.Range("B2").Value (since I can't use offset then I have to resort in a specific address)
.txtAccountId = ActiveSheet.Range("C2").Value (since I can't use offset then I have to resort in a specific address)
.txtTotalBalance = ActiveSheet.Range("D2").Value (since I can't use offset then I have to resort in a specific address)
End With
End Sub
I haven't thought of a way to move from one record to another let alone how to update that one field to another.
Let me know if you have any questions,
Thanks,
Aaron
If I Save A Text File (data Crlf Data Crlf...) As Excel,
it will open fine unless i got some numeric entries starting by zero(s).... i will lose the leading zeros.
same problem if the entry is a 16 or more digits number...
my data will be formatted like.. 1.4325etc E10...instead 14325etc
adding an leading unserscore is not a solution...
see what i mean ?
Data To Excel
Hi, how I can send data directly to excel, without a comma-delimited file, only send data and formatt to a cell. ?
Thanks !!
Data To Excel
If i have some data which user of my applcation entered...
Is it possible to create Excel file, pass the data into its cells make some algoritmic functions and change some colors.
and the worse is...
there is no Office installed... don't ask why not install it, my client wish so.
thx
Help With Data From Excel
I have two Excel documents, one that has codes with addresses and one with system names with addresses. What I'm trying to do is to have a form that you enter either a code or system name and then it will look at the excel documents and within the form give the address where the equipment is at. The location of these files are on the network. I would like a listing where the user can select the correct location of these files.
Data Through Excel
Hi,
I have the following code that draws some data from excel, and puts it into a listbox, this works great.... but I would prefer it to not obtain the data from the first line in the excel. I just can't figure out how to correct this?
Thanks for your help
rgds.
[[[[[ code ]]]]]
Private Sub Form_Load()
Dim objXLApp As Excel.Application
Dim intLoopCounter As Integer
Dim count As Integer
Set objXLApp = New Excel.Application
With objXLApp
.Workbooks.Open "C:my documentsvbaccountsdata1.xls"
.Workbooks(1).Worksheets(1).Select
count = 1
While StrComp(.Cells(count, 1), "") <> 0
Form1.List1.AddItem .Range("A" & count)
count = count + 1
Wend
objXLApp.Workbooks.Close
Form1.List1.ListIndex = 0
Form1.Show
End With
Set objXLApp = Nothing
End Sub
|
TRACKER
