Excel Workbook Full Path Name (Including Sheet Name) 1

[kjv1611]

I know I've done this before, but for the life of me, I cannot seem to remember what character or series of characters I need.

I am running this code:

Option Compare Database
Option Explicit

Private Sub ImportTables()
    Dim objXl As Object
    Dim ws As Object
    Dim strWSname As String
    Dim i As Integer
    Dim sFile As String
    Dim sTable As String
    sFile = "C:\FDCPACases2008JanThroughApril.xls"
    sTable = "My Table"
    
    Set objXl = CreateObject("Excel.Application")
    objXl.Workbooks.Open sFile, , True
    With objXl
        For i = 3 To .Worksheets.Count
            strWSname = .Worksheets(i).Name
            Set ws = .ActiveWorkbook.Worksheets(i)
            [highlight]DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strWSname, _
                sFile, True[/highlight]
        Next i
    End With
    Set objXl = Nothing
End Sub

Which I got from a forum thread over at experts exchange, and trimmed a little bit out, editing variable names a tiny bit as well.

My issue is this: when running it as listed, it will use the NAME for each worksheet, but only actually import the first worksheet in the file. I need it to import a series of worksheets, and of course match the actual sheet to the name.

What I've tried so far are these sort of things:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strWSname, _
                sFile & "#" & strWSname, True

And

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strWSname, _
                sFile & "!" & Worksheets(i), True

And I attempted to include single quotes for the worksheet name in at least one instance.

When I try any of those variations, I keep getting the error similar to this: "The Microsoft Jet Engine cannot find the file [YourFileName]"

Any suggestions? I know it's just got to be something VERY simple that I just cannot rake up into my current memory!

--

"If to err is human, then I must be some kind of human!" -Me

 

[Bong]

It looks like "TrasferSpreadsheet" is a macro in the workbook, "C:\FDCPACases2008JanThroughApril.xls". Any chance your problem is there?

_________________
Bob Rashkin

 

[kjv1611]

Well, I hadn't noticed that I dropped it there - duh, me!

I'll take a look there...

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, now, no wonder it's in that workbook. For some reason, when I open that workbook, Excel does not open my Personal.xls workbook where I normally store all the macros.

Anybody got any idea as to why I can open my Personal.xls normally (just tried in a separate instance), but not when I open this workbook? I'm talking about if I have no instance of Excel already open, and it is still not opening Personal.xls.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, that IS strange. Now, if I open the Excel Application first, and then select "file - open" from within Excel, and choose that workbook, I CAN have both my personal.xls open and this workbook. I can't figure out why it doesn't work that way if I just open the file directly (double-click on the Excel file).

I'll now try moving the module over..

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Okay, now when I open that way, I can see both workbooks, but I can no longer find the code that I put on the workbook when opened exclusively, I guess would be a way for looking at it...

Well, I guess it's now official - my work computer has been possessed by a grimlin or something.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, it now appears that the module containing the code has just flat dissappeared, and I know I saved it - I had even given the module a name... basImport.

Boy, what fun!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Wow! All of that digging around for nothin'! I either need some coffee - am pretty sleepy, actually; or else I just need a brain transplant.

No, it's not in the workbook, it's not in Excel at all - it's in Access! So, I've been chasing a wild dumb goose now for a while!

So, back to the original question. Basically, how do I force Access to look for the specific address of each worksheet, NOT just the first one in the stinking workbook with the command, TransferSpreadsheet?

Thanks for any help!

--

"If to err is human, then I must be some kind of human!" -Me

 

[xlbo]

don't think you can unfortunately unless its changed for 2003. I have run into this before and ended up having to export 5 tables each to new workbooks and then copying data from the new workbooks to the master workbook...all from Access code but it's not ideal - you'd think there would be an option to specify a destination sheet in excel

If I was re-doing it now, I'd just use VBA & ADO to run the queries and export to wherever I liked!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Why not use MS Query to PULL the data into whatever sheet you choose? Then the QUERY can be REFRESHED anytime with a few mouseclicks.

faq68-5829.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[kjv1611]

Yeah, now I think I'm actually going to first combine all the data in Excel into one worksheet. Then import that one worksheet into Access. And in order to differentiate between the data, I'm creating a column that specifies which district (worksheet name) the data came from.

Skip, is it possible to use MS Query in that sort of way, or am I better off sticking with VBA? I have started a VBA procedure in Excel, but I'm not finished, and even if I were, if MS Query would be the better option, I'm all for trying that one out. I know I've used it here or there, but I'm still not terribly familiar with it.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

By the way, there are 94 worksheets in the workbook.

[just kidding]
Whew that was a lot of counting.
[/just kidding]

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Sure, you ought to be able to write a Make Table or Append query.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[PHV]

kjv1611, put the cursor inside the TransferSpreadshett word in your AccessVBA code and then press the F1 key to discover the role of the 6th parameter of this method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Skip,
for an Append Query, how do I include all the data from all spreadsheets, or do I have to do that for each spreadsheet? I suppose I could use something like a SQL command in VBA with variables, and loop through the worksheets?

PHV,
The 6th option says:

Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

Maybe I'm just being dense, but I do not see where that would help select specific spreadsheets. Any clues further along that line? I realize it's talking about a range, but does that mean, I would write a range variable that basically changes in the loop to the desired spreadsheet along with the desired area of data? And if so, then I suppose I have to find the last row/column in each sheet that has data. That was something I was wanting to pull back up anyway, as I couldn't remember how that was done, and was wanting to tinker with it again...

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strWSname, _
sFile, True[!], strWSname & "$"[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks, PHV! That did the trick for getting them into Excel successfully. So, the range CAN just be the actual sheet, and it doesn't have to be just a row/column combination. Very nice indeed!

For anyone interested, my full code used to do this is now:

'In Access 2003
Option Compare Database
Option Explicit

Private Sub ImportTables()
    Dim objXl As Object
    Dim ws As Object
    Dim strWSname As String
    Dim i As Integer
    Dim sFile As String
    Dim sTable As String
    sFile = "C:\FDCPACases2008JanThroughApril.xls"
    sTable = "My Table"
    
    Set objXl = CreateObject("Excel.Application")
    objXl.Workbooks.Open sFile, , True
    With objXl
        For i = 3 To .Worksheets.Count
            strWSname = .Worksheets(i).Name
            Set ws = .ActiveWorkbook.Worksheets(i)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strWSname, _
                sFile, True, strWSname & "$"
        Next i
    End With
    Set objXl = Nothing
End Sub

PHV's edition fixed what I was missing.

Of course, now I've still got to get the data all into one table now, just like I was going to get into one spreadsheet, but I think I'd personally rather do that in Access anyway.

I'll post whatever I come up with for that part as well.

Thanks all for the suggestions.

--

"If to err is human, then I must be some kind of human!" -Me