Changing a csv file opened as hidden workbook

[Ggalla1779]

Hi all

Have piece of code that opens a csv workbook in hidden mode and then list data out.

The csv format has changed and I want to insert a row and delete a column... can this bee done with hidden workbooks/ sheets??

cheers

George

 

[GS]

Ggalla1779 formulated the question :

Hi all

Have piece of code that opens a csv workbook in hidden mode and then list
data out.

The csv format has changed and I want to insert a row and delete a column...
can this bee done with hidden workbooks/ sheets??

cheers

George

Yes! Just use a fully qualified ref to the sheet in the hidden
workbook. Of course, you'll need to know where to insert the row and
which column to delete.

Example...

Sub EditHiddenWkb()
Dim wks As Worksheet
Set wks = Workbooks("Book1").Sheets(1)
With wks
.Rows(2).Insert shift:=xlDown: .Columns(2).Delete
End With
End Sub

...where Workbooks("Book1") is hidden and Sheets(1) contains data. A new
row is inserted AT row 2, moving row 2's data into row 3 position and
so on down the sheet. Columns("B") is deleted, moving column "C" into
column B's position and so on across the sheet.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ggalla1779]

Ggalla1779 formulated the question :

Yes! Just use a fully qualified ref to the sheet in the hidden
workbook. Of course, you'll need to know where to insert the row and
which column to delete.

Example...

Sub EditHiddenWkb()
Dim wks As Worksheet
Set wks = Workbooks("Book1").Sheets(1)
With wks
.Rows(2).Insert shift:=xlDown: .Columns(2).Delete
End With
End Sub

..where Workbooks("Book1") is hidden and Sheets(1) contains data. A new
row is inserted AT row 2, moving row 2's data into row 3 position and
so on down the sheet. Columns("B") is deleted, moving column "C" into
column B's position and so on across the sheet.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks Gary

Is there a way to make it visible or save changes....so I can check if I have changed the csv file correctly?

cheers George

 

[GS]

Ggalla1779 wrote on 6/25/2012 :
Sub EditHiddenWkb()
Dim wks As Worksheet, wkb As Workbook
Dim vAns As Variant, sMsg As String

Set wkb = Workbooks("my.csv"): Set wks = wkb.Sheets(1)
With wkb
'Get the position to insert a new row,
'and the label of the column to delete.
sMsg = "Enter the row number position to insert the new row AND " _
& "the label of the column to delete, separated by a comma." _
& vbCrLf & vbCrLf & "Example: 2,B "
vAns = InputBox(sMsg)
If vAns = False Then Exit Sub '//user cancels

'Insert row and delete column
vAns = Split(vAns, ",")
With wks
.Rows(vAns(0)).Insert shift:=xlDown: .Columns(vAns(1)).Delete
End With 'wks

'Confirm success
.Visible = True
sMsg = "Do you want to save the changes to " & wkb.Name & " ?"
vAns = MsgBox(sMsg, vbYesNo)
.Visible = False: If vAns = vbYes Then .Save Else .Saved = True
End With 'wkb
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ggalla1779]

I tried that bu didnt work, changed it a bit

Set xlAppx = New Excel.Application
Set wkb = xlAppx.Workbooks.Open(Filename:=MyFile)
Set wks = wkb.Sheets(1)

wkb.Visible = True

Doesnt like this says its .Visible isnt available..this will be excel 2003

 

[GS]

Ggalla1779 explained on 6/26/2012 :

I tried that bu didnt work, changed it a bit

Set xlAppx = New Excel.Application

Why are you starting a new instance of Excel? I thought the hidden
workbook was in the running instance!

Set wkb = xlAppx.Workbooks.Open(Filename:=MyFile)
Set wks = wkb.Sheets(1)

wkb.Visible = True

Doesnt like this says its .Visible isnt available..this will be excel 2003

Well, I tested the code in XL2003 and it worked for me. The code
assumes the hidden workbook is in the same instance of Excel that the
code is running in.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ggalla1779]

Am modifying code thats there they open new instance for hidden workbook so user doesnt see it

 

[GS]

Ggalla1779 formulated the question :

Am modifying code thats there they open new instance for hidden workbook so
user doesnt see it

That's not necessary because you can open it in the same instance and
hide the workbook there. In either case, the code I posted worked for
me using the same instance. The approach you're using is a bit more
complex but should still work as long as you have a fully qualified ref
to the instance and the workbook/worksheet you're working on.

Alternatively, you could use standard VB[A] procedures for file I/O to
read the CSV into memory and work on it there (or 'dump' it into a
hidden temp worksheet). When you want to view the data just 'unhide'
the temp worksheet. When satisfied the data is correct just 'dump' it
back into a CSV and delete the temp worksheet. This will be orders of
magnitude faster and no memory spaces to cleanup. (Every object you
create must have space reserved for it in memory. This needs to be
released as you're done with each object)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ggalla1779]

Hi Garry thanks for reply

The reason they used a new workbook is the main spreadsheet is protected and unlocked as they need it

Opening a new workbook was the workaround for it....they do it alot tbh and I think its really wasteful...

I got it working by making ref to sheet opened and then copying the hidden sheet, then setting the ref to that. I felt a pang as I copied sheet !!

so now its kinda getting there but had to put in the sheet ref here and there so there rest of code would work

 

[GS]

It sounds like you're trying to work towards an optimal solution. IMO,
using a separate instance doesn't serve that goal in this scenario!

It would be much easier (and faster) to hide what's going on behind the
scenes within the same instance of Excel than go to the trouble you're
going to in order to accomplish what I would consider a rather trivial
task.

<BTW>
The reason the line...

wkb.Visible = True

...fails is because you have to make the new instance visible first.
(Like I said, it's a lot of extra trouble going this route!)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Sorry.., I wasn't paying close enough attention when I was fitting the
post to avoid word wrap! Revise the code as follows...

Sub EditHiddenWkb()
Dim wks As Worksheet, wkb As Workbook
Dim vAns As Variant, sMsg As String

Set wkb = Workbooks("my.csv"): Set wks = wkb.Sheets(1)
With wkb
'Get the position to insert a new row,
'and the label of the column to delete.
sMsg = "Enter the row number position to insert the new row AND " _
& "the label of the column to delete, separated by a comma." _
& vbCrLf & vbCrLf & "Example: 2,B "
vAns = InputBox(sMsg)
If vAns = False Then Exit Sub '//user cancels

'Insert row and delete column
vAns = Split(vAns, ",")
With wks
.Rows(vAns(0)).Insert shift:=xlDown: .Columns(vAns(1)).Delete
End With 'wks

'Confirm success
Windows(wkb.Name).Visible = True
sMsg = "Do you want to save the changes to " & wkb.Name & " ?"
vAns = MsgBox(sMsg, vbYesNo)
Windows(wkb.Name).Visible = False
If vAns = vbYes Then .Save Else .Saved = True
End With 'wkb
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ggalla1779]

thanks

Its behaving better (although still not optimised)


1 thing I am stuck at..... when data file sheet is copied then i goto close workbook and it still asks me do I want to save spreadsheet...... why?

If Not xlWkb1 Is Nothing Then
xlWkb1.Close savechanges:=False
xlApp1.Quit
End If

 

[Gord Dibben]

Tell Excel the file has been saved even though it has not been.

xlWkb1.Close Saved = True


Gord

 

[Ggalla1779]

thanks gord give that a go