Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #31   Report Post  
Old June 30th 11, 06:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 42
Default VBA Question

On Jun 27, 5:17*pm, isabelle wrote:
hi carl,

i hope that the correction made ​​by Gord has solved your problem,

--
isabelle


Thanks. I did try this:

Sub test2()
Dim nRow As Integer, nColumn As Integer, n As Integer
Dim sDir As String
nRow = 5
For i = 1 To 3 'where 3 is the range of paths in Sheet1 A1:A3
'adjust as necessary
sDir = Range("A" & i)
n = n + 1
For nColumn = 1 To 5
Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _
("'" & sDir & "[ORF.xlsx]ORF_Charge'!R" & nRow & "C" & nColumn &
"")
Next
Next
End Sub

So I've changed the file extension to xlsx.

My file paths is in sheet 1 A1:A3. Like this J:\Projects\ORF\Meeds\xlsx
\005\ORF.xls

When I run the macro I still get prompted with an explorer window
that look like I need to select a file. Tha window is pointed to my
local pc desktop.


  #32   Report Post  
Old June 30th 11, 07:00 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default VBA Question

I don't know what else to say

Are the paths in A1:A3 in double quotes?

"C:\the path\more path\rest of path"

Does the workbook ORF.xlsx exist in each of the tree folders?

I cannot replicate the "explorer window" unless one or both of the above
criteria are not met.


Gord


On Thu, 30 Jun 2011 08:48:22 -0700 (PDT), carl wrote:

Thanks Gord.

I made the changes but the macro still prompts me with the explorer
window to select a file.

I have my list of paths in Sheet 1 A1:A3

Here's the code I am using.

Sub test2()
Dim nRow As Integer, nColumn As Integer, n As Integer
Dim sDir As String
nRow = 5
For i = 1 To 3 'where 3 is the range of paths in Sheet1 A1:A3
'adjust as necessary
sDir = Range("A" & i)
n = n + 1
For nColumn = 1 To 5
Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _
("'" & sDir & "[ORF.xlsx]ORF_Charge'!R" & nRow & "C" & nColumn &
"")
Next
Next
End Sub


The only change I made was the file extension "xls" to "xlsx" and
nColumn set to 5.

  #33   Report Post  
Old June 30th 11, 07:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default VBA Question

You have neglected to enclose the full paths in double quotes as you were shown
6/27/2011 posting by me.

See my earlier posting today.


Gord


On Thu, 30 Jun 2011 10:34:03 -0700 (PDT), carl wrote:

On Jun 27, 5:17*pm, isabelle wrote:
hi carl,

i hope that the correction made ??by Gord has solved your problem,

--
isabelle


Thanks. I did try this:

Sub test2()
Dim nRow As Integer, nColumn As Integer, n As Integer
Dim sDir As String
nRow = 5
For i = 1 To 3 'where 3 is the range of paths in Sheet1 A1:A3
'adjust as necessary
sDir = Range("A" & i)
n = n + 1
For nColumn = 1 To 5
Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _
("'" & sDir & "[ORF.xlsx]ORF_Charge'!R" & nRow & "C" & nColumn &
"")
Next
Next
End Sub

So I've changed the file extension to xlsx.

My file paths is in sheet 1 A1:A3. Like this J:\Projects\ORF\Meeds\xlsx
\005\ORF.xls

When I run the macro I still get prompted with an explorer window
that look like I need to select a file. Tha window is pointed to my
local pc desktop.

  #34   Report Post  
Old June 30th 11, 07:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default VBA Question

Isabelle

It doesn't matter whether the backslash is present or not.

Works either way for me.............what is critical are the double quotes.


Gord

On Thu, 30 Jun 2011 13:16:06 -0400, isabelle wrote:

is that list contains values like "c:\temp\" with a backslash in the end ?

  #35   Report Post  
Old June 30th 11, 07:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default VBA Question

I meant backslash "at the end"

On Thu, 30 Jun 2011 11:21:16 -0700, Gord Dibben wrote:

Isabelle

It doesn't matter whether the backslash is present or not.

Works either way for me.............what is critical are the double quotes.


Gord

On Thu, 30 Jun 2011 13:16:06 -0400, isabelle wrote:

is that list contains values like "c:\temp\" with a backslash in the end ?



  #36   Report Post  
Old July 1st 11, 08:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 42
Default VBA Question

On Jun 30, 2:23*pm, Gord Dibben wrote:
I meant backslash "at the end"



On Thu, 30 Jun 2011 11:21:16 -0700, Gord Dibben wrote:
Isabelle


It doesn't matter whether the backslash is present or not.


Works either way for me.............what is critical are the double quotes.


Gord


On Thu, 30 Jun 2011 13:16:06 -0400, isabelle wrote:


is that list contains values like "c:\temp\" with a backslash in the end ?- Hide quoted text -


- Show quoted text -


Thanks all. I've worked through this intital issue. Now the results
display #REF1.

Can you confirm that this will work on an XLSX file - I am running
Excel 2003.
  #37   Report Post  
Old July 1st 11, 10:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default VBA Question

OK! At least we've worked through the "explorer window".

In your workbook with the code you have two sheets.

Sheet1 and Sheet2

Code is in a General Module.

A1:A3 of Sheet1 has the paths.

Select Sheet2 and run the macro.

I don't have 2007 installed at present but I don't know why the code should not
work in 2007 version with 2007 version workbooks.

It won't work if all you have is 2003 and are trying to communicate with 2007
ORF.xlsx workbooks.


Gord


On Fri, 1 Jul 2011 12:25:28 -0700 (PDT), carl wrote:

Thanks all. I've worked through this intital issue. Now the results
display #REF1.

Can you confirm that this will work on an XLSX file - I am running
Excel 2003.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 Macro/VB Question DDE Question MadDog22 Excel Worksheet Functions 1 March 10th 10 01:47 AM
SUM question Nick Ng[_2_] Excel Worksheet Functions 5 October 18th 09 04:10 PM
where can I see my question and answer? Yesterday I ask a question IP Excel Discussion (Misc queries) 2 May 10th 08 04:08 PM
Newbie Question - Subtraction Formula Question [email protected] Excel Discussion (Misc queries) 3 May 5th 06 05:50 PM
The question is an excel question that I need to figure out howto do in excel. Terry Excel Worksheet Functions 3 January 23rd 06 06:22 PM


All times are GMT +1. The time now is 11:32 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017