[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Make sheet active when multiple sheets

Private Sub Workbook_Open()
Dim filename As String
Dim ws As Worksheet
Dim qt As QueryTable
There are multiple sheets in my workbook.
When the workbook opens to another sheet, the following code does not work.
I would like to also import another csv to a different sheet "IMPACT".
Can I first make the Entity worksheet active, so that it will work no matter how it was closed? Also, can I then make the Impact sheet active and import "d:\impact.csv"?


filename = "d:\Entity.csv"

    Set ws = Worksheets("Entity")
    Set qt = ws.QueryTables.Add("TEXT;" & filename, Range("A1"))
    qt.TextFileConsecutiveDelimiter = False
    qt.TextFileTabDelimiter = False
    qt.TextFileSemicolonDelimiter = True
    qt.TextFileCommaDelimiter = True
    qt.TextFileSpaceDelimiter = False
    qt.Refresh

End Sub
0
Euro5
Asked:
Euro5
  • 7
  • 6
  • 4
1 Solution
 
NorieCommented:
That code will work whatever sheet is active if you change Range("A1") to ws.Range("A1") .
0
 
Arno KosterCommented:
next to the code for entitiy, you could insert code for the impact sheet:

[...]
filename = "d:\Entity.csv"    
    Set ws = Worksheets("Entity")
    Set qt = ws.QueryTables.Add("TEXT;" & filename, Range("A1"))
    qt.TextFileConsecutiveDelimiter = False
    qt.TextFileTabDelimiter = False
    qt.TextFileSemicolonDelimiter = True
    qt.TextFileCommaDelimiter = True
    qt.TextFileSpaceDelimiter = False
    qt.Refresh
filename = "d:\impact.csv"    
    Set ws = Worksheets("Impact")
    Set qt = ws.QueryTables.Add("TEXT;" & filename, Range("A1"))
    qt.TextFileConsecutiveDelimiter = False
    qt.TextFileTabDelimiter = False
    qt.TextFileSemicolonDelimiter = True
    qt.TextFileCommaDelimiter = True
    qt.TextFileSpaceDelimiter = False
    qt.Refresh
[...]

Open in new window

0
 
Arno KosterCommented:
when you have more csv files that you want to work with, it would be wise to streamline the process a bit:

Sub process()
Dim ws As Worksheet
Dim qt As QueryTable
Dim sheetnames() As String
Dim sheetname As String
Dim filename As String

sheetnames = Array("entity", "impact")
For Each sheetname In sheetnames
    filename = "d:\'" & sheetname & ".csv"
    Set ws = Worksheets(sheetname)
    Set qt = ws.QueryTables.Add("TEXT;" & filename, ws.Range("A1"))
    qt.TextFileConsecutiveDelimiter = False
    qt.TextFileTabDelimiter = False
    qt.TextFileSemicolonDelimiter = True
    qt.TextFileCommaDelimiter = True
    qt.TextFileSpaceDelimiter = False
    qt.Refresh
Next sheetname
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Euro5Author Commented:
But if the sheet is not active, it will only result in an error...
0
 
Arno KosterCommented:
edit: good point imnorie !
0
 
Euro5Author Commented:
Thanks! When I run this, nothing happens - but I don't get an error! :)
0
 
NorieCommented:
When you run what?

The error is being caused because you haven't specified the worksheet that Range("A1"), the destination for the data, is on.

When you do that VBA assumes it's on the active sheet which could be a different sheet than the one you are importing to.
0
 
Euro5Author Commented:
I put that code into Workbook_Open()
and now get error at line
sheetnames = Array("Entity", "Impact")

Help!?
0
 
Euro5Author Commented:
error: For each control variable on arrays must be Variant
0
 
Euro5Author Commented:
Maybe the issue is that I require the code in workbook_open?
0
 
Arno KosterCommented:
can you try it like this:

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim qt As QueryTable
Dim sheetnames() As Variant
Dim sheetname As Variant
Dim filename As String

sheetnames = Array("entity", "impact")
For Each sheetname In sheetnames
    filename = "d:\'" & sheetname & ".csv"
    Set ws = Worksheets(sheetname)
    Set qt = ws.QueryTables.Add("TEXT;" & filename, ws.Range("A1"))
    qt.TextFileConsecutiveDelimiter = False
    qt.TextFileTabDelimiter = False
    qt.TextFileSemicolonDelimiter = True
    qt.TextFileCommaDelimiter = True
    qt.TextFileSpaceDelimiter = False
    qt.Refresh
Next sheetname

End Sub

Open in new window

0
 
NorieCommented:
Change it to this.
Option Explicit

Sub process()
Dim ws As Worksheet
Dim qt As QueryTable
Dim sheetnames() As String
Dim sheetname As String
Dim filename As String
Dim I As Long

    sheetnames = Array("entity", "impact")

    For I = LBound(sheetnames) To UBound(sheetnames)
        filename = "d:\'" & sheetname(I) & ".csv"
        Set ws = Worksheets(sheetname(I))
        Set qt = ws.QueryTables.Add("TEXT;" & filename, ws.Range("A1"))
        qt.TextFileConsecutiveDelimiter = False
        qt.TextFileTabDelimiter = False
        qt.TextFileSemicolonDelimiter = True
        qt.TextFileCommaDelimiter = True
        qt.TextFileSpaceDelimiter = False
        qt.Refresh
    Next I

End Sub

Open in new window

The code would only go in the open event if you wanted it to run when the workbook opens.
0
 
NorieCommented:
Oops, sorry akoster.

I didn't realise it was your code with the loop.
0
 
Euro5Author Commented:
imnorie, can you help me to get this code to work?
I need it to run when the workbook opens. What am I doing wrong?

Sub process()
Dim ws As Worksheet
Dim qt As QueryTable
Dim sheetnames() As String
Dim sheetname As String
Dim filename As String

sheetnames = Array("entity", "impact")
For Each sheetname In sheetnames
    filename = "d:\'" & sheetname & ".csv"
    Set ws = Worksheets(sheetname)
    Set qt = ws.QueryTables.Add("TEXT;" & filename, ws.Range("A1"))
    qt.TextFileConsecutiveDelimiter = False
    qt.TextFileTabDelimiter = False
    qt.TextFileSemicolonDelimiter = True
    qt.TextFileCommaDelimiter = True
    qt.TextFileSpaceDelimiter = False
    qt.Refresh
Next sheetname
End Sub
0
 
NorieCommented:
Just put the code in the worksheet open event in the ThisWorkbook module.
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim qt As QueryTable
Dim sheetnames() As String
Dim sheetname As String
Dim filename As String

sheetnames = Array("entity", "impact")
For Each sheetname In sheetnames
    filename = "d:\'" & sheetname & ".csv"
    Set ws = Worksheets(sheetname)
    Set qt = ws.QueryTables.Add("TEXT;" & filename, ws.Range("A1"))
    qt.TextFileConsecutiveDelimiter = False
    qt.TextFileTabDelimiter = False
    qt.TextFileSemicolonDelimiter = True
    qt.TextFileCommaDelimiter = True
    qt.TextFileSpaceDelimiter = False
    qt.Refresh
Next sheetname
End Sub 

Open in new window

0
 
Euro5Author Commented:
Thanks!
0
 
NorieCommented:
???
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now