Convert .xls to delimiter File in Excel2003?

[Rpax]

Hi,

I was wondering if you can help. I'am trying to Open a Excel sheet that is linked to a PI database, wait 10sec for the data to refresh and then save and close the file. I'am trying to save the file as a txt delimiter file so to take a snapshot of the updated data (and not the format links to PI). Below is my effort


MyDateFormat = Year(now) & Right("0" & Month(Now), 2) & Right("0" & Day(now), 2)
Set Excel = CreateObject("Excel.Application")
Excel.Application.DisplayAlerts = False
Excel.Application.Visible = True
Excel.Workbooks.Open("C:\Program Files\Solvent_Tanks.xls")
Excel.RegisterXLL "pipc32.xll"
Excel.AddIns("PI-DataLink").Installed = False
Excel.AddIns("PI-DataLink").Installed = True
wscript.sleep(10000) 'sleep 10 seconds
Excel.ActiveWorkbook.SaveAs "C:\Program Files\Solvent_Tank_Reports\Solvents_" & MyDateFormat & ".txt", xlText, False
Excel.Activeworkbook.Close
Excel.Quit
WScript.Quit

The problem that I'am having is the saving of the file?. Also, I'am open to other ideas how I can get the raw data from the file (the snapshot of the data). Any ideas what's wrong?

Thanks - Rpax

 

[rpaulson]

try -

replace this

Excel.ActiveWorkbook.SaveAs "C:\Program Files\Solvent_Tank_Reports\Solvents_" & MyDateFormat & ".txt", xlText, False

with

wline = ""
For r = 1 To Range("A65536").End(xlUp).Row
    lcol = Cells(r, 256).End(xlToLeft).Column
    wline = wline & Join(Application.Transpose(Application.Transpose(Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
Open "C:\Program Files\Solvent_Tank_Reports\Solvents_" & MyDateFormat & ".txt"  For Output As #1 'Replaces existing file
Print #1, wline
Close #1

hth,
Ross

 

[Rpax]

Hi Ross,

Many thanks for replying. I've tried below and what you recommended but got an error "Expected End of Statement" Line 14 Char 6". Sorry I can't add any insight as just dipping my toe into VB.

MyDateFormat = Year(now) & Right("0" & Month(Now), 2) & Right("0" & Day(now), 2)
Set Excel = CreateObject("Excel.Application")
Excel.Application.DisplayAlerts = False
Excel.Application.Visible = True
Excel.Workbooks.Open("C:\Program Files\Solvent_Tanks.xls")
Excel.RegisterXLL "pipc32.xll"
Excel.AddIns("PI-DataLink").Installed = False
Excel.AddIns("PI-DataLink").Installed = True
wscript.sleep(10000) 'sleep 10 seconds
wline = ""
For r = 1 To Range("A65536").End(xlUp).Row
    lcol = Cells(r, 256).End(xlToLeft).Column
    wline = wline & Join(Application.Transpose(Application.Transpose(Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
Open "C:\Program Files\Solvent_Tank_Reports\Solvents_" & MyDateFormat & ".txt"  For Output As #1 'Replaces existing file
Print #1, wline
Close #1
Excel.Activeworkbook.Close
Excel.Quit
WScript.Quit

 

[Rpax]

Hi,

I've taken a different approach to saving this data. What I'am trying now is to copy the Data from one excel file to another.

The code below is opening a new work book. I then open my excel file that is linked to my PI data and I try to Copy and Paste the cells from this PI workbook to the other excel book. I then do a saveas with todays date to the new excel book. However, the Copy and Paste doesn't work (Subscription Out of Range Line 19). Appreciate an suggestions on this?

MyDateFormat = Year(now) & Right("0" & Month(Now), 2) & Right("0" & Day(now), 2)
Set Excel = CreateObject("Excel.Application")
Set ExcelNew = CreateObject("Excel.Application")

ExcelNew.Application.DisplayAlerts = False
ExcelNew.Application.Visible = True
ExcelNew.Workbooks.Open("C:\Program Files\Solvent_Tank_Reports\Solvents"& ".xls")
wscript.sleep(5000) 'sleep 5 seconds

Excel.Application.DisplayAlerts = False
Excel.Application.Visible = True
Excel.Workbooks.Open("C:\Program Files\Solvent_Tanks.xls")
Excel.RegisterXLL "pipc32.xll"
Excel.AddIns("PI-DataLink").Installed = False
Excel.AddIns("PI-DataLink").Installed = True
wscript.sleep(5000) 'sleep 5 seconds

Excel.Workbooks("C:\Program Files\Solvent_Tanks.xls").Worksheets("Sheet1").Range("A1:C27").Copy ExcelNew.Workbooks(("C:\Program Files\Solvent_Tank_Reports\Solvents"& ".xls")  ".xls").Worksheets("Sheet1").Range("A1")

ExcelNew.Activeworkbook.SaveAs "C:\Program Files\Solvent_Tank_Reports\Solvents_" & MyDateFormat & ".xls"

Excel.Activeworkbook.Close
ExcelNew.Activeworkbook.Close
Excel.Quit
WScript.Quit