Solved: Concealing open workbooks

[clvestin]

I open a workbook to extract some information.


[VBA]
Workbooks.Open Filename:="S:\PV\mydummy.xls"
UserForm2.TextBox1.Text = Range("b5")
UserForm2.TextBox2.Text = Range("b6")
UserForm2.TextBox3.Text = Range("b7")
UserForm2.TextBox4.Text = Range("b8")
UserForm2.TextBox5.Text = Range("b9")
UserForm2.TextBox6.Text = Range("b10")
UserForm2.TextBox7.Text = Range("b11")
UserForm2.TextBox8.Text = Range("b12")
Workbooks("mydummy").Close
[/VBA]

I would prefer this workbook not be visible.
Do I really need to open the workbook at all, ie can i read the info without the Open. This could just as well be a csv file if that is an easier fix.

[XLGibbs]

If you are just bring in one row of data from the text file why not have a sheet set up that is pre-set using the Data?Import> Text file function on the worksheet itself.

In the VB window you can shet that sheet to be xlVeryHidden as the default property,

and instead of opening tthe file you can just refresh the external data...

If you toggle screenupdating off before the open, copy data, close sequence above, the file would not be visible during the process (although the file count in the taskbar may increase while it is open by 1 then decrease).

But I would think hiding the text import on a xlveryhidden sheet in the file is the way to go... unless the location of said file, and file name itself may change...

Then, you could record the import of the text and modify that to be built into your code as well...

[Bob Phillips]

You could use ADO

[vba]
Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "S:\pv\mydummy.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [B5:B12]"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1
' Check to make sure we received data.
If Not oRS.EOF Then
Dim ary
ary = oRS.getrows
textbox1.Text = ary(0, 0)
textbox2.Text = ary(0, 1)
'etc.
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub
[/vba]

[Norie]

Just turn off ScreenUpdating.
[vba]Dim wb As Workbook
Dim I As Long
Application.ScreenUpdating = False

Set wb = Workbooks.Open (Filename:="S:\PV\mydummy.xls")
For I = 1 to 8
With wb.Activesheet
UserForm2.Controls("Textbox" & I).Text = .Range("b" &(I+4))
End With
Next I
wb.Close
Application.ScreenUpdating = True[/vba]