Re: xls files

Posted by Grant E Foraker on July 29, 2021 at 05:36:05: Uploaded files: 2

In Reply to: xls files posted by Marc from DATA 1 on July 28, 2021 at 15:36:07:

Closest I've seen uses a "Master" xls that is launched by Comet. It opens the CSV, copies the data, opens a Read Only XLS, pastes the csv data, then launches the Read Only. Uses does a Save As to complete the cycle.

The "Master" uses XLS macros.
-----------------------------------------------------------------------------------------
OMLLWA-1 "\\psr_br\Com_Data_BR\COMET\Material"
OMLLWA-2 "\\psr_br\Com_Data_BR\COMET\Material\MASTER MATERIAL REPORT - WA - MASTER.XLS"

From my #PATH file

1sr record tell Comet where to put the CSV
2nd tells Comet the Master" to launch
============================================================================
This is the Macro Code

Sub Auto_Open()
'
' Macro1 Macro
' Macro recorded 04/03/2015 by John McCann
'

'
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ChDir "\\PSR_BR\Com_Data_BR\COMET\Material"
Workbooks.Open Filename:="\\PSR_BR\Com_Data_BR\COMET\Material\MATLW-XL.CSV"
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:W1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Master Material Report - WA - MASTER.xls").Activate
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A7").Select
Windows("MATLW-XL.CSV").Activate
ActiveWindow.Close
Workbooks.Open Filename:="\\PSR_BR\Com_Data_BR\COMET\Material\OMLLW Template for Bill George - NO AUTO MACRO.xls"
Windows("Master Material Report - WA - MASTER.xls").Activate
Range("A7").Select
Selection.Copy
Windows("OMLLW Template for Bill George - NO AUTO MACRO.xls").Activate
ActiveCell.Offset(-5, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Master Material Report - WA - MASTER.xls").Activate
ActiveCell.Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveCell.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("OMLLW Template for Bill George - NO AUTO MACRO.xls").Activate
ActiveCell.Offset(5, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Select
Workbooks("Master Material Report - WA - MASTER.xls").Close SaveChanges:=False
Application.ScreenUpdating = True

'
ErrorHandler:
=========================================================================================

Screen shot of CSV and final XLS.

Follow Ups:

• Re: xls files Marc from DATA 1 07:25:24 07/29/21 (1)
  • Re: xls files Grant E Foraker 07:29:03 07/29/21 (0)
• Re: xls files Grant E Foraker 05:39:04 07/29/21 (0)

Post a Followup

Each file can be a maximum of 1MB in length Uploaded files will be purged from the server on a regular basis.