variable 'open' command

[Walt]

Hi Guys

I have the following problem:
I have to copy data from one excelfile (1.xls) to another
(summery.xls). No problem so far. it looks like

Workbooks.Open ("c:\[...]\1.xls")
Windows("1.xls").Activate

But now the path and the file itself changes! i need some kind of
query to tell to program which file it should use.

I couldn't find anything in the helpfiles which are not helpful at
all, btw.

Help is very much appreciated!
Walt

 

[Dave Peterson]

Does this mean that C:\...\1.xls can change name and location?

If yes, you can ask the user to just click on the file using the File|open
dialog.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim myWkbk As Workbook

myFileName = Application.GetOpenFilename("Excel files, *.xls")
If myFileName = False Then
'user hit cancel
Exit Sub '??
End If

Set myWkbk = Workbooks.Open(Filename:=myFileName)

'Now you can refer to the variable mywkbk instead of the actual name.
mywkbk.worksheets(1).range("A1").value = date
mywkbk.close savechanges:=true

End Sub

Hi Guys

I have the following problem:
I have to copy data from one excelfile (1.xls) to another
(summery.xls). No problem so far. it looks like

Workbooks.Open ("c:\[...]\1.xls")
Windows("1.xls").Activate

But now the path and the file itself changes! i need some kind of
query to tell to program which file it should use.

I couldn't find anything in the helpfiles which are not helpful at
all, btw.

Help is very much appreciated!
Walt

 

[Walt]

Thx, Dave. That helped a lot! But there is still a problem. If I try
(as Henry and I thought would be possible)

Workbooks("myWkbk").Activate

i get an error message:

"Run-time error '9': Subscript out of range"

Any clue for that one?

TIA
Walt

Does this mean that C:\...\1.xls can change name and location?

If yes, you can ask the user to just click on the file using the File|open
dialog.

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim myWkbk As Workbook

myFileName = Application.GetOpenFilename("Excel files, *.xls")
If myFileName = False Then
'user hit cancel
Exit Sub '??
End If

Set myWkbk = Workbooks.Open(Filename:=myFileName)

'Now you can refer to the variable mywkbk instead of the actual name.
mywkbk.worksheets(1).range("A1").value = date
mywkbk.close savechanges:=true

End Sub

Hi Guys

I have the following problem:
I have to copy data from one excelfile (1.xls) to another
(summery.xls). No problem so far. it looks like

Workbooks.Open ("c:\[...]\1.xls")
Windows("1.xls").Activate

But now the path and the file itself changes! i need some kind of
query to tell to program which file it should use.

I couldn't find anything in the helpfiles which are not helpful at
all, btw.

Help is very much appreciated!
Walt

 

[D. A. Gray]

Hi Walt,

Your problem statement is a bit vague. How is the program
supposed to determine the file name?

David Gray
P6 Consulting
http://www.p6c.com

You are more important than any technology we may employ.