New to VB & Module and trying to make this work (1 Viewer)

[kingsransomracing]

Here is what I am trying to do.

I have a macro I created in excel that pulls data from a csv file (created by my weatherstation) and creates an xml file (used by my access program). This macro works great.

Sub Autpen()

'Open csv file
Workbooks.Open Filename:="C:\users\mcagnew\Desktop\Kestrelweather.csv", ReadOnly:=True
Application.Wait Now + TimeValue("00:00:01")

'Find max row value
Dim rng As Range
Dim dblMax As Double
Dim ws As Double
Dim tp As Double
Dim wc As Double
Dim rh As Double
Dim mo As Double
Dim hi As Double
Dim dp As Double
Dim wb As Double
Dim ap As Double
Dim bp As Double
Dim al As Double
Dim da As Double
Dim ad As Double
Dim rad As Double
Set rng = Range("a:a")
dblMax = Application.WorksheetFunction.Max(rng)

'Select Data
Application.Rows.Find(dblMax).Select
Selection.Offset(, 1).Select
ws = ActiveCell.Value
Selection.Offset(, 1).Select
tp = ActiveCell.Value
Selection.Offset(, 1).Select
wc = ActiveCell.Value
Selection.Offset(, 1).Select
rh = ActiveCell.Value
Selection.Offset(, 1).Select
mo = ActiveCell.Value
Selection.Offset(, 1).Select
hi = ActiveCell.Value
Selection.Offset(, 1).Select
dp = ActiveCell.Value
Selection.Offset(, 1).Select
wb = ActiveCell.Value
Selection.Offset(, 1).Select
ap = ActiveCell.Value
Selection.Offset(, 1).Select
bp = ActiveCell.Value
Selection.Offset(, 1).Select
al = ActiveCell.Value
Selection.Offset(, 1).Select
da = ActiveCell.Value
Selection.Offset(, 1).Select
ad = ActiveCell.Value
Selection.Offset(, 1).Select
rad = ActiveCell.Value

'Change Date to m/d/yyyy HH:mm:ss format
Dim sec As Double
Dim min As Double
Dim hr As Double
Dim MDate As Date
Dim NDate As Date
Dim Odate As Date

days = Int(dblMax / 86400)
hr = Int((dblMax - (Int(days * 86400))) / 3600)
min = Int((dblMax - (Int(days * 86400)) - Int(hr * 3600)) / 60)
sec = Int((dblMax - (Int(days * 86400)) - Int(hr * 3600)) - Int(min * 60))

LDate = DateAdd("d", days, "1/1/2000 00:00:00")
MDate = DateAdd("h", hr, LDate)
NDate = DateAdd("n", min, MDate)
Odate = DateAdd("s", sec, NDate)

'Create XML file
Dim attArray As Variant
Dim i As Integer
Dim xmlFile
xmlFile = "C:\users\mcagnew\Desktop\RunData.xml"

Open xmlFile For Output As #1
Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & _
" encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"
'' < add xml-schema if it's needed here >
Print #1, "<KestrelSamples>"
Print #1, "<KestrelSample>"
Print #1, "<DateTime>" & Odate & "</DateTime>"
Print #1, "<Wind_speed>" & ws & "</Wind_speed>"
Print #1, "<Temperature>" & tp & "</Temperature>"
Print #1, "<Wind_chill>" & wc & "</Wind_chill>"
Print #1, "<Relative_Humidity>" & rh & "</Relative_Humidity>"
Print #1, "<Moisture>" & mo & "</Moisture>"
Print #1, "<Heat_index>" & hi & "</Heat_index>"
Print #1, "<Dew_point>" & dp & "</Dew_point>"
Print #1, "<Wet_bulb>" & wb & "</Wet_bulb>"
Print #1, "<Absolute_pressure>" & ap & "</Absolute_pressure>"
Print #1, "<Barometric_pressure>" & bp & "</Barometric_pressure>"
Print #1, "<Altitude>" & al & "</Altitude>"
Print #1, "<Density_altitude>" & da & "</Density_altitude>"
Print #1, "<Air_density>" & ad & "</Air_density>"
Print #1, "<Relative_air_density>" & rad & "</Relative_air_density>"
Print #1, "</KestrelSample>"
Print #1, "</KestrelSamples>"
Close #1

'Activate and close csv file
Windows("Kestrelweather.csv").Activate
ActiveWorkbook.Close
ActiveWorkbook.Saved = False

'Close Macro
ActiveWorkbook.Saved = True
Application.Quit


End Sub

In access now I have this macro:

RunApplication

Command Line excel.exe C:\KestrelWeather_sorted.xlsm)
​

RunSavedImportExprot

Saved Import Export Name Import-RunData
​

Requery

Control Name
​

GoToRecord

Record Last
​

So how this works is the first RunApplication opens the excel file, runs the macro above and exits excel.

The second RunSavedImportExport runs an import of the xml data into my database.

The issue I am having is that the macro doesn't wait for the Run Application of the excel to finish. so I came on here and found that I can do a shell and wait function by creating a module. I tried it but continue to get an error (probably because I don't know how to use it).

In a module I wrote this:


Declare Sub Sleep Lib "kernal32" (ByVal lngMilliseconds As Long)

Sub Wait(dblSeconds As Double)
For i = 1 To dblSeconds * 1000
DoEvents ' handle events
Sleep (10) ' suspend process without a performance hit
Next
End Sub

Then in the above macro in between the RunApplication and RunImportExport I did a RunCode and tried writing in both Wait and I tried Sleep. Neither one of these worked.

Ultimately what I would like to do is move the macro I am running in excel directly into access, so I don't have to depend on access opening excel everytime (this is on a 30 second cycle) but some of the macro commands I used in excel don't work in access (eg. Application.Wait Now + TimeValue("00:00:01"))

Do you have any suggestions on how I can make this work the best?

Remember I am new to VB code so go easy on me

Thanks.

 

[kingsransomracing]

Nevermind all. I managed to get it working. I just have one issue which I will create a new post with

 

[Kiwiman]

Howzit

Glad you got it working but is there any reason why you don't just import the csv straight into access?

 

[kingsransomracing]

The reason is because the csv file is not just line by line data. it looks like this:
------------------------------------------------------------------------------------------
FoxTerm Info: Logging started.

> S

> DT WS TP WC RH MO HI DP WB AP BP AL DA AD RA

s mph °F °F % gpp °F °F °F inHg inHg ft ft lb/f³ %

3.82E+08 0 75.1 75 37.5 50.7 72.7 47.4 58.6 28.58 28.57 1255 2737 0.071 92.2

>

FoxTerm Info: Logging stopped.

FoxTerm Info: Logging started.

S

> DT WS TP WC RH MO HI DP WB AP BP AL DA AD RA

s mph °F °F % gpp °F °F °F inHg inHg ft ft lb/f³ %

3.82E+08 0 75 75 37.5 50.7 72.7 47.4 58.6 28.58 28.57 1253 2737 0.071 92.2

>

FoxTerm Info: Logging stopped.
-------------------------------------------------------------------------------------------------------------
. This cycles every 10 seconds and it will fill up the access database with unwanted information. so before it gets imported I need to sort through the data and get the most recent poll from the weather station

 

[Kiwiman]

Howzit

Say no more

 

[kingsransomracing]

The current problem I am having is the code below works great when I don't have the Kestrelweather.csv file open, but when I open it (to simulate the weather station writing to the file) it generates an error saying it can't open the file.

The funny thing is that when it opens the file it says "read only" on the top of the excel window, so its opening it in read only, but for some reason still won't open it if another program has it open.

'Open csv file
Excel.Application.Workbooks.Open "C:\users\mcagnew\Desktop\Kestrelweather.csv", , True
Excel.Application.Visible = True
Excel.Application.Wait Now + TimeValue("00:00:03")

 

[jdraw]

You could try using the FileSystemObbbject, but you will have to identify how you want to open the file... For Reading, For Writing...

There is info here
http://msdn.microsoft.com/en-us/library/aa155438(office.10).aspx

It may not be relevant, but you don't have to open a csv with Excel..