Using filename retrieved from GetOpenFilename

  • Thread starter Horatio J. Bilge, Jr.
  • Start date
H

Horatio J. Bilge, Jr.

I am trying to write a macro that will use one file to update another. I got
some test code to work the way I wanted, but I need to user to select the
target file, and that is causing trouble.

Here is what I have so far:
Option Explicit
Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False
FileName =Application.GetOpenFilename(MultiSelect:=False)
Workbooks.Open FileName
Workbooks(FileName).Worksheets("Sheet1").Unprotect Password:="password"
ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
Workbooks(FileName).Worksheets("Sheet1").Range("A1").PasteSpecial
Workbooks(FileName).Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
Workbooks(FileName).Worksheets("Sheet1").Protect Password:="password"
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub
 
D

Duke Carey

Filename will include the path for the workbook - which is OK for opening the
file. After that, Excel dispenses with the path. Use something like this

Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False

FileName =Application.GetOpenFilename(MultiSelect:=False)
if filename = false then exit sub
dim wb as workbook
set wb = Workbooks.Open (FileName)
wb.Worksheets("Sheet1").Unprotect Password:="password"

ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
wb.Worksheets("Sheet1").Range("A1").PasteSpecial
wb.Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
wb.Worksheets("Sheet1").Protect Password:="password"
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub
 
H

Horatio J. Bilge, Jr.

Nice simple fix. Thanks for the help.


Duke Carey said:
Filename will include the path for the workbook - which is OK for opening the
file. After that, Excel dispenses with the path. Use something like this

Sub auto_open()
Dim FileName As Variant
Application.ScreenUpdating = False

FileName =Application.GetOpenFilename(MultiSelect:=False)
if filename = false then exit sub
dim wb as workbook
set wb = Workbooks.Open (FileName)
wb.Worksheets("Sheet1").Unprotect Password:="password"

ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Cells.Copy
wb.Worksheets("Sheet1").Range("A1").PasteSpecial
wb.Worksheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
wb.Worksheets("Sheet1").Protect Password:="password"
Application.CutCopyMode = False
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top