Prompt for file name and paste contents of active sheet

[Guest]

I have a template that collates and summaries data from three data sheets
created by our database.

Each file is saved as a job number followed by GL, L or J. For example,
General Ledger data is exported to a file called 1234GL.xls, Labour data is
exported to a file called 1234L.xls, etc. Each file has only one sheet with
the same name as the file name without the xls extension.

I have destination sheets in a Job Summary Template called "Ledger",
"Labour" and 'JobCard". Macros are triggered when the destination sheet is
updated.

Currently users are taken via a macro to the file directory and prompted to
select a data file. The entire contents of the ActiveSheet is copied to the
sheet allocated to that macro (“Ledgerâ€, “Labour†etc). Unfortunately, the
user must close the three open data sheets before continuing to the next job
summary.

Would it be possible for the user to be prompted for the job number, have
this added to the “GL, “L or “J†and the file selected for them, and the
contents of the ActiveSheet copied to the named target sheet and the data
file closed.

If the prompt is too difficult I would be happy with being able to close the
data file after the data is copied to the named target sheet.

 

[Guest]

I'm not sure from your description if you are just asking to close the 3
files, or also asking about a better way of opening the files.

Everything you are asking is possible. I think upgrading your poresent
macros rather than add ing new macros is the best way of going. It would be
easier if you posted the old macros so they can be modified.

 

[Guest]

Thanks Joel, I was hoping to have the data files close after the data was
pasted. However, I was being a bit opportunistic asking if the user could be
prompted for the file name (job No) to save them scolling through dozens in
the list-nice but not essential.

The following macro is activated on file open to select the first data file.
For clarity I'll post the others separtely. They are activated via button.

Sub OpenLedger()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Select ""YES"" to proceed to Open a Job Ledger Data File, ""NO"" to
view Current File only"
Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Open a New Ledger Data File " ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then
Dim myFileName As Variant
Dim wkbk As Workbook
Dim MyPath As String

MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji"
MsgBox "Select a Job GL File to use"
ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji"
'ChDir "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji"
ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well
myFileName = Application.GetOpenFilename("Excel Files, *.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

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

Else
Exit Sub
End If
ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
'wkbk.Close SaveChanges:=False '---attempts to make it work
'ActveWindow.Close '---Attempts to make it work
Windows("JobCost Template V3.3.xls").Activate
Sheets("Ledger").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("A16").Select
Application.DisplayAlerts = True


End Sub

 

[Guest]

Sorry Joel, I mentioned that I would post the other macros. They are
identical to this except for the message prompt and destination sheets which
I should be able to sort out myself.

Cheers

 

[Guest]

If you uncomment this line in the code it does close the workbook. If you
look at the VBA project window it is still listed but the book is really
closed.

wkbk.Close SaveChanges:=False ' this does work

You can also close workbooks by name

workbooks("book1.xls").close

When you close a workbook by name youmust remove the path name and only
include the filename with the extension. There are a few ways of doing this.
I sometimes use this code

Filename = "c:\temp\workbook1.xls"
Do While InStr(Filename, "\") > 0
Filename = Mid(Filename, InStr(Filename, "\") + 1)
Loop

 

[Guest]

Thanks again Joel,
I had tried this earlier but it fails at;
"Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False"

I assumed this to be due to the clipboard clearing before the paste
instruction. I can see that if I had the name of the data file I could paste
before closing. Unfortunately, every file name will be different. Maybe if
the user passed the file name via a prompt at opening it could be used in the
wbk.Close command. What do you think?

 

[Guest]

I've changed the macro that runs on open to that below. It runs fine.

Since this runs first I have changed the other two Macros to pick up the Job
Number from the Ledger Sheet (sFilename =
Sheets("Ledger").Range("I17").Value).

I can now run the three one after the other on open. Probably not as
efficient as it could be but it works.

The only thing to add now is some error handling when a data file is not
found.

As you can see, I'm no expert and rely on you guys a lot, so thanks
(everyone) for all the good ideas.---It's almost idiot proofed!

-----------
Sub OpenLedger()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Select ""YES"" to proceed to Open a Job Ledger Data File, ""NO"" to
view Current File only"
Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Open a New Ledger Data File " ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then
Dim myFileName As Variant
Dim wkbk As Workbook
Dim MyPath As String
Dim sFilename As String
Dim fExitDo As Boolean
Dim sFileType As String
Dim sFileOpen As String

MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji\"
ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji"
ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well
sFilename = InputBox("Please Provide the Job Number Only")
sFileType = "GL"
sFileOpen = MyPath & sFilename & sFileType & ".xls"
fExitDo = False

If sFilename = "" Then
Exit Sub 'user hit cancel
End If

Set wkbk = Workbooks.Open(Filename:=sFileOpen)

Else
Exit Sub
End If
ActiveSheet.Cells.Select
Selection.Copy
Application.DisplayAlerts = False
Windows("JobCost Template V3.3.xls").Activate
Sheets("Ledger").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
wkbk.Close Savechanges = False
Application.DisplayAlerts = True


End Sub