[LONG] how to open workbooks using names stored in array?

[mudz78]

Hi,

This is my first post, so don't be too rough.

My understanding of Excel (and programming) in general, is limited. I have a broad understanding of concepts, but limited hands on experience.

So, to the problem at hand:

Using VB in Excel, I am trying to open a whole bunch of user selected files (.TBL extension) and copy data from all of these files into a single workbook (in fact, a single worksheet).

There are some formatting issues with the TBL files that I managed to solve, but I am having difficulting copying the data from each file in the array to the new workbook.

My problem lies in how I can use the value stored in the array to activate that workbook. Everything I try seems to return a "subscript out of range error" , or a "data type mismatch". I can't find anything on converting the array data type "variant" to a file name type "String", and using the array variable name as my workbook reference isn't working.

Some issues I would llike to point out:

I'm not sure how to appropriately reference between workbooks and worksheets to begin with, so I tend to end up refer to everything with a sort of "absolute reference" ie, there is proabably a shorthand way of coding my activation procedures (fill me in please!)

I *like* coding various subs that I call with a main routine, but I don't think that I have fully grasped how various declarations interact in this kind of scenario. For example, it would nice to know how I can declare global variables in a main routine and then vary those values in the various subs, but for some reason I can't get it happening. I am aware of Private and Public elements, but not too sure when (or where) I can/should use them.

Here is a copy of the pertinent code (extraneous is snipped), you might even recognise some of it from these boards:

'fetches all the file names to an array
Sub SelectOpenCopy()

Dim i As Long
Dim wbkToCopy As Workbook
Dim vaFiles As Variant

vaFiles = Application.GetOpenFilename("TBL Files (*.tbl), *.tbl", _
Title:="Select files", MultiSelect:=True)

addNew

'sequentially sorting throught the array files

If IsArray(vaFiles) Then
For i = LBound(vaFiles) To UBound(vaFiles)
Set wbkToCopy = Workbooks.Open(Filename:=vaFiles(i))

'the below subs do all the neccessary formatting to each file
createSheets
formatSheets
dataSort

'this sub is what i would like to copy and paste the data with (and is where my problem lies)
dataTransferNext i
End If
End Sub


Sub addNew()

'the file that I wish to populate

Set NewBook = Workbooks.Add
With NewBook
.Title = "Converted TBL Files"
.Subject = "Traffic Counter Database"
End With
Application.DisplayAlerts = False
Worksheets("sheet2").Delete
Worksheets("sheet3").Delete
Application.DisplayAlerts = True
Worksheets(Sheets(1).Name).Name = "completedCounterData"
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
NewBook.SaveAs Filename:=fName
End Sub

ANy help would be gretaly appreciated.

Thanks,
-mudz

 

[Paul_Godley]

Re: [LONG] how to open workbooks using names stored in array

Hi,

If the TBL files that you are using are stored in one folder, and no other TBL files are in the same folder, I would use "DIR" to loop through each file.

If you typr DIR in the VB help, and view the example code, it should help.

Good Luck
Paul

 

[Richie(UK)]

Re: [LONG] how to open workbooks using names stored in array

Hi mudz,

Welcome to the board

OK, first off - I'm glad that you like having various subs that are called from the main routine. IMO this leads to much more structured code that is easier to debug and easier to understand (both for you and anybody else that reads it). :wink:

If you are looking to use variables in more than one routine then there are basically two approaches, public variables or passing arguments to the subs.

If you want the variable to be available just to one module then declare it at the top of the module concerned. If you want it available to all modules then declare it as Public.

However, I would suggest that the better approach is to pass arguments between your various subs. I've included a simple illustration based around your code.

Sub SelectOpenCopy()
    Dim i As Long
    Dim vaFiles As Variant
    
    vaFiles = Application.GetOpenFilename(Title:="Select files", MultiSelect:=True)
    
    'sequentially sorting throught the array files
    If IsArray(vaFiles) Then
        For i = LBound(vaFiles) To UBound(vaFiles)
            'this sub is what i would like to copy and paste the data with _
            (and is where my problem lies)
            dataTransfer (vaFiles(i))
        Next i
    End If

End Sub

Sub dataTransfer(strWbkName As String)
    Dim wbkToCopy As Workbook
        
    Set wbkToCopy = Workbooks.Open(FileName:=strWbkName)
    MsgBox wbkToCopy.Name
    wbkToCopy.Close
    
End Sub

Does that help?

 

[mudz78]

Re: [LONG] how to open workbooks using names stored in array

Thanks for the idea PG, unfortunately, there is not guarantee that I will want to open all of the <filename>.tbl files in any given folder, which is why I have opted for a user selection.

--------------------------------------------------------------------------------
Sub SelectOpenCopy()
Dim i As Long
Dim vaFiles As Variant

vaFiles = Application.GetOpenFilename(Title:="Select files", MultiSelect:=True)

'sequentially sorting throught the array files
If IsArray(vaFiles) Then
For i = LBound(vaFiles) To UBound(vaFiles)
'this sub is what i would like to copy and paste the data with _
(and is where my problem lies)
dataTransfer (vaFiles(i))
Next i
End If

End Sub

Sub dataTransfer(strWbkName As String)
Dim wbkToCopy As Workbook

Set wbkToCopy = Workbooks.Open(FileName:=strWbkName)
MsgBox wbkToCopy.Name
wbkToCopy.Close

End Sub
--------------------------------------------------------------------------------


Does that help?

Hi Richie,

Yes that has helped a lot, , but...I can get my array filenames (thanks very much, btw), but now I am having trouble passing another variable (which is also a filename) to a function.

Here is the pertinent code:

Sub SelectOpenCopy()
Dim i As Long
Dim vaFiles As Variant
Dim fName As String

fName = Application.GetSaveAsFilename
Loop Until fName <> False
NewBook.SaveAs Filename:=fName


If IsArray(vaFiles) Then
For i = LBound(vaFiles) To UBound(vaFiles)

dataTransfer (vaFiles(i), fName)
Next i
End If

Sub dataTransfer(strWbkName As String, strNewName As String)

Dim wbkToCopy As Workbook
Dim newWbk As Workbook

Set newWbk = Workbooks.Open(Filename:=strNewName)
Set wbkToCopy = Workbooks.Open(Filename:=strWbkName)

createSheets
formatSheets
dataSort

Worksheets("fileData").Activate
Worksheets("fileData").Range("A2").Select
Worksheets("fileData").Range(Selection, Selection.End(xlToRight)).Select
Worksheets("fileData").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Workbooks(Filename:=newName).Activate
End Sub

The code breaks at:

dataTransfer (vaFiles(i), fName)

and I get a msg return "Compile Error, expected: ="

It's driving me crazy!

I am not too sure about the Sub dataTransfer() as I haven't been able to get the script to reach this point, but I am sure that it has errors too (much of what I have done has been cut and paste).

Again, any help would be greatly apprectiated.

Thanks for your time,
-mudz
any explanation as to *why* my errors are, well, wrong, would also be pretty handy

 

[dk]

Re: [LONG] how to open workbooks using names stored in array

Hi,

You don't need the parentheses when calling a sub:-

dataTransfer CStr(vaFiles(i)), fName should do the trick. You will see that I used the Cstr function to convert the value in vaFiles(i) to a string, otherwise you'd get another error.

HTH
Dan