Excel instance does not close from access vba

[Ruzz2k]

Hi I have two problems excel instance does not close at the end and two when I try to do the looking using text it works but soon as I try using date/time it returns nothing.

any help is appreciated.

thanks

Private Sub Command84_Click()
Dim objExcel As Excel.Application
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number = 429 Then 'Excel not running - start it
    Set objExcel = CreateObject("Excel.Application")
    Err.Clear
End If
 

Dim result As String
Dim A As String
Dim B As String
'A = "03/05/2013  11:26:00"
A = "A"
'A = Format("03/05/2013 11:27:00", "dd/mm/yyyy hh:nn:ss")
B = "A1:C100"
'Sheets("Test").Activate
'vlookupVBA = Application.WorksheetFunction.VLookup(lookupValue, table_lookup, colOffset, False)
'result = vlookupVBA(A, B, 2)
objExcel.Workbooks.Open "C:\Test.csv", True, False
objExcel.Visible = False
Sheets("Test").Activate
'result = "#N/A"
'With Workbooks("C:\Test.csv").ActiveSheet
Dim myrange As Range
Set myrange = Range("A1:C100")
result = objExcel.Application.VLookup(A, myrange, 3, False)

'result = objExcel.Workbooks("C:\Test.csv").Worksheets("Test").Range("A1:C100")
 
 
MsgBox result

ActiveWorkbook.Close SaveChanges:=False
objExcel.Application.Quit
'objExcel.Workbooks.Close
'objExcel.Quit
'End With
Set objExcel = Nothing
End Sub

 

[Bilbo_Baggins_Esq]

Not a full solution, but some thoughts.

I don't think it will be easy to find/match/lookup a date/time combination.
Date = yes, date/time = difficult at best.
Try capturing the full serial value


as for the Excel object, your code does not show how/where it is created

 

[namliam]

A date is a number / double value, a proper date is anyways... You are looking for a TEXT/String value.... when looking for a string you will never find a date since they will not match ever....

'objExcel.Quit
that should close excel, however you have commented it out.... thus excel will not close/quit

 

[Ruzz2k]

Hi, just to give some perspective, I have created an access database user enters 20 time values along with a date. There are .csv files which will have a datetime in column A and a temperature (double value) in column c usually.
I wanted to do lookups based on the twenty different times to populate a series of text boxes. Can I create an array or something? Or is date time going to be too hard to deal with?
Note I combine the date and time in the database when I save the data so I assumed I can do the same when I do a lookup.
Thanks,

 

[namliam]

It will depend on a few factors, combining date and time is not a problem... but it will depend on how you store them and how they are stored in the file.

If you have the data already inside the database, combined, why go back to the spreadsheet to look it up ?

 

[Ruzz2k]

Hi, I am entering this data which then goes on through complex calculations.
There is a date "dd/mm/yyyy" 20 x time values "hh:nn:ss" and then 20 x ref 1 readings, 20 x ref 2 readings and 20 x working readings. Now the user sometimes enters them manually on the form but those done on an automated system (wireless automatic temperature collection) are stored in a csv file automatically, now rather than type the data back in I thought once the reading times have been entered on the form (usually the user enters 1 time and an interval and the 20 times are populated) so I need the ability for the user to select the file for each of the ref 1, ref 2 and working ref and then lookup the datetime and subsequently the temperature readings and load them into the form instead of typing them in.

Datetime is critical as the time is entered on the database form and the date and the csv file also has matching datetime that corresponding temperature values I need to pull into the text boxes.

I hope that makes sense, else I will have to explain better when I get home.

Thanks.

 

[namliam]

if possible uploading a (faked) file will go a long way to making it more understandable...
If possible too a reduced version of the database or atleast a screenshot of what you are trying to achieve, a picture can paint a thousand words...

 

[Ruzz2k]

Hi please find example of ms access form and csv file.
Note user enters first time and rest get populated using the interval.
CSV file, I would like to keep it flexible i.e it could have more than 20 lines for temp/datetime and i would like it to pick the appropriate line.

So Using 1-20 on the database first time+date compare to csv file and pick the corresponding temp in column c and put it into the corresponding box for ref 1 and then do the same upto 20 before the next file is selected for ref 2 and finally the one for ref 3.
If there is no workaround I would have to make sure the user trims down the csv so there are only 20 lines which is not convenient as I want to save time.

I hope this makes more sense.
thanks

 

[Rx_]

Just a quick comment for consideration.
Take some time to evaluate all the date time conversions in VBA.
Date / time is nothing more than the number of days since 1900 and the fraction of a day
Sorry, the attachment I put up shows only 0.15. If more resolution was presented the time 3:35AM it would be 0.1493055555556 That is the fraction of one day.

There is a presentation layer of the date / time depending on the calendar system chosen. But, the data is actually numeric.

Now() + 1 = tomorrow

Convert and move everything over to Excel in Numeric, then simply write vba code to re-format the presentation layer as Date/time.
When performing a lookup, the > < or between works great.
Present one layer to the user, use the formulas to sort, locate in numeric.

 

[namliam]

It will depend on a number of factors, is there only one text file? Or multiple
How is the text file opened? In code? a linked table? You are doing it in excel, but code or linked table would be better in this case I believe...
Have you considered using a linked table or code to source the file instead of excel?

The problem here though is that adding data in a table like this is going to be a nightmare.... however perhaps if times are filled automagicaly adding in the data at that time might be feasable.

 

[Ruzz2k]

hi, The .csv files will be different every time. So I was counting on the user selecting the file then opening in code in background to lookup the data. The basic structure of the csv file is the same hence why I thought vlookup.

If there is no straightforward way of doing this I may have to consider what rx is saying regarding using the date serial and converting the dates in the excel file before a lookup

I don't know where to go from here.

 

[namliam]

You can still dynamicaly (re)link the file as a linked table without much issues.

On the other hand the "real" problem with your vlookup is, as RX and I said, is the difference between a text date and a real date.... you are never going to match the two.
The second problem really is you are trying it the (very) hard way I think... Assuming the file is ordered always (per your linked file) you can open the file in code and read it line by line or indeed use a linked file and just query it