Convert .xls to delimiter File in Excel2003?

Rpax

New Member
Joined
Mar 27, 2020
Messages
7
Office Version
2007
Platform
Windows
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,100
try -

replace this

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

with

VBA Code:
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

New Member
Joined
Mar 27, 2020
Messages
7
Office Version
2007
Platform
Windows
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.

VBA Code:
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

New Member
Joined
Mar 27, 2020
Messages
7
Office Version
2007
Platform
Windows
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?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,931
Messages
5,447,371
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top